Page 1 of 1

Database Connections

Posted: Mon Feb 05, 2007 7:10 am
by giles
Hi,

I’ve just finished my first development. Each page load is querying my database approx. 6 times from various stages including functions and classes etc. At this time, a connection is being made each time one of these queries is run.

Everything works great, but I can’t help wondering if this is the most efficient way of operating? - for example, would it be possible / more efficient to create a single connection and pass it to each query?

I’d appreciate any comments or thoughts
Giles

Posted: Mon Feb 05, 2007 7:26 am
by mikeq
You only have to make the connection once at the beginning of the page. It will then be available for the entire page.

Posted: Mon Feb 05, 2007 5:27 pm
by giles
Much neater, thank you for pointing me in the right direction
:D

Posted: Tue Feb 06, 2007 2:35 am
by amaudy
And do not forget to close the connection by mysql_close(); function on end page or end all job.
It can save memory.

Posted: Tue Feb 06, 2007 3:44 am
by crazytopu
Making a connection on each page would mean if you ever need to change the connection settings you would have to do it on every single page. Imagine, if you set up a local connection on your local machine and then decide to put your site on a remote server, you are most likely to change your connection settings. The more pages you have the more cumbersome it becomes for you.

So, I would suggest you make a class -

Code: Select all

<?php
class SystemComponent {

var $settings;

function getSettings(){
//System variables
$settings['siteDir']='/var/www/mysite/';

//Database variables
$settings['dbhost']='localhost';
$settings['dbusername']='root';
$settings['dbpassword']='1234';
$settings['dbname']='db1';

return $settings;

}

}

then make a subclass of it and put all your fucntions there

Code: Select all

<?php
////////////////////////////////////////////////////////////////////////////////////////
// Class: DbConnector
// Purpose: Connect to a database, MySQL version
///////////////////////////////////////////////////////////////////////////////////////
require_once 'SystemComponent.php';

class DbConnector extends SystemComponent {

//var $theQuery;
var $link;

//*** Function: DbConnector, Purpose: Connect to the database ***
function DbConnector(){

// Load settings from parent class
$settings = SystemComponent::getSettings();

// Get the main settings from the array we just loaded
$host = $settings['localhost'];
$db = $settings['db1'];
$user = $settings['root'];
$pass = $settings['1234'];

// Connect to the database
$this->link = mysql_connect ($host, $user, $pass)or die ("Unable to connect to Database Server");
mysql_select_db($db)or mysql_error();   // die ("There is no such database");
register_shutdown_function(array(&$this, 'close'));

}

//*** Function: query, Purpose: Execute a database query ***
function query($query) {

//$this->theQuery = $query; 
return mysql_query($query);//, $this->link); 
}

//*** Function: fetchArray, Purpose: Get array of query results ***
function fetchArray($result) {

return mysql_fetch_array($result);

}

//** Function: get no or rows ***

function getNoOfRows($result){


return mysql_num_rows($result);
}



//*** Function: close, Purpose: Close the connection ***
function close() {

mysql_close($this->link);

}


}
?>
Then just use an object of the dbConnector class to make the connection on each page:

Code: Select all

<?php
// Require the database class
require_once('includes/DbConnector.php');

// Create an object (instance) of the DbConnector, 
$connector = new DbConnector();    // this line will eshtablish the connection

You can use the same object to use all the functions you declared inside the subclass

Code: Select all

$result = $connector->query('SELECT * FROM user);

I hope this helps.

Posted: Tue Feb 06, 2007 6:42 am
by mikeq
well by saying put it at the start of the page, that didn't negate the ability to use include() or require() in your script.

In general I put the connection settings in a file called config.inc.php

which would have things like

Code: Select all

$Host = "myhost";
$User = "user";
...
you then include that file at the beginning of the script that needs to connect to the database

Code: Select all

include('config.inc.php'); //this is just an example, yes you can use require et al

$Connection = mysql_connect($Host,$User......etc etc);
So the config settings aren't set on each page but in one file.

The answer to the question is, you only have to connect once on each page. Whether you use a class or the example I have shown you are still only making one connection per page.
And do not forget to close the connection by mysql_close(); function on end page or end all job.
It can save memory.
Is that definitely the case, I though that once the script had finished running the database connection is freed up. I never use mysql_close() function.