Thread: MySQL --> PostgreSQL with PHP
Hi! I've been trying to convert this PHP-query thing that I used to run on the project I am moving from MySQL to Postgres. I've done as good as I can but it just doesn't work. I believe my MySQL --> Postgres translation is to blame. There is no error message in the browser. Anyone who can spot what I'm doing wrong here? ----------------------------------------------------------------------- <?php // Producing simple table output with PostgreSQL // Show from timmar in an HTML <table> function displayTimmar($result) { print "<h3 align=center>Arbetstider - Ekolådans Packavdelning</h3><br />\n"; // Start a table, with column headers print "\n<table>\n<tbody>\n<tr>\n" . "\n\t<th style>ID</th>" . "\n\t<th>Datum</th>" . "\n\t<th>Vecka</th>" . "\n\t<th>Dag</th>" . "\n\t<th>P|T|S</th>" . "\n\t<th>Namn</th>" . "\n\t<th>Tid in</th>" . "\n\t<th>Tid ut</th>" . "\n\t<th>Lunch</th>" . "\n</tr>"; // Until there are no rows in the result set, fetch a row into // the $row array and ... while ($row = @ pg_fetch_row($result)) { // Start a TABLE row print "\n<tr>"; // Print out each of the attributes in that row as a // separate TD (Table Data). foreach($row as $data) print "\n\t<td> {$data} </td>"; // Finish the row print "\n</tr>"; } // Then, finish the table print "\n</tbody>\n</table>\n"; } $query = "SELECT * FROM timmar"; // Connect to the PostgreSQL server if (!($connection = @ pg_connect($hostName, $username, $password))) die("Cannot connect"); if (!(pg_select_db($databaseName, $connection))) showerror( ); // Run the query on the connection if (!($result = @ pg_query ($query, $connection))) showerror( ); // Display the results displayTimmar($result); ?> ----------------------------------------------------------------------- -- PostgreSQL 8.4.4, Ubuntu Linux 10.04, Firefox 3.6.10
Hi. On Friday 08 Oct 2010 at 22:42 Helgi Örn Helgason wrote: > Hi! > Anyone who can spot what I'm doing wrong here? [big snip] Try this (easier for me to show than explain): //============ START CODE =================== // Connect to the PostgreSQL server // Build a string to specify the connection parameters, NOT pass them as args. $connectionString = "host=$hostName dbname=$databaseName ". "user=$username password=$password"; if (!$connection = pg_connect($connectionString)) { die("Cannot connect: ".pg_last_error()); } // Run the query on the connection $query = "SELECT * FROM timmar"; // You had $connection and $query in the wrong order here. if (!$result = pg_query ($connection, $query)) { die("Query failed: ".pg_last_error()); } // Display the results displayTimmar($result); //============ END CODE =================== I added some error feedback that I suspect may come in useful while you are working on the code, removed some @ and () you didn't need, and added some {}. For full details of the pg_* commands start here: http://uk.php.net/manual/en/ref.pgsql.php For the functions you are using, here: http://uk.php.net/manual/en/function.pg-connect.php and here: http://uk.php.net/manual/en/function.pg-query.php If the table is small (I assume it is since you are sticking the whole thing in a HTML table) you might also want to consider fetching all the results at once with pg_fetch_all then using a simple foreach in displayTimmar() rather than hitting the database for each row individually. Cheers, Mark
Thank's for your reply Mark, everything looks very good and I will take a look at the links. On 9 October 2010 00:47, Mark Kelly <pgsql@wastedtimes.net> wrote: [snip] > //============ START CODE =================== > // Connect to the PostgreSQL server > // Build a string to specify the connection parameters, NOT pass them as args. > $connectionString = "host=$hostName dbname=$databaseName ". > "user=$username password=$password"; > if (!$connection = pg_connect($connectionString)) { > die("Cannot connect: ".pg_last_error()); > } > My problem this time is that the lines above consistently gives me "Cannot connect:" and nothing else. I am quite sure I've managed to exclude everything else from causing any errors. Could there be some knot in these lines?
On 9 October 2010 00:47, Mark Kelly <pgsql@wastedtimes.net> wrote: > > //============ START CODE =================== > // Connect to the PostgreSQL server > // Build a string to specify the connection parameters, NOT pass them as args. > $connectionString = "host=$hostName dbname=$databaseName ". > "user=$username password=$password"; > if (!$connection = pg_connect($connectionString)) { > die("Cannot connect: ".pg_last_error()); > I forgot, this is my connection file: <?php $connstr = "host=localhost port=5432 dbname=database user=me password=yes"; $dbh = pg_connect($connstr); if ($dbh) { echo ""; } else { echo "No connection has been established<br>"; } ?> Could there be a conflict between these two?
Hi. On Saturday 09 Oct 2010 at 10:09 Helgi Örn Helgason wrote: > On 9 October 2010 00:47, Mark Kelly <pgsql@wastedtimes.net> wrote: > > //============ START CODE =================== > > // Connect to the PostgreSQL server > > // Build a string to specify the connection parameters, NOT pass them as > > args. $connectionString = "host=$hostName dbname=$databaseName ". > > "user=$username password=$password"; > > if (!$connection = pg_connect($connectionString)) { > > die("Cannot connect: ".pg_last_error()); > > I forgot, this is my connection file: > > <?php > $connstr = "host=localhost port=5432 dbname=database user=me password=yes"; > $dbh = pg_connect($connstr); > if ($dbh) > { > echo ""; > } > else > { > echo "No connection has been established<br>"; > } > ?> > > Could there be a conflict between these two? You only need to connect once, then use the connection handle for subsequent database operations. If you have already made a connection by including your connection script, there is no need to do it again. It won't hurt (other than speed & memory), but isn't any point. Just miss out the connection in the example code I sent. You still might want to change your connection script to give you feedback on errors by including pg_last_error(). Cheers, Mark