[Kuggi]

This advanced tutorial will cover the following subjects:

We want to create a PHP5 OOP class which can help us connect to our MYSQL server.
We could not decide if we wanted to use the MYSQL or MYSQLI connector, so we use them both (and can select which one to use).
We should be able to have multiple connections open at the same time, using both connectors.
We should have a single class only containing the database information (hostname and such) so we can have multiple connections at ones.
We want to test the class using both connectors at the same time in the end of this tutorial.

If you are new to OOP development in PHP5, please read one of the other OOP PHP5 tutorials on this page before reading this.

First, we need to create the class that contains the configurations of our databases:

class config
{
	public $hostname;
	public $username;
	public $password;
	public $database;
	public $prefix;
	public $connector;
	
	function __construct($hostname = NULL, $username = NULL, $password = NULL, $database = NULL, $prefix = NULL, $connector = NULL)
	{
		$this->hostname = !empty($hostname) ? $hostname : "";
		$this->username = !empty($username) ? $username : "";
		$this->password = !empty($password) ? $password : "";
		$this->database = !empty($database) ? $database : "";
		$this->prefix = !empty($prefix) ? $prefix : "";
		$this->connector = !empty($connector) ? $connector : "mysqli";
	}
	
	function __destruct()
	{
		
	}
}

This class will only contain some fields we can use to store our data in and a constructor to feed the fields with data and a deconstructor to clean up the object then we decides to remove it.

The fields are:

hostname: the hostname of your database.
username: the username used to login to your database.
password: the password in clear text to login to your database.
database: the name of the database.
prefix: if you decide to use table prefixes, you should enter the prefix here as: "prefix_".
constructor: this is IMPORTANT: type mysql or mysqli here to select which database connector to use, we decide to use mysqli for you if you can't decide it.

We can use the config class as this in the examples below and in your code:

$config = new config("hostname", "username", "password", "database", "prefix", "connector");

And because of our design here, we can have multiple objects of the class config (to store information about two databases):

$config_db_1 = new config("hostname", "username", "password", "database", "table_prefix", "connector");
$config_db_2 = new config("hostname", "username", "password", "database", "table_prefix", "connector");

So now, we have stored the information of two databases, now we only need the hard part: the db class.

First we creates the class and adds some private variables to store the selected config object in.

class db
{
	private $connection;
	private $selectdb;
	private $lastQuery;
	private $config;
}

These variables should both share the login and server information, but also data such as the open connection and the last query we used (this will be used as an example later).

Now, we know that PHP5 OOP classes needs to have an constructor and deconstructor, lets add them, descriptions follow:

class db
{
	private $connection;
	private $selectdb;
	private $lastQuery;
	private $config;

	function __construct($config)
	{
		$this->config = $config;
	}
	
	function __destruct()
	{
		
	}
}

Constructor:
This function are called every single time we create an object of the db class and takes one argument, the config class object.
If you decide not to use the argument, the variables inside will be assigned some default values, and it is here we select to use the mysqli connector as default.

Destructor:
This function does the clean up and removes everything from the object when we decide not to use it anymore (or the pages that we have build are done loading in the browser).

Before we can use the db class to anything, we need to add some functions to open and close the connections.

Function to open connection: (remember that we wanted to use two connectors here).

public function openConnection()
{
	try
	{
		if($this->config->connector == "mysql")
		{
			$this->connection = mysql_connect($this->config->hostname, $this->config->username, $this->config->password);
			$this->selectdb = mysql_select_db($this->config->database);
		}
		elseif($this->config->connector == "mysqli")
		{
			$this->connection = mysqli_connect($this->config->hostname, $this->config->username, $this->config->password);
			$this->selectdb = mysqli_select_db($this->connection, $this->config->database);
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

This function first look at the selected connector and if we decided to use mysql, it will connect to the database using mysql and in the same way, it can connect to the database using mysqli, it also selects our database using the correct function (depending on if you selected mysql or mysqli as connector).

If you have a fast-moving head, you could see that we have decided to make the openConnection function PUBLIC, the reason for this is that we want to be able to use this class in many ways, we have added a little feature here, so we can use this class to hold the connection open ONES for the hole website or for every single time we want to run a query (I will add an example of what i mean).

The next function is the close connection function:

public function closeConnection()
{
	try
	{
		if($this->config->connector == "mysql")
		{
			mysql_close($this->connection);
		}
		elseif($this->config->connector == "mysqli")
		{
			mysqli_close($this->connection);
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

Again, this function reads our config object to see which connector we are using and then closes the connection of the right connector, this function is also used public, because we want to be able to call it then needed outside the class.

Lets add the latest two functions to our db class:

class db
{
	private $connection;
	private $selectdb;
	private $lastQuery;
	private $config;

	function __construct($config)
	{
		$this->config = $config;
	}
	
	function __destruct()
	{
		
	}

	public function openConnection()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				$this->connection = mysql_connect($this->config->hostname, $this->config->username, $this->config->password);
				$this->selectdb = mysql_select_db($this->config->database);
			}
			elseif($this->config->connector == "mysqli")
			{
				$this->connection = mysqli_connect($this->config->hostname, $this->config->username, $this->config->password);
				$this->selectdb = mysqli_select_db($this->connection, $this->config->database);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}

	public function closeConnection()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				mysql_close($this->connection);
			}
			elseif($this->config->connector == "mysqli")
			{
				mysqli_close($this->connection);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}

}

How much fun do we have now?, we can open and close a connection to a database using both mysql and mysqli connectors, but we can't use the class to anything, the feature we need is to run queries of cause.

But before we adds a function to run a query, we need to think a little, because people on websites can't be trusted, they perform SQL injections if they can, so we need to be sure of that they can't on our classes, what we need is a function to add a " / " char before every " ' " char in the queries, this will stop people from attacking our databases with crappy inputs.

The code for this are:

public function ecapeString($string)
{
	if($this->config->connector == "mysql")
	{
		return mysql_real_escape_string($string);
	}
	elseif($this->config->connector == "mysqli")
	{
		return mysqli_real_escape_string($this->connection, $string);
	}
}

Now we can start on our query function:

public function query($query)
{
	
}

First we have to handle the use of table prefixes:

public function query($query)
{
	$query = str_replace("}", "", $query);
	$query = str_replace("{", $this->config->prefix, $query);
}

If the prefix is defined, we will change the query to contain that pre-stored prefix, this means that all our table names in queries, should be written as {table}.

The next thing is a big bunch of code, but we place it under the if in the function:

public function query($query)
{
	$query = str_replace("}", "", $query);
	$query = str_replace("{", $this->config->prefix, $query);

	try
	{
		if(empty($this->connection))
		{
			$this->openConnection();
			
			if($this->config->connector == "mysql")
			{
				$this->lastQuery = mysql_query($this->ecapeString($query));
			}
			elseif($this->config->connector == "mysqli")
			{
				$this->lastQuery = mysqli_query($this->connection, $this->ecapeString($query));
			}
			
			$this->closeConnection();
			
			return $this->lastQuery;
		}
		else
		{
			if($this->config->connector == "mysql")
			{
				$this->lastQuery = mysql_query($this->ecapeString($query));
			}
			elseif($this->config->connector == "mysqli")
			{
				$this->lastQuery = mysqli_query($this->connection, $this->ecapeString($query));
			}
			
			return $this->lastQuery;
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

First we changes the prefix as described, next we checks if the connection to the database is open, if not, we need to open it (if the connection is closed here, we know that we have selected to open and close the connection every single time we run a query, if the connection is open here, we know that we have decided to have one connection open for every browser used to access our site, and to save server resources), last we stores the results of the query in the lastQuery variable.

The last thing, we will of cause also handle the database connector here. but we did store the data in the lastQuery variable, this is because we can use this to output the last query used by the user.

Let us add a little bonus function to output the last used query to the browser ( if you need it ):

public function lastQuery()
{
	return $this->lastQuery;
}

simple?.

Lets put it together as it is now:

class db
{
	private $connection;
	private $selectdb;
	private $lastQuery;
	private $config;

	function __construct($config)
	{
		$this->config = $config;
	}
	
	function __destruct()
	{
		
	}

	public function openConnection()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				$this->connection = mysql_connect($this->config->hostname, $this->config->username, $this->config->password);
				$this->selectdb = mysql_select_db($this->config->database);
			}
			elseif($this->config->connector == "mysqli")
			{
				$this->connection = mysqli_connect($this->config->hostname, $this->config->username, $this->config->password);
				$this->selectdb = mysqli_select_db($this->connection, $this->config->database);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}

	public function closeConnection()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				mysql_close($this->connection);
			}
			elseif($this->config->connector == "mysqli")
			{
				mysqli_close($this->connection);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
	
	public function ecapeString($string)
	{
		return addslashes($string);
	}

	public function query($query)
	{
		$query = str_replace("}", "", $query);
		$query = str_replace("{", $this->config->prefix, $query);
	
		try
		{
			if(empty($this->connection))
			{
				$this->openConnection();
				
				if($this->config->connector == "mysql")
				{
					$this->lastQuery = mysql_query($this->ecapeString($query));
				}
				elseif($this->config->connector == "mysqli")
				{
					$this->lastQuery = mysqli_query($this->connection, $this->ecapeString($query));
				}
			
				$this->closeConnection();
				
				return $this->lastQuery;
			}
			else
			{
				if($this->config->connector == "mysql")
				{
					$this->lastQuery = mysql_query($this->ecapeString($query));
				}
				elseif($this->config->connector == "mysqli")
				{
					$this->lastQuery = mysqli_query($this->connection, $this->ecapeString($query));
				}
				
				return $this->lastQuery;
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}

	public function lastQuery()
	{
		return $this->lastQuery;
	}
}

It could be fun to see if the connection to the database is open, let us add a function that can do that for us:

public function pingServer()
{
	try
	{
		if($this->config->connector == "mysql")
		{
			if(!mysql_ping($this->connection))
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		elseif($this->config->connector == "mysqli")
		{
			if(!mysqli_ping($this->connection))
			{
				return false;
			}
			else
			{
				return true;
			}
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

This function will, depending on the connector, ping the server to see if we have an active connection to it.

As of now, we can connect to a database, close the connection, ping the server and run a query, but we can't handle the returned data from the database, so we need 4 new functions to do just that:

hasRows: to see if the data we got has any rows in it.
countRows: returns the number of rows in the returned data.

Some people prefer to use the fetch_assoc functions to handle the data, others the fetch_arry, so we need to be large here and add them both:

fetchAssoc: returns all data with the mysql_fetch_assoc or mysqli_fetch_assoc functions.
fetchArray: returns add data with the mysql_fetch_array or mysqli_fetch_array functions.

The code for the functions is:

public function hasRows($result)
{
	try
	{
		if($this->config->connector == "mysql")
		{
			if(mysql_num_rows($result)>0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		elseif($this->config->connector == "mysqli")
		{
			if(mysqli_num_rows($result)>0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

public function countRows($result)
{
	try
	{
		if($this->config->connector == "mysql")
		{
			return mysql_num_rows($result);
		}
		elseif($this->config->connector == "mysqli")
		{
			return mysqli_num_rows($result);
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

public function fetchAssoc($result)
{
	try
	{
		if($this->config->connector == "mysql")
		{
			return mysql_fetch_assoc($result);
		}
		elseif($this->config->connector == "mysqli")
		{
			return mysqli_fetch_assoc($result);
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

public function fetchArray($result)
{
	try
	{
		if($this->config->connector == "mysql")
		{
			return mysql_fetch_array($result);
		}
		elseif($this->config->connector == "mysqli")
		{
			return mysqli_fetch_array($result);
		}
	}
	catch(exception $e)
	{
		return $e;
	}
}

These functions are the last ones to add to our class, and then we can view some examples of the use.

The complete classes:

class db
{
	private $connection;
	private $selectdb;
	private $lastQuery;
	private $config;

	function __construct($config)
	{
		$this->config = $config;
	}
	
	function __destruct()
	{
		
	}

	public function openConnection()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				$this->connection = mysql_connect($this->config->hostname, $this->config->username, $this->config->password);
				$this->selectdb = mysql_select_db($this->config->database);
			}
			elseif($this->config->connector == "mysqli")
			{
				$this->connection = mysqli_connect($this->config->hostname, $this->config->username, $this->config->password);
				$this->selectdb = mysqli_select_db($this->connection, $this->config->database);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}

	public function closeConnection()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				mysql_close($this->connection);
			}
			elseif($this->config->connector == "mysqli")
			{
				mysqli_close($this->connection);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
	
	public function ecapeString($string)
	{
		return addslashes($string);
	}

	public function query($query)
	{
		$query = str_replace("}", "", $query);
		$query = str_replace("{", $this->config->prefix, $query);
	
		try
		{
			if(empty($this->connection))
			{
				$this->openConnection();
				
				if($this->config->connector == "mysql")
				{
					$this->lastQuery = mysql_query($this->ecapeString($query));
				}
				elseif($this->config->connector == "mysqli")
				{
					$this->lastQuery = mysqli_query($this->connection, $this->ecapeString($query));
				}
			
				$this->closeConnection();
				
				return $this->lastQuery;
			}
			else
			{
				if($this->config->connector == "mysql")
				{
					$this->lastQuery = mysql_query($this->ecapeString($query));
				}
				elseif($this->config->connector == "mysqli")
				{
					$this->lastQuery = mysqli_query($this->connection, $this->ecapeString($query));
				}
				
				return $this->lastQuery;
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}

	public function lastQuery()
	{
		return $this->lastQuery;
	}

	public function pingServer()
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				if(!mysql_ping($this->connection))
				{
					return false;
				}
				else
				{
					return true;
				}
			}
			elseif($this->config->connector == "mysqli")
			{
				if(!mysqli_ping($this->connection))
				{
					return false;
				}
				else
				{
					return true;
				}
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
	
	public function hasRows($result)
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				if(mysql_num_rows($result)>0)
				{
					return true;
				}
				else
				{
					return false;
				}
			}
			elseif($this->config->connector == "mysqli")
			{
				if(mysqli_num_rows($result)>0)
				{
					return true;
				}
				else
				{
					return false;
				}
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
	
	public function countRows($result)
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				return mysql_num_rows($result);
			}
			elseif($this->config->connector == "mysqli")
			{
				return mysqli_num_rows($result);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
	
	public function fetchAssoc($result)
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				return mysql_fetch_assoc($result);
			}
			elseif($this->config->connector == "mysqli")
			{
				return mysqli_fetch_assoc($result);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
	
	public function fetchArray($result)
	{
		try
		{
			if($this->config->connector == "mysql")
			{
				return mysql_fetch_array($result);
			}
			elseif($this->config->connector == "mysqli")
			{
				return mysqli_fetch_array($result);
			}
		}
		catch(exception $e)
		{
			return $e;
		}
	}
}

class config
{
	public $hostname;
	public $username;
	public $password;
	public $database;
	public $prefix;
	public $connector;
	
	function __construct($hostname = NULL, $username = NULL, $password = NULL, $database = NULL, $prefix = NULL, $connector = NULL)
	{
		$this->hostname = !empty($hostname) ? $hostname : "";
		$this->username = !empty($username) ? $username : "";
		$this->password = !empty($password) ? $password : "";
		$this->database = !empty($database) ? $database : "";
		$this->prefix = !empty($prefix) ? $prefix : "";
		$this->connector = !empty($connector) ? $connector : "mysqli";
	}
	
	function __destruct()
	{
		
	}
}

And now to the fun part, examples:

For every example, we have placed the two classes in a file called: db.class.php

For every config object, you can use: MYSQL or MYSQLI.

1. example: a simple connection to a database, using only one connection for each browser.

<?PHP
	// We start by including the classes.
	include('db.class.php');
	
	// We need to have 1 config object with all data in.
	$config = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…

	// Now we need to have access to the db class, we uses the config object to configure the db object.
	$db = new db($config);

	// We can now open the connection to the database.
	$db->openConnection();

	// If your config details are right, we are now connected to a database, lets test the connection before we run queries.
	$are_we_online = $db->pingServer();
	// The variable $are_we_online should be true (or 1) if we are connected to the server.
	echo "Are we online: " . $are_we_online; // prints 0 or 1.

	// Let us run a query.
	$sql = $db->query("SELECT * FROM {table}");

	// The variable $sql will now hold the data returned from the database, we can now work with it.
	
	// Does it have rows ?
	$hasRows = $db->hasRows($sql);
	echo "Does it have rows: " . $hasRows; // prints 0 or 1 (true or false).
	
	// How many rows does it have.
	$countRows = $db->countRows($sql);
	echo "How many rows: " . $countRows; // returns the number of rows. 
	
	// We can get the data from the fetch_assoc function.
	$result = $db->fetchAssoc($sql);

	// We can get the data from the fetch_array function.
	$result = $db->fetchArray($sql);

	 We can even print out the latest used query:
	echo $db->lastQuery();

	// As a last thing, close the connection.
	$db->closeConnection();
?>

2. example: connection to a single database using the "one connection pr query"-method, this will slowdown your code a little and generate more resource-use on the server.

<?PHP
	// We start by including the classes.
	include('db.class.php');
	
	// We need to have 1 config object with all data in.
	$config = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…

	// Now we need to have access to the db class, we uses the config object to configure the db object.
	$db = new db($config);

	// We don't need to start the connection here, because we opens it every single time we runs a query.

	// If your config details are right, we are now connected to a database, lets test the connection before we run queries.
	$are_we_online = $db->pingServer();
	// The variable $are_we_online should be true (or 1) if we are connected to the server.
	echo "Are we online: " . $are_we_online; // prints 0 or 1.

	// Let us run a query.
	$sql = $db->query("SELECT * FROM {table}");

	// The variable $sql will now hold the data returned from the database, we can now work with it.
	
	// Does it have rows ?
	$hasRows = $db->hasRows($sql);
	echo "Does it have rows: " . $hasRows; // prints 0 or 1 (true or false).
	
	// How many rows does it have.
	$countRows = $db->countRows($sql);
	echo "How many rows: " . $countRows; // returns the number of rows. 
	
	// We can get the data from the fetch_assoc function.
	$result = $db->fetchAssoc($sql);

	// We can get the data from the fetch_array function.
	$result = $db->fetchArray($sql);

	 We can even print out the latest used query:
	echo $db->lastQuery();

	// We don't need to close the connection here, because we only starts it when needed.
?>

3. example: multiple connections open at ones.

<?PHP
	// We start by including the classes.
	include('db.class.php');
	
	// We need to have 2 config objects with all data in.
	$config1 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
	$config2 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…

	// Now we need to have access to the db class, we uses the config object to configure the db object.
	$db1 = new db($config1);
	$db2 = new db($config2);

	// We can now open the connection to the databases.
	$db1->openConnection();
	$db2->openConnection();

	// If your config details are right, we are now connected to the databases, lets test the connection before we run queries.
	$are_we_online_1 = $db1->pingServer();
	$are_we_online_2 = $db2->pingServer();
	// The variable $are_we_online should be true (or 1) if we are connected to the server.
	echo "Are we online width 1: " . $are_we_online_1; // prints 0 or 1.
	echo "Are we online width 2: " . $are_we_online_2; // prints 0 or 1.

	// Let us run a query.
	$sql1 = $db1->query("SELECT * FROM {table}");
	$sql2 = $db2->query("SELECT * FROM {table}");

	// The variable $sql will now hold the data returned from the database, we can now work with it.
	
	// Does it have rows ?
	$hasRows1 = $db1->hasRows($sql1);
	$hasRows2 = $db2->hasRows($sql2);
	echo "Does it have rows: " . $hasRows1; // prints 0 or 1 (true or false).
	echo "Does it have rows: " . $hasRows2; // prints 0 or 1 (true or false).
	
	// How many rows does it have.
	$countRows1 = $db1->countRows($sql1);
	$countRows2 = $db2->countRows($sql2);
	echo "How many rows 1: " . $countRows1; // returns the number of rows. 
	echo "How many rows 2: " . $countRows2; // returns the number of rows. 
	
	// We can get the data from the fetch_assoc function.
	$result1 = $db1->fetchAssoc($sql1);
	$result2 = $db2->fetchAssoc($sql2);

	// We can get the data from the fetch_array function.
	$result1 = $db1->fetchArray($sql1);
	$result2 = $db2->fetchArray($sql2);

	 We can even print out the latest used query:
	echo $db1->lastQuery();
	echo $db2->lastQuery();

	// As a last thing, close the connection.
	$db1->closeConnection();
	$db2->closeConnection();
?>

4. example: using multiple connectors but only have the connection open when needed.

<?PHP
	// We start by including the classes.
	include('db.class.php');
	
	// We need to have 2 config objects with all data in.
	$config1 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
	$config2 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…

	// Now we need to have access to the db class, we uses the config object to configure the db object.
	$db1 = new db($config1);
	$db2 = new db($config2);

	// Because we want only to have an open connection when needed, we don't have to open it here.
	

	// If your config details are right, we are now connected to the databases, lets test the connection before we run queries.
	$are_we_online_1 = $db1->pingServer();
	$are_we_online_2 = $db2->pingServer();
	// The variable $are_we_online should be true (or 1) if we are connected to the server.
	echo "Are we online width 1: " . $are_we_online_1; // prints 0 or 1.
	echo "Are we online width 2: " . $are_we_online_2; // prints 0 or 1.

	// Let us run a query.
	$sql1 = $db1->query("SELECT * FROM {table}");
	$sql2 = $db2->query("SELECT * FROM {table}");

	// The variable $sql will now hold the data returned from the database, we can now work with it.
	
	// Does it have rows ?
	$hasRows1 = $db1->hasRows($sql1);
	$hasRows2 = $db2->hasRows($sql2);
	echo "Does it have rows: " . $hasRows1; // prints 0 or 1 (true or false).
	echo "Does it have rows: " . $hasRows2; // prints 0 or 1 (true or false).
	
	// How many rows does it have.
	$countRows1 = $db1->countRows($sql1);
	$countRows2 = $db2->countRows($sql2);
	echo "How many rows 1: " . $countRows1; // returns the number of rows. 
	echo "How many rows 2: " . $countRows2; // returns the number of rows. 
	
	// We can get the data from the fetch_assoc function.
	$result1 = $db1->fetchAssoc($sql1);
	$result2 = $db2->fetchAssoc($sql2);

	// We can get the data from the fetch_array function.
	$result1 = $db1->fetchArray($sql1);
	$result2 = $db2->fetchArray($sql2);

	 We can even print out the latest used query:
	echo $db1->lastQuery();
	echo $db2->lastQuery();

	// We don't need to close anything here, because we closes the connection after every query are run.
?>

I hope that this ( little and advanced ) tutorial on a dual connector for mysql can help you in your development.

EDIT: changed the name of the __destruct() functions to be valid, changed mysqli_query to have two arguemnts, changed constructor to use the config class the right way, changed $this->connector to $this->config->connector and changed PingServer function, changed a single variable name.

This post has been edited by Kuggi: 27 March 2011 - 04:17 AM

[Dormilich]

unfortunately I have to do some serious nitpicking …

the deconstructor of a class is called __destruct(), so __deconstruct() won’t be called. additionally __destruct() is called, when the object is unset … thus using unset() in the deconstructor is pointless (unsetting an already unsetting object) to dangerous (infinite loop). ->closeConnection() is what you would normally put in the destructor.

the config class (besides the fact that class names should start upper-case), never ever make private data (like login credentials) public, use protected or private instead. add accessor method to get the data out.

when you pass the config object to the DB class, why do you rewrite all the data into the DB class? you already have an object for that: the config class.

class DB
{
    protected $config = NULL;

    public function __construct(Config $conf)
    {
        $this->config = $conf;
    }
}

// usage in a method:
$host = $this->config->getHost();

the type hinting makes sure, that we get the object we expect. ideally this should be made by Interfaces, but that’s for later.

when using PHP 5, don’t consider using the mysql functions, they’re outdated!

when using OOP & mysqli, consider using mysqli’s OOP style to begin with.

try…catch: neither mysql nor mysqli use Exceptions.

if you escape data, use ->real_escape_string() and not addslashes(), there is a reason for that function.

num_rows: depending on the type of query (SELECT vs. INSERT/UPDATE/DELETE) you use different functions (_num_rows() vs. _affected_rows()).



sorry for making so much complaints, but they have to be made.

best regards, Dormi

This post has been edited by Dormilich: 22 March 2011 - 03:40 AM

[Kuggi]

Dormilich, on 22 March 2011 - 03:22 AM, said:

unfortunately I have to do some serious nitpicking …

the deconstructor of a class is called __destruct(), so __deconstruct() won’t be called. additionally __destruct() is called, when the object is unset … thus using unset() in the deconstructor is pointless (unsetting an already unsetting object) to dangerous (infinite loop). ->closeConnection() is what you would normally put in the destructor.

the config class (besides the fact that class names should start upper-case), never ever make private data (like login credentials) public, use protected or private instead. add accessor method to get the data out.

when you pass the config object to the DB class, why do you rewrite all the data into the DB class? you already have an object for that: the config class.

class DB
{
    protected $config = NULL;

    public function __construct(Config $conf)
    {
        $this->config = $conf;
    }
}

// usage in a method:
$host = $this->config->getHost();

the type hinting makes sure, that we get the object we expect. ideally this should be made by Interfaces, but that’s for later.

when using PHP 5, don’t consider using the mysql functions, they’re outdated!

when using OOP & mysqli, consider using mysqli’s OOP style to begin with.

try…catch: neither mysql nor mysqli use Exceptions.

if you escape data, use ->real_escape_string() and not addslashes(), there is a reason for that function.

num_rows: depending on the type of query (SELECT vs. INSERT/UPDATE/DELETE) you use different functions (_num_rows() vs. _affected_rows()).



sorry for making so much complaints, but they have to be made.

best regards, Dormi

First, thanks for pointing all that out

1) i wanted to store all information in their own variable to make it simple to read ( i think it is ).
2) I know that mysql connectors in PHP5 are outdated, but i wanted to create a function which could handle all versions of mysql servers and because of that we should use both mysql and mysqli. (if you read in the constructor of the db class, the mysqli are default selected).
3) i used the try catch to catch all mysql_error() and mysqli_error() codes and return them to the screen.
4) i have read that the mysql_ecape_string() is outdated and on the php.net site: "This function has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged." http://dk2.php.net/m...cape-string.php
5) i could add a function to handle the _affected_rows() that are missing.

Beside your comments, i myself found that $mysqli_query($query) are missing an argument, so i will update the tutorial to take care of that and your comment about the config class.

Thanks for the comment

[Dormilich]

1) although it might be more simple to read, it’s against the principle of OOP (data encapsulation). or in other words, if you have the login data in the DB class, why using a Config class at all?

2) mysqli supports MySQL 4.1+, curently we have 5.1 and I doubt anyone is using MySQL 4.0 and below

3) Errors are not Exceptions, those are two completely different ways of error handling. that’s why the or die(mysql_error()); construct is used so often. actually, if it would throw Exceptions, your PHP would complain about uncaught Exceptions, which just doesn’t happen for mysql/mysqli.

4) you’re right, mysql_escape_string() is deprecated, but I was talking about mysql_real_escape_string().


PS. you still need to fix your destructor. unset() does not belong there.

This post has been edited by Dormilich: 22 March 2011 - 05:11 AM

[Kuggi]

Dormilich, on 22 March 2011 - 05:07 AM, said:

1) although it might be more simple to read, it’s against the principle of OOP (data encapsulation). or in other words, if you have the login data in the DB class, why using a Config class at all?

2) mysqli supports MySQL 4.1+, curently we have 5.1 and I doubt anyone is using MySQL 4.0 and below

3) Errors are not Exceptions, those are two completely different ways of error handling. that’s why the or die(mysql_error()); construct is used so often. actually, if it would throw Exceptions, your PHP would complain about uncaught Exceptions, which just doesn’t happen for mysql/mysqli.

4) you’re right, mysql_escape_string() is deprecated, but I was talking about mysql_real_escape_string().


PS. you still need to fix your destructor. unset() does not belong there.

1) i have changed the constructor to use the config class in the right way.
2) i know of some web hosting companies in the EU which uses mysql server 4.0
3) do you want me to add the or die(); and remove the try catches ? i can read out the errors as it is now
4) i have added the mysql and mysqli _real_escape_string functions.

I hope the results are good now

[Dormilich]

1) you can add type hinting in the constructor to avoid passing the wrong object.

3) just remove the try…catch block. you don’t need to use or die() just some sensible way of error handling (Exceptions would be very good now)

there are some more things that could be done, but maybe later.

[Kuggi]

Dormilich, on 22 March 2011 - 11:41 PM, said:

1) you can add type hinting in the constructor to avoid passing the wrong object.

3) just remove the try…catch block. you don’t need to use or die() just some sensible way of error handling (Exceptions would be very good now)

there are some more things that could be done, but maybe later.

I think that the class as it is now is an okay starting point for people who need to connect to a database ( i know the code works for me )

And i agree that it could be made in another smart way, but this is how i wanted to code it and you can read the article for my result if you can code it in a more "OOP PHP5" way, you are free to submit your own classes.. fx as a snippet or a new tutorial on DIC

[Dormilich]

Kuggi, on 23 March 2011 - 08:44 AM, said:

I think that the class as it is now is an okay starting point for people who need to connect to a database ( i know the code works for me )

I don’t insist on type hinting, just mentioning it as it can prevent a lot of errors.

Kuggi, on 23 March 2011 - 08:44 AM, said:

And i agree that it could be made in another smart way, but this is how i wanted to code it and you can read the article for my result if you can code it in a more "OOP PHP5" way, you are free to submit your own classes.. fx as a snippet or a new tutorial on DIC

I did, just check my tutorials and snippets.

This post has been edited by Dormilich: 23 March 2011 - 12:50 AM

[Dogstopper]

@Kuggi, if you want to update your tutorial, feel free to send a Moderator an updated copy. I have read Dormilich's comments and they seem to be good revisions to make.

[Dormilich]

@Kuggi: if you’re interested in how I would go at it …

Explanation:
The first part is the configuration. from the DI point-of-view, you only need to guarantee that the methods you use exist, no matter which class provides them. This is one more reason not to use public properties.

interface iDBConfigReader
{
	public function getHost();
	public function getUser();
	public function getPassword();
	public function getDatabase();
	public function getPort();
	public function getSocket();
}

interface iDBConfigWriter
{
	public function setHost();
	public function setUser();
	public function setPassword();
	public function setDatabase();
	public function setPort();
	public function setSocket();
}

final: class cannot be extended. otherwise it should be pretty much self-explaining. The only assumption I use here is that the default values for mysql (host, username, password) are the same as for mysqli (IMHO, a sensible assumption).

final class MySQLConfiguration implements iDBConfigReader, iDBConfigWriter
{
	private
		  $host
		, $username
		, $password
		, $dbname
		, $port
		, $socket
	;
	
	public function __construct(
		  $host = NULL
		, $user = NULL
		, $pass = NULL
		, $db   = ""
		, $port = 3306
		, $sock = NULL
	)
	{
		$this->setHost($host);
		$this->setUser($user);
		$this->setPassword($pass);
		$this->setDatabase($db);
		$this->setPort($port);
		$this->setSocket($sock);
	}
	
	public function getHost()
	{
		return $this->host;
	}
	
	public function setHost($host = NULL)
	{
		if (NULL === $host)
		{
			$this->host = NULL;
		}
		elseif (!is_string($host))
		{
			$this->host = ini_get("mysqli.default_host");
		}
		else
		{
			$this->host = $host;
		}
		return $this->host;
	}
	
	public function getUser()
	{
		return $this->username;
	}
	
	public function setUser($user = NULL)
	{
		if (!is_string($user))
		{
			$user = ini_get("mysqli.default_user");
		}
		$this->username = $user;
		return $this->username;
	}
	
	public function getPass()
	{
		return $this->password;
	}
	
	public function setPass($pass = NULL)
	{
		if (NULL === $pass)
		{
			$this->password = NULL;
		}
		elseif (!is_string($pass))
		{
			$this->password = ini_get("mysqli.default_pw");
		}
		else
		{
			$this->password = $pass;
		}
		return $this->password;
	}
	
	public function getDatabase()
	{
		return $this->dbname;
	}
	
	public function setDatabase($db = NULL)
	{
		$this->dbname = (string) $db;
		return $this->dbname
	}
	
	public function getPort()
	{
		return $this->port;
	}
	
	public function setPort($port = NULL)
	{
		if (!is_int($port))
		{
			$port = ini_get("mysqli.default_port");
		}
		$this->port = $port;
		return $this->port;
	}
	
	public function getSocket()
	{
		return $this->socket;
	}
	
	public function setSocket($sock = NULL)
	{
		if (!is_string($sock))
		{
			$sock = ini_get("mysqli.default_socket");
		}
		$this->socket = $sock;
		return $this->socket;
	}
}

Next is the DB class itself. Since you can have either mysql or mysqli the obvious choice is to use a Factory Pattern. The Factory class decides itself (right, there is no user choice!) which to use. If neither of the handlers can handle the database (i.e. wrong password) an Exception is thrown.

interface iFactory
{
	public static function createDatabaseHandler(iDBConfigReader $conf);
}

class DBFactory implements iFactory
{
	public static function createDatabaseHandler(iDBConfigReader $conf)
	{
		try
		{
			return new MySQLi_Handler($conf);
		}
		catch (RuntimeException $e)
		{
			return new MySQL_Handler($conf);
		}
	}
}

Now for the really interesting part. Each of the handler classes deals with its database layer (mysqli OR mysql). This is used to prevent code duplication (no need to test, which layer to use in every method). The interface assures that both classes use the same methods, so that the user does not have to concern itself whether he uses mysql or mysqli.

interface iDBHandler
{
	// just an example, certainly more methods are needed in the final interface(s)
	public function query($sql);
}

class MySQLi_Handler implements iDBHandler
{
	protected $connection = NULL;
	
	public function __construct(iDBConfigReader $conf)
	{
		$host = $conf->getHost();
		$user = $conf->getUser();
		$pass = $conf->getPassword();
		$db   = $conf->getDatabase();
		$port = $conf->getPort();
		$sock = $conf->getSocket();
		
		$cnx  = new MySQLi($host, $user, $pass, $db, $port, $sock);

		if (mysqli_connect_error())
		{
			throw new RuntimeException(mysqli_connect_error(), mysqli_connect_errno());
		}
		$this->connection = $cnx;
	}
	
	public function __destruct()
	{
		$this->connection->close();
	}
	
	public function query($sql)
	{
		// make the query using MySQLi
	}
}

class MySQL_Handler implements iDBHandler
{
	protected $connection = NULL;
	
	public function __construct(iDBConfigReader $conf)
	{
		$host = $conf->getHost();
		$user = $conf->getUser();
		$pass = $conf->getPassword();
		$db   = $conf->getDatabase();
		
		$cnx  = mysql_connect($host, $user, $pass);
		
		if (!$cnx or !mysql_connect_db($db, $cnx))
		{
			throw new RuntimeException(mysql_error(), mysql_errno());
		}
		$this->connection = $cnx;
	}
	
	public function __destruct()
	{
		mysql_close($this->connection);
	}
	
	public function query($sql)
	{
		// make the query using mysql
	}
}

Usage:

try
{
	$database = new MySQLConfiguration("localhost", "login", "password", "mydb");
	$db = DBFactory::createDatabaseHandler($database);
	$db->query("SELECT somefield FROM mytable");
	// …
}
catch (Exception $e)
{
	echo "<p>sorry, there is an error.</p>";
	send_error($e->getMessage());
}

This post has been edited by Dormilich: 23 March 2011 - 05:13 AM