PHP MySQLi object oriented implementation

MySQLi - i standing for 'improved'.

PDO
PDO works with various different database systems
MySQLi will only work with MySQL databases.
Both are object oriented, but MySQLi allows procedural usage also.

PHP MySQLi object oriented implementation,

Connecting
Create a new instance of MySQLi on localhost database

$db = new mysqli('localhost', 'mysql_database', 'pass', 'demo');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

Querying

$sql = SELECT * FROM `users` WHERE `live` = 1
$result = $db->query($sql)

if(!$result ){
    die('There was an error running the query [' . $db->error . ']');
}

Output query results
To loop through the results and output the username

while($row = $result->fetch_assoc()){
    echo $row['username'] . '<br />';
}


Number of returned rows


<?php
echo 'Total results: ' . $result->num_rows;
?>

Number of affected rows

<?php
echo 'Total rows updated: ' . $db->affected_rows;
?>

Free result set
It's advisable to free a result after use

$result->free();

This will free up some system resources, and is a good practice to get in the habit of doing.

Escaping characters

$db->real_escape_string('This is an unescaped "string"');

$db->escape_string('This is an unescape "string"');
The string should now be safer to insert into your database through a query.

 close the connection:

$db->close();

Define a statement

$statment = $db->prepare("SELECT `name` FROM `users` WHERE `username` = ?");

Bind parameters
$name = 'Bob';
$statement->bind_param('s', $name);
Execute the statement
$statement->execute();

Iterating over results

$statement->bind_result($returned_name);

while($statement->fetch()){
    echo $returned_name . '<br />';
}

Close statement

$statement->free_result();

MySQLi Transactions

Disable auto commit
You need to make it so that any query you submit doesn't automatically commit in the database.

$db->autocommit(FALSE);

Commit the queries
After a few queries that you've ran using $db->query()
 we can call a simple function to commit the transaction:

$db->commit();

Rollback
 roll something back:

$db->rollback();