Ultra simplified MySQL and PHP interaction
Here’s the scenario: you need a clean, simple and fast way to interact with your MySQL database using PHP. You’ve looked through the docs on PHP.net and trolled every forum you can find, but everything looks over-complicated and really confusing. There’s a much easier way that won’t give you a headache.
Here’s the typical MySQL connection and query example from PHP.net:
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table border="0">";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tbody><tr>";
foreach ($line as $col_value) {
echo "<td>$col_value</td>";
}
echo "</tr>";
}
echo "</tbody></table>";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
It works, but it’s bulky and redundant. You could copy/paste this beast into your code for every single query, but... why? You want some sort of Database Abstraction Layer. An abstraction layer is an include or class that simplifies this interaction and combines multiple functions so that you can do everything you need to do without having to manually build a foundation every time. Database abstraction layers are nothing new, and there are hundreds of PHP class files out there to, supposedly, simplify this process. Unfortunately, the vast majority of them just make things more complex and merely convert versions of the code above into an include file of some sort without actually simplifying your interaction with these functions. Nearly every other database class file forces you to manually differentiate between every query type and call each of them differently. If I want just a single record, I have to use getone(). If I want multiple records through a typical select statement, I have to use fetch() for results as a list and fetcho() in order to get an object result. If I want to perform a SQL insert or update, I have to use query().
That’s not helpful at all, it’s just stupid.
The Solution
A couple of years ago I was totally fed up with this and just wanted to call a simple query, have it auto connect to the database, get my result and give it back to me in a nice format I can use. I wanted to do this with no more than three lines of code, including my actual SQL statement and be able to call the same single function every time no matter what type of result I was asking for. This is what I want, give it to me. Bang, here’s your data. That’s it. Since everything else out there seemed like a giant rat’s nest of code, I wrote my own. Ever since, my SQL interactions have looked like this:
include_once "lib_database.php";
include_once "blk_dbconnect.php";
$myResult = query("SELECT something FROM someTable WHERE foo = 'bar'");
- I include my abstraction layer, which I call a “library file” (hence the “lib_” prefix to keep things organized).
- I include a small block of code to connect to the database (blk_dbconnect.php). This just contains the database host, db name, login and password and the calls a dbconnect() function from lib_database.php. I’ll cover that function in more detail below.
- I hand lib_database.php my SQL query using just query(). That’s it. My results come back as a nicely formatted array inside of the $myResult variable (or if there is only one record, as a simple string) and there’s no more fuss. I’ve used this same method, with minor updates over time, in well over 50 sites over the past couple of years without a hitch. It’s clean, simple, modular and saves time.
The Code
Let’s take a closer look under the hood and see what’s actually happening. The blk_dbconnect.php file is simple, just one line to tell lib_database.php to connect with your credentials:
dbconnect('my_db_name', 'my_db_login', 'my_db_password', 'my_db_host');
Why make this an include? So that you can re-use it over and over without having to manually code your db credentials every time, and it keeps these credentials in one place. If you ever change hosts, login or password, you’ll only need to update this one file instead of every single instance where you talk to your database.
Now let’s see what’s inside lib_database.php. First, here’s our dbconnect() function, used above.
function dbconnect($dbname, $dblogin, $dbpassword, $dbhost='localhost') {
$dblink = mysql_connect($dbhost, $dblogin, $dbpassword) or die ('Could not connect: ' . mysql_error(
//echo 'Connected successfully';
mysql_select_db($dbname) or die('Could not select database');
return $dblink;
}
Notice the parameters you’re passing into dbconnect() and that the $dbhost param is optional, defaulting to ‘localhost’.
Now we come to the real meat of this abstraction layer. This single query function handles all of your SQL statements and returns pretty results to you. It’s smart enough to know the difference between a query, fetch or single result, and even frees up MySQL memory when you’re done using mysql_free_result().
function query($query,$assoc=1) {
$r = @mysql_query($query);
if( mysql_errno() ) {
$error = 'MYSQL ERROR '.mysql_errno().' : ' . mysql_error(). '$query';
echo($error); return FALSE;
}
if ( strtolower(substr($query,0,6)) != 'select' ) {
return array(mysql_affected_rows(),mysql_insert_id());
}
)
$count = @mysql_num_rows($r);
if ( !$count ) {
return 0;
}
if ($count == 1) {
if ($assoc) {
$f = mysql_fetch_assoc($r);
} else {
$f = mysql_fetch_row($r);
}
mysql_free_result($r);
if (count($f) == 1 ) {
list($key) = array_keys($f);
return $f[$key];
} else {
$all = array();
$all[] = $f;
return $all;
}
} else {
$all = array();
for( $i = 0; $i < $count; $i++ ) {
if( $assoc ) $f = mysql_fetch_assoc($r);
} else {
$f = mysql_fetch_row($r);
}
$all[] = $f; }
mysql_free_result($r);
return $all;
}
}
Finally, we have a secondary function if you need to explicitly close your db connection. Honestly, I have very little use for this and it’ll be the same for most of you. But, it’s included in the library for those rare occasions when you need it.
function dbclose($dblink='$dblink') {
mysql_close($dblink);
}
Simplify Even Further
To make things even more simplified I include both lib_database.php and blk_dbconnect.php in my site’s template header so that when I need to talk to MySQL, my library file is ready to go and I’m already connected to the database. I just need to call my query with one simple line: $myResult = query(“SELECT something FROM someTable WHERE foo = ‘bar’”). No more bloated bullshit, no more headaches trying to muddle through SQL connection strings and having to constantly tell MySQL the type of result I’m asking for... I just tell it what I want, and I get it.
Work Smarter, Not Harder
There are two things to remember above all else when you’re doing any sort of programming: KISS and DRI – Keep It Simple Stupid, and Don’t Repeat Yourself. That means don’t over complicate things, cut the bloat and cruft and do it simply or don’t do it at all. If you’ve written a block of code or some custom function, make it modular so that you can use it again, and again, and again. You’ve already done the work once, there’s no sense in doing it all over again. Follow these two principles and you’ll write faster, slimmer, more extensible code every time. You’ll be amazed at the time and frustration you’ll save yourself after you’ve built up a catalog of library files for common tasks.
