DEPRECATED: some of the information in this tutorial may still be useful if you're curious about classes in general. But the
usage of mysql_connect()/etc. functions will not work in PHP 7. Instead, checkout mysqli_connect().
The intent of this tutorial is to introduce you to some of the basic ideas behind Object Oriented Programming (OOP) in PHP. One of the big things to note when reading this is that it is not an introduction to the theoretical foundations of OOP, but more of a "this is how you can use objects in PHP". I do not cover the topics of public vs. private member variables, inheritance, or anything else that makes OOP as powerful as it is. The purpose here is to just get used to how objects work in PHP (or in general). Also, I will be writing this tutorial for PHP 5, which means it is not directly compatible with PHP 4. By that I mean, you can easily change it to work with PHP 4, but it won't be copy/paste (note: any class wrote for PHP 4 should work without modification in PHP 5). For more info, check out php.net's New Object Model.
I'm assuming by now you already know the basics of PHP (i.e. how to make a php file...). So, the first thing you need when making an object is a class definition:
<?php /** Interface with MySQL and perform basic error handling. */ class MySQL_object { /** * Construct the object with the basic amount of info needed to connect to a database. * @param $host The db host * @param $user The db username * @param $password Said user's password * @param $line The line number (optional) */ function __construct($host, $user, $password, $line=NULL){ } } ?>
This is a commented version of the basic setup you need to start making a class in PHP (for more information on the particular format
of the comments I'll be using in the code throughout this tutorial, visit the
phpDoc website). The __construct() function is actually optional but
in this case it wouldn't make sense not to take advantage of it.
The exact purpose of __construct() is to setup the object with some basic info. It will perform the basic setup to make
sure your object has all the information it needs. Generally speaking, this involves initializing the member variables to some default
value (or value passed in through the argumens). In this case, we will also be setting up the connection to our database in the
constructor (as opposed to making a separate connect() function).
That's all well and good, but how do you even use objects? There's a function definition in there, but if you've ever used functions
before, you'll notice that you can't exactly call __construct() (in fact, if you try, you'll get a fatal error). So, in
short, here is how you call it:
<?php $db = new MySQL_object('localhost', 'USERNAME', 'PASSWORD', __LINE__); ?>
Here we have a couple of things happening:
- We are actually calling
__construct(); the constructor of an object is what is called when you instantiate a new instance of it (ref: Constructors and Destructors) - We are passing our login credentials to the object (which need to be changed by you to your actual database login credentials)
- And we are also passing the current line number thanks to PHP's Magic Constants
But we still don't have anywhere to store this information, nor do we even use them to connect to the database. We don't actually need to store the login credentials (doing so would be very bad practice as the password could later be leaked accidentally and the last thing the internet needs is your password floating about), but we will need to store a pointer to the connection we establish.
<?php /** Interface with MySQL and perform basic error handling. */ class MySQL_object { public $connection; public $result; /** * Construct the object with the basic amount of info needed to connect to a database. * @param $host The db host * @param $user The db username * @param $password Said user's password * @param $line The line number (optional) */ function __construct($host, $user, $password, $line=NULL){ if(!($this->connection = @mysql_connect($host, $user, $password))){ die('Unable to connect to host'.($line !== NULL ? ' on line '.$line : '').'<br>'.mysql_error()); } } } ?>
Here we get into some weirdness. First off, at the top, you see the addition of two lines, one of which looks like "public
$connection;". The second is inside of __construct(), you should see "$this->connection." As I said
earlier I wasn't going to get into the issue of public vs. private variables, but if you want to know more you can check out php.net's
description of visibility. Now, whats happening
there is that a member variable is being added to the MySQL_object class.
Member variables stick with each new object that is created (so long as you avoid making them
static), which means every time you see a call to
new MySQL_object, the variable you store the object to will have it's own set of member variables which can be accessed later.
In this case, we are accessing the variable through the self-referential $this pseudo-variable. Where you see
$this->connection, it is actually referring to the variable we declared with public $connection;. More
specificially, it is referring to the $connection variable that associates with the object we instantiated ($db
in this case). You can think of $this as an alias; any given call to a method (or constructor) will refer to what ever
object preceeded the "->" in the call to that method (or before the "=" in the instantiation).
So now that we have a connection, we need to do something with that lingering $result variable. Specifically, we are
going to make a function to handle making queries to the database. The reason this is useful is because PHP's mysql_query()
function can return false and throw an error. If we don't handle the error at the point mysql_query() is called, it can
show up later and cause headaches when we try to fetch the result.
In order to make this function, we first need to make something to select the database to run our queries against (otherwise MySQL won't
know where to look for the tables you are referring to in your FROM clause).
/* These functions will get added after __construct(), see complete file at end of page. */ /** * Select the database to use. * @param $db The name of the database * @param $line The line number (optional) */ function select_db($db, $line=NULL){ @mysql_select_db($db, $this->connection) or die('Could not select database'.($line !== NULL ? ' on line '.$line : '').'<br>'.mysql_error()); } /** * Execute an sql query. * @param $query The query to execute * @param $line The line number (optional) * @return resource A pointer to the query that was executed */ function sql_query($query, $line){ $this->result = @mysql_query($query, $this->connection) or die('Unable to query database'.($line !== NULL ? ' on line '.$line : '').'.<br>'.mysql_error()); return $this->result; }
The two important things to note about the above two functions are:
- The use of error control operator (@) in
@mysql_select_db(...) - The use of a low-precedence "
or" and how it works. Whenmysql_query()is called, it will either return a resource (which evaluates to a boolean true), or false. If it returns false,$this->resultwill be set to false, anddie()will be called; otherwise the call todie()will be ignored due to the short-circuiting "or" (ref: Logical Operators)
Aside from those, you see the class taking advantage of $this->connection to make sure the queries are executed on the
right connection; which is yet another handy feature of OOP that takes some of the burden off of your shoulders when working with
multiple database connections.
To use the functions we just added is simple, just make calls to them the same way in which you refer to member variables:
<?php /* Don't forget to change USERNAME, PASSWORD, and local_db to the appropriate values. You may also need to change localhost; if you don't know, don't worry until something breaks. */ $db = new MySQL_object('localhost', 'USERNAME', 'PASSWORD', __LINE__); $db->select_db('local_db', __LINE__); $result = $db->query('SELECT * FROM `test_table`', __LINE__); ?>
Finally, now that we can execute queries, we need to be able to retrieve the results.
/** * Get the result of the last (or a given) sql query. * @param $result A pointer to a valid sql result set (optional) * @param $type The result type to get (optional): MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH * @return array Result record dependent on $type */ function fetch_array($result=NULL, $type=MYSQL_ASSOC){ if(!is_resource($result)){ $result = $this->result; } switch($type){ case MYSQL_ASSOC: case MYSQL_NUM: case MYSQL_BOTH: break; default: die('Invalid MySQL result type.'); } if($r = @mysql_fetch_array($result, $type)){ return array_change_key_case($r, CASE_UPPER); } return false; }
Now here, you'll notice some trickery: the $result passed in to fetch_array() is optional. This is handy
when performing multiple queries on the same MySQL_object (often times this will happen in a nested situation). In practice,
you can store the result of one query in a variable outside of the class (i.e. as done previously) and then execute another query without
losing your original reference.
The other bit of cheating is with array_change_key_case(), which is being used to normalize the returned columns to all uppercase. This means that you'll no longer have to worry about what case you defined the columns in when you originally created whatever table you're referencing.
The complete class with example usage:
<?php class MySQL_object { public $connection; public $result; /** * Construct the object with the basic amount of info needed to connect to a database. * @param $host The db host * @param $user The db username * @param $password Said user's password * @param $line The line number (optional) */ function __construct($host, $user, $password, $line=NULL){ if(!($this->connection = @mysql_connect($host, $user, $password))){ die('Unable to connect to host'.($line !== NULL ? ' on line '.$line : '').'<br>'.mysql_error()); } } /** * Select the database to use. * @param $db The name of the database * @param $line The line number (optional) */ function select_db($db, $line=NULL){ mysql_select_db($db, $this->connection) or die('Could not select database'.($line !== NULL ? ' on line '.$line : '').'<br>'.mysql_error()); } /** * Execute an sql query. * @param $query The query to execute * @param $line The line number (optional) * @return resource A pointer to the query that was executed */ function sql_query($query, $line){ $this->result = @mysql_query($query, $this->connection) or die('Unable to query database'.($line !== NULL ? ' on line '.$line : '').'.<br>'.mysql_error()); return $this->result; } /** * Get the result of the last (or a given) sql query. * @param $result A pointer to a valid sql result set (optional) * @param $type The result type to get (optional): MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH * @return array Result record dependent on $type */ function fetch_array($result=NULL, $type=MYSQL_ASSOC){ if(!is_resource($result)){ $result = $this->result; } switch($type){ case MYSQL_ASSOC: case MYSQL_NUM: case MYSQL_BOTH: break; default: die('Invalid MySQL result type.'); } if($r = @mysql_fetch_array($result, $type)){ return array_change_key_case($r, CASE_UPPER); } return false; } } $db = new MySQL_object('localhost', 'USERNAME', 'PASSWORD', __LINE__); $db->select_db('local_db', __LINE__); $result = $db->query('SELECT * FROM `test_table`'); /* Loop through the results of the first query */ while($r = $_db->fetch_array($result)){ $_db->query('SELECT * FROM `another_table` WHERE `col` = \''.$r['SOME_COLUMN'].'\''); /* Check if something was returned from the previous query, note no arguments being passed to fetch_array() */ if($row = $_db->fetch_array()){ echo $row['SOME_OTHER_COLUMN']; } } ?>
