Assembling classes: a brief look at the “MySQLConnector” class - PHP

As most developers want to move quickly to the nitty-gritty of the source code, let’s show the whole list for our first class. Here’s how it looks:

class MySQLConnector {

            var $conId; // connection identifier

            var $host; // MySQL host

            var $user; // MySQL username

            var $password; // MySQL password

            var $database; // MySQL database

            var $result; // result set

            // constructor

            function MySQLConnector($host,$user,$password,$database){

                        // validate incoming parameters

                        (!empty($host))?$this->host=$host:die('Host parameter not valid');

                        (!empty($user))?$this->user=$user:die('User parameter not valid');

                        (!empty($password))?$this->password=$password:die('Password parameter not valid');

                        (!empty($database))?$this->database=$database:die('Database parameter not valid');

                        // connect to MySQL and select database

                        $this->connectDB();

            }

            // connect to MYSQL server and select database

            function connectDB(){

                        $this->conId=@mysql_connect($this->host,$this->user,$this->password) or die('Error connecting to the server '.mysql_error());

                        @mysql_select_db($this->database,$this->conId) or die('Error selecting database');

            }

            // perform query

            function performQuery($query){

                        $this->result=@mysql_query($query,$this->conId) or die('Error performing query '.$query);

            }

            // fetch row

            function fetchRow(){

                        return mysql_fetch_array($this->result,MYSQL_ASSOC);

            }

            // get number of rows

            function getNumRows(){

                        return mysql_num_rows($this->result);

            }

            // get number of affected rows

            function getAffectedRows(){

                        return mysql_affected_rows($this->conId);

            }

            // get ID from last inserted row

            function getInsertID(){

                        return mysql_insert_id($this->conId);

            }

}

I hope that the above listed code refreshed your memory, because it’s been a while since the last time we reviewed this class. Taking a quick look at its definition, we can see that the class shows a few simple methods for connecting to the MySQL server, performing SQL queries, returning table rows and so forth. Also, as mentioned earlier, it presents some methods for calculating the number of rows returned by a query, as well as the rows affected by an INSERT, UPDATE or DELETE query.

Indeed, the class is easily extendable, either by adding new methods according to specific needs, or deriving a subclass, which will inherit all of the methods present in it, by adding more capacity to those present in the base class. The preferred approach will depend on your personal needs.

Fortunately, the advantage of having a set of functional classes is that they can be used as standalone packages or easily plugged into other applications. In this case, let us say we need to use this class to quickly fetch some records. Here’s a quick and dirty implementation to extract some records from a “users” table:

// include the class

require_once 'mysqlclass.php';

// instantiate a MySQLConnector object passing to it the connection parameters

$db=&new MySQLConnector('host','user','password','database');

// build the query

$sql='SELECT fname,lname FROM users';

// perform query

$db->performQuery($sql);

// display the results

while($row=$db->fetchRow()){

            echo 'First Name :'.$row['fname'].' Last Name :'. $row['lname'].'<br />';

}

// display total number of results

echo 'Total number of results :'.$db->getNumRows();

The above example shows how easy it is to use the class to perform some of the most common operations associated with MySQL. We instantiated a new “MySQLConnector” class, then performed a simple “SELECT" statement to retrieve records from a hypothetical “users” table, displaying the “firstname” and “lastname” fields included in each table row. After displaying the results, we indicated the total number of rows returned by the query. It's simple and straightforward.

Now that we’ve gained a more intimate knowledge of the functionality of this class, let’s take the next step, and list the complete source code for the “Pager” class. Just keep reading.