Sunday, January 13, 2013

PHP-MySQLi Class

What is MySQLi

MySQLi (MySQL improved) is a driver used in PHP to access MySQL databases. PHP 5.5.0 deprecate the use of old mysql driver and it gives warning that old driver will be removed in the future versions. Also it recommends the use of MySQLi or PDO_MySQL extension.

PHP-MySQLi Class

https://github.com/ajillion/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php provides a good PHP class for MySQLi, MySqliDb Class . Go get the code. Now import the Class into your project:
require_once('Mysqlidb.php');
and Create a new instance of the class
$db = new Mysqlidb('host', 'username', 'password', 'databaseName');

Insert Query

To do a insert query, MySQL Query is "INSERT INTO posts (title,body) VALUE ('Inserted title','Inserted body'); " . PHP code corresponding to this is as follows:
$insertData = array(
    'title' => 'Inserted title',
    'body' => 'Inserted body'
);

if ( $db->insert('posts', $insertData) ) echo 'success!';

Select Query

To select the entire rows in a table, MySQL query is "SELECT * FROM tableName ;" . PHP code for this is below:
$results = $db->get('tableName');
print_r($results); // contains array of returned rows
If you want to add any condition using `where`, as in MySQL query "SELECT * FROM TABLENAME WHERE COLUMNNAME1='VALUE1' AND COLUMNNAME2='VALUE2';" do as follows:
$db->where('COLUMNNAME1','VALUE1');
$db->where('COLUMNNAME2','VALUE2');
$results = $db->get('TABLENAME');

Update Query

MySQL Query: "UPDATE tableName SET fieldOne='fieldValue',fieldTwo='fieldValue' WHERE id=1 ;". Using MySqliDb Class , you can do as follows:
$updateData = array(
    'fieldOne' => 'fieldValue',
    'fieldTwo' => 'fieldValue'
);
$db->where('id', 1);
$results = $db->update('tableName', $updateData);

Delete Query

$db->where('id', int);
if ( $db->delete('posts') ) echo 'successfully deleted';