Extending mysqli class with example
PHP’s mysqli extension is a great starting point for your OOP type access to MySQL server. Here’s a simple example and some possible features for you to explore using base mysqli class provided by PHP and extending it to suite your needs.
There are two basic errors that can occour while working with SQL – Connect error and Query error. For that we’ll implement two different exceptions we can catch in our code.
Once exceptions are defined, we’ll write our own DB class that we’ll use to connect to MySQL Server with some neat features like query logging and error/exception handling.
/**
* DB Connect Exception class
*
* This exception will be thrown, when we are unable to connect to MySQL Server.
*/
class DBConnectException extends Exception {
public function __construct($error, $errno = 0){
parent::__construct($error, $errno);
}
}
/**
* DB Query Exception class
*
* This exception will be thrown, when MySQL server is unable to process our SQL.
*/
class DBQueryException extends Exception {
public function __construct($error, $errno = 0){
parent::__construct($error, $errno);
}
}
/**
* Our base DB class, that extends mysqli.
*
*/
class DB extends mysqli {
/**
* We'll overwrite parent __construct, so we can attach our DBConnectException
* exception upon any failure.
*
* We'll try to provide all the arguments parent class provides.
* @param string $host MySQL hostname
* @param string $user MySQL username
* @param string $pass MySQL password (use null for no password)
* @param string $db MySQL database to select (use null for none)
* @param string $port MySQL port to connect to (use null for default)
* @param string $socket MySQL socket to be used (use null for default)
* @throws DBConnectException
*/
public function __construct($host = 'localhost', $user = null, $pass = null,
$db = null, $port = null, $socket = null){
// execute parent constructor that will try to connect,
// use @ operator, to supress any error output
@parent::__construct($host, $user, $pass, $db, $port, $socket);
// check if connect errno is set
if ($this->connect_errno != 0){
// error has occoured, throw our DBConnectException with
// error message and error code
throw new DBConnectException($this->connect_error, $this->connect_errno);
}
}
/**
* Query method
*
* @param string $sql SQL to execute
* @return mysqli_result Object
* @throws DBQueryException
*/
public function query($sql){
// here, we will log the query to sql.log file
// note that, no error check is being made for this file
file_put_contents('/tmp/sql.log', $sql . "\n", FILE_APPEND);
// on with query execution, call the parent query method
// call it with @ operator, to supress error messages
$result = @parent::query($sql);
// check if errno is set
if ($this->errno != 0){
// throw our DBQueryException with error message and error code
throw new DBQueryException($this->error, $this->errno);
}
// if everything is OK, return the mysqli_result object
// that is returned from parent query method
return $result;
}
}
Here’s a simple usage example:
// enclose this into try catch block, so we can handle errors
try {
// initialize our DB class
$db = new DB('localhost', 'root');
// since we did not provide the DB to connect to, select it now
$db->select_db('test');
// now, perform example query
$r = $db->query("select * from test_table");
while ($rr = $r->fetch_assoc()){
echo "Row Id: " . $rr['id'] . "\n";
}
} catch (DBConnectException $e){
// execute this upon connect error
echo "ERROR WHILE CONNECTING: " . $e->getMessage() . " (" . $e->getCode() . ")\n";
} catch (DBQueryException $e){
// execute this upon query error
echo "ERROR IN QUERY: " . $e->getMessage() . " (" . $e->getCode() . ")\n";
}
And that’s it. There are unlimited ways of how to use this class to make your coding life easier. Enjoy!