Object Interaction in PHP: Introduction to Aggregation, part 2 - Improving the “MySQLConnector class: adding row-counting methods (
Page 4 of 5 )
As I said before, very often we need to count the number of rows returned by a SELECT query, in order to display paged result sets or implement another similar application. Also, it’s useful to know how many rows were affected by INSERT, UPDATE or DELETE statements. If we want to add these capabilities to our class, should we write the proper methods. Okay, the revamped version of the class looks as follows:
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);
}
}
At this point, we’re incorporate the “getNumRows()”, “getAffectedRows()” and “getInsertID” methods, respectively, to calculate the number of records returned after performing a SELECT statement, as well as to determine the number of records affected after an INSERT UPDATE or DELETE statement has being executed. Also, the class is able to calculate the ID (in case of havingAUTO_INCREMENT table fields) of the last row inserted in a table.
Of course, there may be more methods valid to be added to the class, but for the moment it’s more than enough. So, having completed the class definition, it’s time to look at a possible implementation. Just join me in the next example.