Php PDO connection in postgres

Question

i have a php script which is called again and again with in a interval of 4 seconds by ajax.

try {
    $conn_p = new PDO("pgsql:host=$db_host;dbname=$db_dbname", $db_user, $db_password);
} catch(PDOException $e) {  
    //$e->getMessage();
}

function abc($id)
{
    global $conn_p;

    $st = "select * from table where id=:id";
    $sqlst=$conn_p->prepare($st);
    $bindParamArray=array("id"=>$id);
    $sqlst->execute($bindParamArray);   
    $row=$sqlst->fetch();   
    return $row;
}

function xyz($no)
{
    global $conn_p; 
    $st= "select * from table2 where no=:no and display='Y'";
    $sqlst=$conn_p->prepare($st);
    $bindParamArray=array(':no' => $no);
    $sqlst->execute($bindParamArray);   
    $row=$sqlst->fetch();   
    return $row;
}

function abc($id)
{
    global $conn_p;

    $st = "select * from table where id=:id";
    $sqlst=$conn_p->prepare($st);
    $bindParamArray=array("id"=>$id);
    $sqlst->execute($bindParamArray);   
    $row=$sqlst->fetch();   
    return $row;
}

function getData()
{
    global $conn_p; 
    $st= "select * from table2";
    $sqlst=$conn_p->prepare($st);
    $sqlst->execute();  
    $row=$sqlst->fetchAll();    
    return $row;
}
........same other functions

$data = getData();

foreach ($data as $dd) {

    $abc[] = abc($dd['id']);
    $xyz[] = xyz($dd['no']);
    //some other manipulations......
}

echo json_encode(array('data1'=>$abc,'data2'=>$xyz));

When running sql command

select * from pg_stat_activity

it is showing multiple connections.(told by my server admin to me)

Now my question is that:

Should i need to close the connetion ?
If yes, then when to close it ?
And what if some error occurs ?

Depending on the specifics of your setup you might want to consider using the persistent connection. php.net/manual/en/pdo.connections.php — matiit
– matiit, Commented Jan 3, 2018 at 15:09
ok, when to use $conn_p = null; I mean in my above script where to write it, at end of script or ? — Deepak Kumar
– Deepak Kumar, Commented Jan 3, 2018 at 15:24

jeroen · Accepted Answer · 2018-01-03 15:30:33Z

1

Some suggestions:

No, you do not need to close the connection unless your script runs for a long time and you are not using it while it is doing its time-taking manipulations. The connection will be closed automatically when the script finishes.
You should probably not fetch all rows from the database every x seconds. Only new or changed rows should be more efficient. And do you really need all rows in your page?
You should not run ajax calls using a short interval. Instead do an ajax call and set a timeOut for the next call when the current ajax call finishes. That way requests will never overlap.

answered Jan 3, 2018 at 15:30

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

halojoy · Accepted Answer · 2018-01-03 15:15:57Z

0

THis is what I use in my class for postgresql.
It is same as for mysql.

$this->dsn = $this->driver . ':host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->name;

answered Jan 3, 2018 at 15:15

halojoy

2252 silver badges7 bronze badges

Collectives™ on Stack Overflow

Php PDO connection in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related