Bobbis.net

At work the other day, I was asked to help someone learn how to connect to a MySQL database using PHP. When I saw what they had done in an attempt, it was very inelegant looking a lot like the following code snippet:

$link = mysql_connect('example.com', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$dbConnection = mysql_select_db('database_name');
if (!$dbConnection) {
    die('Could not connect to database');
}

$result = mysql_query('SELECT * FROM table');
if (!result) {
    die('Query failed: ' . mysql_error());
}

$row = mysql_fetch_array($result);

echo $row['attribute'];

mysql_close($link);

I about cried. I do have to say though, that for someone learning the language, it is important to learn how to do it this way in order to understand what is going on, but once you have done this for a while you will see some flaws and frustrations when working on a larger project.

The example above requires a lot of icky looking code every time you connect to and use a database. It also requires the developer to define the MySQL server, the user, the password, and the database every time you want to make a connection to a database, which can be in almost every file of a complex application. It also has no standard way to deal with errors and often times you have to guess where errors happened in your code, especially if you have multiple queries in an execution.

By using includes and a nice function supplied by ILoveJackDaniels and a few of my own conventions, making database connections will become much easier to implement and debug. In the attached example the database connection gets simplified to:

include('include.php');

dbConnect();

$result = do_query('SELECT * FROM table', __LINE__);

$row = mysql_fetch_array($result);

echo $row['attribute'];

dbDisconnect();

Now that is much easier to read and understand, and since most of the work is done in the custom functions dbConnect() and do_query() it makes it easier to have a nice error reporting system that tells you where in your execution any error occurred down to the line number, and even display a detailed description of the error on the page and/or in an email to you. This example also places the hostname, username, password, and database information in one location, making it easier to change those values in case the application needs to be moved to a different machine or database.

Check out the attached examples and their comments to learn more.