Wednesday, February 14, 2018

PHP MySQLi object oriented implementation

MySQLi - i standing for 'improved'.

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,

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 . ']');


$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

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

Number of affected rows

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

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


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:


Define a statement

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

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

Iterating over results


    echo $returned_name . '<br />';

Close statement


MySQLi Transactions

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


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


 roll something back:


No comments:

Post a Comment