make db connection persistent throught zend framework

Question

I'm using zend framework. currently everytime I need to use the db I go ahead and connect to the DB:

function connect()
{
    $connParams = array(
        "host"   => $host,
        "port"    => $port,
        "username" => $username,
        "password" => $password,
        "dbname"   => $dbname
    );

    $db = new Zend_Db_Adapter_Pdo_Mysql($connParams);
    return $db
}

so I would just call the connect() function everytime I need to use the db

My question is...suppose I want to reuse $db everywhere in my site and only connect once in the very initial stage of the site load and then close the connection right before the site gets sent to the user, what would be the best practice to accomplish this?

Which file in Zend should I save $db in, what method should I use to save it (global variable?), and which file should I do the connection closing in?

If your app uses the default ZF structure, then the answer by @erickthered is right on. If you are only using `Zend_Db` in an otherwise non-ZF project, then see: http://stackoverflow.com/questions/4840941/zend-db-without-zend-framework/4841608#4841608 — David Weinraub, Nov 24 '11 at 03:36
@DavidWeinraub I totally agree. I was basically trying to mimic the style as it seemed he was going for. I personally don't actually use the method I described in my answer, but it would work for those who intend to use only `Zend_Db` in their projects independent of the rest of the framework. The way you described in the answer you linked is how I would probably do it if I didn't use erickthered's method (which is what I actually use). — Yes Barry, Nov 26 '11 at 10:29

score 2 · Answer 1 · answered Nov 24 '11 at 02:43

If you are using the default project structure (with the application, library, tests and public folders), you should use set up the db parameters in application/configs/application.ini

Example application.ini:

[production]
resources.db.adapter = "pdo_mysql"
resources.db.params.host = "localhost"
resources.db.params.username = "testuser"
resources.db.params.dbname = "testdb"
resources.db.params.password = "testpasswd"
resources.db.isDefaultTableAdapter = true

In this way zend framework will automatically open and close the connections to the database and you can use the Zend_Db_Table or Zend_Db_Table_Abstract classes to query your tables easily, for example, to retrieve the student's data for a given SSN you could write a model (application/models/Student.php) that looks something like this:

<?php
class Model_Student extends Zend_Db_Table_Abstract
{
    protected $_name = "student";

    public function fetchRowsBySSN($ssn)
    {
        $select = $this->select();
        $select->where('ssn = ?', $ssn);

        return $this->fetchRow($select)->toArray();
    }
}

As you can see there's no need to open/close the connection and you get an associative array with the fields and values of the student record.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Your best best may be to move all of your db connection code into a separate class in which you can set a static $db var.

protected static $_db;

public static function connect()
{
    if (self::$_db == null) {
        $config = Zend_Config_Xml(); // whatever you'd use

        self::$_db = Zend_Db::factory($config->database);
        self::$_db->setFetchMode(Zend_Db::FETCH_OBJ);
        self::$_db->query('SET NAMES UTF8');

        Zend_Db_Table::setDefaultAdapter(self::$_db); // optional
    }

    return self::$_db;
}

public static function close()
{
    if (self::$_db != null) {
        self::$_db->closeConnection();
    }
}

According to Zend:

Normally it is not necessary to close a database connection. PHP automatically cleans up all resources and the end of a request. Database extensions are designed to close the connection as the reference to the resource object is cleaned up.

However, if you have a long-duration PHP script that initiates many database connections, you might need to close the connection, to avoid exhausting the capacity of your RDBMS server. You can use the Adapter's closeConnection() method to explicitly close the underlying database connection.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 24 '11 at 01:45

Yes Barry

9,514
5
50
69

what about closing the connection? – kamikaze_pilot Nov 24 '11 at 01:50
it'll close automatically without me doing anything? – kamikaze_pilot Nov 24 '11 at 01:53
http://zend-framework-community.634137.n4.nabble.com/How-to-properly-close-database-connection-after-query-td674006.html – Yes Barry Nov 24 '11 at 02:01
What if I have a process that doesn't end? e.g. websocket. Is there a way to not use persistent connection? – Rafael Moni Dec 22 '15 at 12:44
1

@RafaelMoni Just close the connection after you're done. E.g. 1. open connection. 2. run query. 3. get results. 4. close connection. – Yes Barry Dec 22 '15 at 21:13
1

That's what I did! Thanks anyway! :) – Rafael Moni Feb 01 '16 at 19:38

make db connection persistent throught zend framework

2 Answers2