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
Database Connections
Moderator: General Moderators
-
crazytopu
- Forum Contributor
- Posts: 259
- Joined: Fri Nov 07, 2003 12:43 pm
- Location: London, UK
- Contact:
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 -
then make a subclass of it and put all your fucntions there
Then just use an object of the dbConnector class to make the connection on each page:
You can use the same object to use all the functions you declared inside the subclass
I hope this helps.
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);
}
}
?>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 connectionYou 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.
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
you then include that file at the beginning of the script that needs to connect to the database
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.
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";
...
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);
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.
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.And do not forget to close the connection by mysql_close(); function on end page or end all job.
It can save memory.