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'; 

2 comments:

  1. Hi ,

    How to do some custom query like "SELECT * FROM TABLE WHERE COL1 = 'something' OR COL2 = 'something2' ;" . The $db->where() is doing a AND functionality I guess .

    Derek

    ReplyDelete
    Replies
    1. Hi Derek ,

      There are some other functions also in this Class which I didnt specify here like $db->query() , $db->rawQuery()etc. For your case, You can using either of these. With query() , you can directly give your mySql query like $db->query("SELECT * FROM TABLE WHERE COL1 = 'something' OR COL2 = 'something2' ;"); . With rawQuery(), you have to bind the variables later. db->rawQuery("SELECT * FROM TABLE WHERE COL1 = ? OR COL2 = ? ;",$params); where $params is an array('something','something2') ;

      Delete