Обсуждение: 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