If you develop websites in PHP5 or later, you know to the 'new stuff' in PHP5 called OOP, this is a little guide to create a class to your website, so you can connect to your mysql database the OOP way.

First we create a class called "db".



We need some information to your database, so we can connect to the right one, we need information for the Hostname, Database name, username and password to the mysql database.

So now we create some variables to store the information:



Before we can use the variables we need to set the information in the class contructor so the content of the variables is set every time we use the class.



Here you can see where to place the constructor:



The constructor runs every time you create an object of the class.

Now we can start to write our first function, the function that opens the connection to the database.



The function is placed under the constructor, but what does it do ?

the open_connection function trys to call the mysql_connect function and places the new conenction in the variable $this->connect, after that, it trys to select the right database and places the selected database in the variable $this->select_db, if the two trys returns any errors, then the function runs the Catch and returns the error message to the user.

Now our class look like:



But, now the conenction is open, how about closing it ?

So now we create an function to close the connection, we can't let the database be open right.



We can place the function under the function thats opens the connection.



Then, now its time to use our db class to something usefull, but how do we do that ?

First of all, we need to create some kind of function to handle all our queries to and from the database, so we don't need to rewrite it all the time, remember that an website uses a lot of queries to get all the content.

Lets start with the funtion to run queries.



And what do that function do ?

The function opens the connection to the database and trys to run the query that the user writes, and returns the result to the user, so he or she can do whatever its needed to make the results readable for the viewer of the website.

Lets place it in the class:



Now, we can start to use the class on our website, in the top of the index page, we starts by including the class, i have placed the class in a file called db.php.



But thats not all, we need to initialize the class to use the functions.



NOW we are ready to rock the web, lets make a simple test, to be sure that the class works.



This code should not work at all on your site, but gives you an idea of how you can use the class.

I hope that you can use this little guide to OOP Database connections.

Great tutorial!
I have a question, though:
Is there a difference between assigning the according values to the user, host etc. in the constructor ( like you did ), and assigning them as we declare the properties?

class db
{
     private $hostname='foo';
     private $username='dude';
     private $password='ilikepie';
     private $database='dbnamegoeshere';
     private $connect;
     private $select_db;
}    

If you include actual connection information in the class declaration, then your class is only usable for that exact connection, which pretty much defeats the whole re usability aspect of OOP.
In the tutorial it didn't parse any arguments for the constructor, but ideally the constructor would be passed in the actual hostname and other details, which it would then use. That way you can use the same general db class for all your connections.

You can do it both ways

Would it be efficient to create a parent class and then redefine the needed connection arguments in its children and using each of them for whatever connection we need?

class connection1 extends connection
{
 private $database='anotherdb';
}

It is more safe and normal to have the connection string at one place, so i don't think that you should do it that way.

Indeed. Personally, I would recommend having a file called config.php that sets those variables. Something like this:

$config['host'] = "host";
$config['username'] = "username";
$config['password'] = "password";

And add this to your .htaccess file:

<Files config.php>
order allow,deny
deny from all
</Files>

This will prevent others from accessing it externally, but will still allow your script to work with it.

You can load it up into a class like this (db.class.php):

class db {

	public function __construct($config) {
		mysql_connect($config['hostname'],$config['username'],$config['password']) or die("Could not connect!");
		return true;
	}

	public function select_db($database) {
		mysql_select_db($database) or die("Failed to select database!");
		return true;
	}
}

And pass it along like this:

require("config.php");
require("db.class.php");

$database = "Database you want to use.  You can also set this in config.php if you prefer."
$db = new db($config);
$db->select_db($database);

Hope this is useful for you

This post has been edited by Valek: 8 Feb, 2009 - 08:12 PM

I did a small change to this code. now it's working perfectly. please try and enjoy oop.

here is the code...

$sql=$db->query("SELECT data FROM table WHERE something = 'something'");
if($sql)
{
while($r = mysql_fetch_array($sql))
{
echo $r['data'];
}
}

This may be a dumb question but, but what would be the advantages/disadvantages to using mysql_pconnect, instead of mysql_connect and having to open and close the database connection for every query?

Hi guys,

Just thought i'd share my 2cents. Can't say i am a guru and only just recently picked up PHP again after a 2year absence. Ventured into another language when i switched jobs.

in my config.php



in my class-db.php


in my web application pages


I used to use $config['DB_HOST'], etc in my config file, until recently when discovered the use of define(), and i found it an eyesore *hehe* to see db($config) within my code pages.

How to use in class?



Thank

This post has been edited by mochitto: 17 Nov, 2009 - 02:42 PM