populate dropdown not working

[denzity]

Hi, I have an intranet site that I need to convert as the database backend has changed from MySQL to Postgresql. The following code should populate a dropdown box, I have changed the MySQL functions to Postgres functions but its not happening.

db_connect() is connection function from include file.

<?php
function db_connect()
{
GLOBAL $connstr;
GLOBAL $dbh;
GLOBAL $userID;
GLOBAL $password;
$connstr = "dbname = HelpData user = $userID host = localhost port = 5432 password = $password ";
$dbh=pg_connect($connstr);
if (!$dbh)
{
die ("no connection to database has been established");
}
}
return true;
?>

code to populate dropdown

 <?php
db_connect();
//$sql = "SELECT WSID from tblWorkstation ";
$stat = pg_exec($connstr,"SELECT WSID from tblWorkstation ");
while ($row = pg_numrows($stat))
{ 
 print("<option> $row[0]</option>");
} 
?>

Any suggestions?

D

[Netizen]

I don't use the postgresql functions myself but I think your code is wrong

$stat = pg_exec($connstr,"SELECT WSID from tblWorkstation ");
while ($row = pg_numrows($stat))
{
print("<option> $row[0]</option>");
}
?>

should be something like that shown in the example for pg_fetch_row [php.net]

So your code would look more like:

$stat = pg_query($connstr,"SELECT WSID from tblWorkstation ");
while ($row = pg_fetch_row($stat))
{
print("<option> $row[0]</option>");
}
?>

[denzity]

Hmm tried that

Warning: pg_exec(): supplied argument is not a valid PostgreSQL link resource in /var/www/html/Databasesite2/Untitled-2.php on line 27
Warning: pg_fetch_row(): supplied argument is not a valid PostgreSQL result resource in /var/www/html/Databasesite2/Untitled-2.php on line 28

Thanks for the reply though.

D

[coopster]

Welcome to WebmasterWorld, denzity!

Your

return true;

statement is outside of your

db_connect()

function. This isn't your issue (you aren't checking for a valid connection yet anyway), but I just wanted to bring it to your attention.

Your problem seems to be in your connection, or lack thereof, at least that's what the message is describing. You may want to try a simple script (without using functions) for starters and then work your way back from there.

[denzity]

Yes my thought also I created this page...

<?php
//include("functions.php");
//db_connect(); 
$connstr = "dbname = HelpData user = bob password = pwd ";
$dbh=pg_connect($connstr);
 print_r($dbh);
 echo "<br />\n"; 
if ($dbh)
{
 echo "you are connected";
 echo "<br />\n";
 print_r($dbh);
}
else
{
 die ("no connection to database has been established");
 echo "<br />\n";
 print_r($dbh); 
}
echo "<br />\n";
 $stat = pg_query("SELECT WSID from tblWorkstation ",$connstr );
 while ($row = pg_fetch_row($result, $i)) 
 { 
 print("<option> $row[0]</option>");
 } 
print_r($dbh);
 echo "<br />\n";
pg_close($dbh); 
print_r($dbh);
if(!pg_close($dbh)) 
 {
 print "Failed to close connection to " . pg_host($dbh) . ": " .
 pg_last_error($dbh) . "<br/>\n";
 } 
 else 
 {
 print "Successfully disconnected from database";
 }
?>

Which returns these errors...

Resource id #1
you are connected
Resource id #1

Warning: pg_query(): supplied argument is not a valid PostgreSQL link resource in /var/www/html/Databasesite2/Untitled-2.php on line 34

Warning: pg_fetch_row(): supplied argument is not a valid PostgreSQL result resource in /var/www/html/Databasesite2/Untitled-2.php on line 35
Resource id #1
Resource id #1
Warning: pg_close(): 1 is not a valid PostgreSQL link resource in /var/www/html/Databasesite2/Untitled-2.php on line 43

Warning: pg_host(): 1 is not a valid PostgreSQL link resource in /var/www/html/Databasesite2/Untitled-2.php on line 45

Warning: pg_last_error(): 1 is not a valid PostgreSQL link resource in /var/www/html/Databasesite2/Untitled-2.php on line 46
Failed to close connection to :

I,m beginning to think its something to do with postgres or the postgres installation/version.

D

[coopster]

Well, now we know you are connecting, or at least it seems so, but you have flip-flopped your parameters in your pg_query function. The SQL statement comes after the resource connection.

[denzity]

Hi ok this is starting to work now..

<?php
$userID='user';
$password='pwd';
include("functions.php");
db_connect(); 
//$dbh = pg_connect(" dbname=HelpData port=5432 user=user password=pwd ");
 print_r($dbh);
 echo "<br />\n"; 
if ($dbh)
{
 echo "you are connected";
 echo "<br />\n";
 print_r($dbh);
}
else
{
 die ("no connection to database has been established");
 echo "<br />\n";
 print_r($dbh); 
}
echo "<br />\n";
$query = 'SELECT * from "tblWorkstation" ';
 $result = pg_query($dbh,$query);
 while ($row = pg_fetch_row($result)) 
 { 
 print("<option> $row[0]</option>");
 } 
?> 

it seems it didnt like
$dbh=pg_connect($connstr);

it has to be
$dbh=pg_connect(" dbname=HelpData port=5432 user=user password=pwd) "
which is contrary to the text I'm using as a reference.

also
pg_close($dbh)
doesn't work, but that may be for another reason.
Now I just have to get it to populate the dropdown.

A lot of this could be due to changes made in each version.

thanks for the replies.

Bob