Programing: PHP MySQL Wrapper Class

So, I wanted to mix things up a bit and I am posting my MySQL library I commonly use for PHP programs I write. First off, I want to emphasize the fact this specific class is not used on any website that is hosted on the internet (to my knowledge). However, I always use this PHP class whenever I am writing some code that requires a database (these websites are normally for personal use and are hosted on a private server).

This code can be redistributed and reused by anyone wishing to put their website at risk. This code is provided as is with no warranty. If you wish to use this code, you do so at your own risk and I will not provide support (although I'll probably give it if you ask nicely). This code was developed over a long period of time and segments have been adapted from multiple sources and examples. However, this code is unique and was not copied from another source. This has been provided only as an example.

$server = new mySQL('host', 'user', 'pass', 'database');

class mySQL
{
//Database Connection Variables
var $myConnection;
var $myDatabase;

//Number of Queries
var $numberOfQueries;

//Storage of Query Results
var $queryResults;

function mySQL($server, $username, $password, $db)
{
$this->numberOfQueries = 0;
$this->myDatabase = &$db;
$this->myConnection = mysql_connect($server, $username, $password);
}

//Run the query but do not return the result
//Use the preselected database
function runQuery($sql)
{
$this->numberOfQueries++;
$this->queryResults = mysql_db_query($this->myDatabase, $sql);
}

//Run the query but do not return the result
//Use the specified database
function runDBQuery($db, $sql)
{
$this->numberOfQueries++;
$this->queryResults = mysql_db_query($db, $sql);
}

//Get the number of rows on the last query
//Useful for quick and easy way to obtain the number of rows
function getNumRows()
{
return mysql_num_rows($this->queryResults);
}

//Return the raw result set
//Useful if manual minipulation of the query is disired
function getRestult()
{
return $this->queryResults;
}

//Puts the results into a multidimensional array
//First array numbered form 0 to number of results
//Second array is the column names for that result set
function getFullArray()
{
$arr = array();
if ($this->getNumRows() > 0)
{
$x=0;
// iterate through resultset
while($row = mysql_fetch_row($this->queryResults))
{
foreach($row as $i => $value)
{
$column = mysql_field_name($this->queryResults,$i);
$data["$column"] = $value;
$arr[$x] = $data;
}
$x++;
}
return $arr;
}
}

//Puts the first row of results into an array
function getFirstRow()
{
$arr = array();
if ($this->getNumRows() > 0)
{
$row = mysql_fetch_row($this->queryResults);
foreach($row as $i => $value)
{
$column = mysql_field_name($this->queryResults,$i);
$data["$column"] = $value;
$arr = $data;
}
return $arr;
}
}

//Returns the first record (useful when only one value is returned from a query
//If result contains more than one record will return the first record of the first row
function getSingleResult()
{
if ($this->getNumRows() > 0)
{
$row = mysql_fetch_assoc($this->queryResults);
$column = mysql_field_name($this->queryResults,0);
return $row[$column];
}
}
}

This code has some major shortcomings but some major benefits as well. It is not very optimized for a large scale use and is just awful at catching database errors caused by bad queries.

The main benefits are the way that results are returned from a query. First, a query is sent using the runQuery or runDBQuery methods (note: I typically use runQuery because I only use one database at a time. Also, there are some problems with using runDBquery). Queries are executed in two steps. First the query is sent to the database (by passing a string with the query to the above method) and the results are (temporarily) stored in the class. Then, the results are returned using one of the available functions (listed below).

The query results can be returned directly (just as it would by using built in PHP functions) by using the method getResult. However, I never use this method! Depending on what the query is going to return I use one of three methods which I will now explain.

First, if only a single cell (one row and one column of information) is going to be returned the method getSingleResult. Since PHP does not have variable casting this can return a number or a string or any other information. This is good when I need to get a very specific result such as a specific number or title.

Secondly, if only one row needs to be returned the method getFirstRow will return an array with each sub-element being named for the column name in the database. This saves time because the possibly repetitive code of manipulating the information returned by the query into a usable structure. Using this technique (having the database logic store in a class) saves time and results in less code because the annoying way PHP handles MySQL is wrapped in a nice plastic bubble (but that is just my opinion).

An important thing to note is that if the methods getSingleResult and getFirstRow will work even if the data being returned by the query does not "fit" the result. Also, getFirstRow will work in situations that getSingleResult would work, the only difference is the way the variable (array) is returned.

The last method, getFullArray, returns a multi dimensional array with the first dimension being the row number and the second being the column name. This method will probably be used the most because it basically returns a "table" of the query.

Another important method to mention is getNumRows, which basically, returns the number of rows. However, this number can also be obtained by determining the number of rows in the array returned by getFullArray. Also, this class counts the number of queries that were run and this number is stored in the class variable numberOfQueries, but there is no method to get this variable, it must be directly accessed.

I'll probably post more code examples in the future. Hopefully this is just the first of many examples of code that I will post. I've played with some CSS code to help make the actual code look decent in my blog, but it is going to look awful in the RSS feed.

UPDATE: after quite a bit of messing with CSS I was able to get a code block that I like. It wasn't easy and the pre-wrap CSS command only works under Firefox and Opera (there is no IE support). However, the only real difference is that the leading whitespace is not displayed in IE. The problem was that the pre tag does not text wrap.