Connect to DB - each file or from included file?

[GHicks]

I'm curious what the general consensus is on creating your database connection? I have a initialize.php type file where I store all the various variables used in multiple places throughout my site (database connection variables and that sort of thing).

Which do people prefer?

1) Create a database connection inside that initialize script so the connection is made at page load, then using that connection variable to run queries against the database on the pages where they are needed.

2) Just use the stored variables but don't actually create a connection until it is needed on each specific script?

And just for the sake of clarity, does it matter if you are using PDO or procedural coding as to your answer?

Greg

[GHicks]

OK, thought more about it, and also realized a typo, I meant OOP (not PDO) and obviously when using OOP you would create the connecting in the file where it is actually used, otherwise you remove the benefits of OOP. But if procedural what are your thoughts?

[Cups]

I run an app where I don't think I have a script which does not access the db.

So yes I include a file which invokes a conn to the db. Each website has its own <url>.dsn.php file, giving them access to a database which gives them the minimum of permissions necessary to be able to access that and only that single database (ie DROP not available, maybe even DELETE not available).

zzzzzzzzz.com.dsn.php

<?php
// for XXXXX connections only
$DSN_host = "mysql:host=localhost;dbname=xxxxxxxxx";
$DSN_user = "xxxxxxxxxxxxxx";
$DSN_pass = "xxxxxxxxxxxxxxx";

            try{


            $PDO = new PDO( $DSN_host, $DSN_user, $DSN_pass ); 


            } 
            catch (PDOException $e) {
              print "Error!: " . $e->getMessage() . "<br/>";
              die();
    
            }

// access to file system

$include_path = "/var/www/includes/zzzzzzzzzzz.com" ; 

?>

My way of doing things is that most classes which do need to access/store data then depend upon being passed a $PDO object in order to work. This is not everyone's idea of best practice, let me say.

If the class fails to be fed an instance of a PDO object in its constructor it fails fatally and tells you so.

e.g. here is some old code which still powers parts of the app

<?php

 /**
 * class GenericModel (rather dumb)
 * 
 * Bootstraps from an .ini file and provides basic crud for that single table
 * needs to be fed a PDO connection table name and a PK
 * as well as a set of attributes as a white list
 **/

class GenericModel {

protected $PDO;
protected $table;
protected $pk;
protected $select;
protected $attributes;  // synonymous with fields

 /**
 * function __construct
 * 
 * @param OBJ a PDO object
 * @param STR a table name
 * @param STR a private key 
 * 
 **/

    function __construct( PDO $PDO , $table , $pk ){
    $this->PDO = $PDO;
    $this->table= $table ;    
    $this->pk= $pk ;    

    }
... and so on ...

So, yes largely OOP.