4

I am new in programming (especially in PHP). I write simple PHP site. I also use PostgreSQL database. Here is my little class for work with database.

class dbWorker {

    private static $conString = "host=localhost port=5432 dbname=myDB user=postgres password=root";

    public static function execute($sql) {
    $db_conn = pg_connect(self::$conString) or die('Could not connect: ' . pg_last_error());
    pg_query($sql) or die('Query error: ' . pg_last_error());
    pg_close($db_conn);
    return;
    }

    public static function queryOne($sql) {
    $allData = self::queryAll($sql);
    if ($allData) {
        return $allData[0];
    }
    return null;
    }

    public static function queryAll($sql) {
    $db_conn = pg_connect(self::$conString) or die('Could not connect: ' . pg_last_error());
    $qu = pg_query($db_conn, $sql) or die('Query error: ' . pg_last_error());


    $retval = [];
    while ($data = pg_fetch_object($qu)) {
        $retval[] = $data;
    }


    pg_free_result($qu);
    pg_close($db_conn);

    if (!empty($retval)) {
        return $retval;
    }

    return null;
    }

}

It is quite convenient using this. But when I use this class 10-20 times on page - page loading time takes for about 5-6 sec. Then I removed the following lines:

   pg_free_result($qu);
   pg_close($db_conn);

After that, page loading time became 161 milliseconds.

The question is - how better organize this process.

Should I write something like this?

dbWorker::open_connection();
...
all my functions calls (like getComments(), getMessages(), getTasksList() ect.)
...
dbWorker::close_connection();

For now I am novice and I am searching simple solution. (Thanks in advance for your help and excuse me for my english)

Dmitry Novice
  • 155
  • 2
  • 12
  • 1
    Yes, there's no point in opening and closing a connection several times in the one page. I don't know if there is any value in explicitly closing the connection either, if this is a web page. – halfer Jan 28 '15 at 17:59
  • 1
    First, why do want to open and close your database connection many times for a single page? It only causes performance issues. Second, always use a connection pool like pgBouncer or pgPool to increase performance and scalability. – Frank Heikens Jan 28 '15 at 18:03

1 Answers1

3

I would create the connection once, perform all your sql queries , then close the connection.

You might be tempted to create persistent connections but there's quite a body of discussion that implies that's probably not a great idea, which I agree with.

trogers1884
  • 172
  • 8