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

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.