Unable to connect To Database...PostgreSQL and PHP

Manashi · 03-21-2006, 12:49 AM

hi...
I am doin my project in PHP with PostgreSQL as backend...
Both r very new to me....
My OS is LINUX
I am trying to connect to the database but its not getin connected...
By using
"su posgres" command
i hav created user "root" and database "sample"
By using
"psql -U root sample" command
i hav created table "login" and inserted 3 data's...
Now through code using PHP i am trying to insert the data but connection is not geting established..

the code to connect to the datadase is :-

<?php
require_once "DB.php";
$uname=$_POST['uname'];
$pass=$_POST['pass'];

$username="root";
$password="";
$hostname="localhost";
$dbname="sample";

$db=new DB;
$dsource=$db->factory("pgsql");

$dcon=pg_connect("dbname=sample user=root");

$sql="insert into login values('$uname','$pass')";

$rs=$dsource->pg_query($sql);
?>

is there anythin wrong with the code or sum other problem
waiting for the reply...

fakhrul · 03-21-2006, 02:48 AM

i do in php-mysql. my connectivity also failed. my error was

Quote:

undefined function mysql_connect()

at last i found that there was a package php-mysql that has a php-mysql connection modules that contains mysql_connect function and its code.

do you have install such any package like php-pgsql? if yes
is the DB.php file in the same directory?

i am sorry that i do not connect pgsql with php so i do not clearly know the connectivity code of yours. you can go and check how to connect from either
http://www.php.net or to http://www.postgresql.org/ site.
mentioning error msg would help more.
Do not forget to start apache and pgsql daemons

Manashi · 03-21-2006, 07:32 AM

ya i hav pgsql istalled in my system and there is a file name DB.php in /usr/share/pear directory....
now connection is getin established bt data is not getin inserted
The code is as Follows :-
<?php
include("DB.php");

$uname = $_POST['uname'];
$pass = $_POST['pass'];
$enc_pass=md5($pass);
echo "$enc_pass ";

$db = new DB;
$dsource = $db->factory("pgsql");

$user="root";
$host="localhost";
$password="";
$dbase="sample";

$dconn = pg_connect("dbname=sample user=root");
if($db->isError($dconn))
{
echo "Error";
}
else
{
echo "established ";
}

$sq="insert into login values('$uname','$enc_pass')";
$rs=pg_query($sq);
if($db->isError($rs))
{
echo "error";
}
else
{
echo "QUERY ";
}
if($rs == DB_OK)
{
echo "query executed ";
}
else
{
echo "not executed ";
}
pg_close($dconn);
?>

graemef · 03-21-2006, 08:11 AM

It looks to me as if you are mixing up two pgsql libraries.

With the standard library you code should be something like this:

Code:

$dbconn = pg_connect("dbname=sample user=root");
$stat = pg_connection_status($dbconn);
if ($stat === 0)
{
    echo 'Connection status ok';
}
else
{
    echo 'Connection status bad';
} 
$sql="insert into login values('$uname','$enc_pass')";
$rs=pg_query($dbconn, $sql);
if (pg_result_status($rs) === PGSQL_COMMAND_OK)
{
    echo 'Looks good to me';
}
else
{
    echo pg_result_error($rs);
}

Manashi · 03-30-2006, 06:56 AM

i hav tried this code provided by u...

Quote:

$dbconn = pg_connect("dbname=sample user=root");
$stat = pg_connection_status($dbconn);
if ($stat === 0)
{
echo 'Connection status ok';
}
else
{
echo 'Connection status bad';
}
$sql="insert into login values('$uname','$enc_pass')";
$rs=pg_query($dbconn, $sql);
if (pg_result_status($rs) === PGSQL_COMMAND_OK)
{
echo 'Looks good to me';
}
else
{
echo pg_result_error($rs);
}

on execution its giving "Connection status ok"
But its not inserting data in the table login + its not showing the error message also...since as per code if query is not getting executed then pg_result_error($rs)
should execute...
but only "Connection status ok" message is comming on the page....

graemef · 03-30-2006, 11:37 AM

Okay a couple of thoughts.

1) I suspect that it is not inserting the data, but you could confirm that by adding another echo line before the echo pg_result_error($rs);

2) You are inserting data to do that you will require the appropriate permissions. Ensure that your webserver user (apache?) has the appropriate access.

3) Check the pgsql error log to see if there has been anything logged there.

4) If nothign else works just change it to a select rather than an insert, since they have less access restrictions.

graeme.

Manashi · 04-05-2006, 02:31 AM

i hav tried the followin code in the server where my application is installed :-

Quote:

<?php
include("DB.php");

$db = new DB;
echo "OBJECT : $db ";

$dsource = $db->factory("pgsql");
echo "Source : $dsource ";

$dconn = pg_connect("dbname=sample user=root");
echo "Connection : $dconn ";

if($db->isError($dconn))
{
$val1=$db->isError($dconn);
echo "val1 ";
}
else
echo "EStablished";

$sql="insert into login values(3,'cc');";
$rs=pg_query($sql);
echo "$rs";

if($rs==DB_OK)
{
echo "Failed to insert";
echo pg_last_error($dbconn) ." \n";
}
else
{
echo "insert Complete";
}
?>

there its working fine... but on my system it giveing

Quote:

OBJECT : Object id #1
Source : Object id #2
Connection :
Connection EStablished
insert Complete

But when i am checkin in the DB using "psql -U root sample"
the query used is :"select * from login;"
no data is inserted in the table..
i hav logged into postgres and then created a user root
by running the install script of my application i hav created the DB
Db name :- sample
Db user :- root

by using psql -U root sample
i entered into the DB and gave conmmand

Quote:

sample=#\connect sample root
and the result was
You are now connected to database "sample" as user "root".

even after that data in not getting inserted.. i dnt know how to find where the problem lies...

In server i found 2 files Psql.php and PhpPgAdmin are present in /var/www/html directory
but these two file r not present in my system...
Is this is the reason that my Code is failin to connect to DB...

graemef · 04-05-2006, 06:46 AM

A few points.

You are not checking to see if the connection worked. This is because you are mixing up functions from the PostgreSQL library (pg_connect) with functions from the pear(?) library (DB.php & the DB object) There is no relation between these two unless that is you have added it your self and are not telling us

However are you able to select data from the database, please try that first

michaelk · 04-05-2006, 06:57 AM

At first glance I see two syntax errors.

Quote:

$rs=pg_query( $sql);

should be:
$rs=pg_query($dbconn, $sql);

Quote:

$sql="insert into login values(3,'cc');";

semicolon in bold is not required.

phppgadmin or psql.php will not affect your code.

Reference PHP manual
http://us3.php.net/manual/en/ref.pgsql.php