Due to the end of support for PHP MySQL in 2011, PDO or MySqli are becoming more and more widely used for working with databases. They have better functionality (than MySQL) and offer an OOP (Object Oriented Interface) API. Which one is better is a topic for another article, in this article we will try to understand the basics of working with MySqli. Therefore, without further preamble, let's move on to consider connection (connect), select (select), insert (insert), update ( update) and deleting (delete) records (data/documents/information) via PHP MySqli. I hope that this article will be useful in solving problems that may arise when working with PHP MySqli.

Installing MySqli

When using PHP version 5.3.0+, MySqli is available by default; for older versions, to make it available, include php_mysqli.dll DLL inside the php.ini file and edit php.ini by uncommenting the line  extension=php_mysqli.dll. On Linux, MySQLIi will be installed automatically when you install the PHP5 mysql package. More information about installing on windows and linux systems can be found here .

Database connection

MySqli offers two ways to connect to a database: procedural and object-oriented. It is recommended to use object-oriented. Procedural is similar to (old) MySql, so for beginners it may be preferable to use it, it is worth remembering that it is not recommended to use it.

PHP

 //procedural style $mysqli = mysqli_connect('host','username','password','database_name');  //object-oriented style (recommended) $mysqli = new mysqli('host','username','password','database_name');

The following shows opening a database connection in an object-oriented way. This method will be used in all the examples below.

PHP

 <?php // Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name');  //Display any connection error if ($mysqli->connect_error) {     die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); }  ?> 

Selecting (SELECT) the resulting row as an associative array

mysqli_fetch_assoc() : The code below fetches the resulting row as an associative array. The returned array contains the rows retrieved from the database, where the column names will be the key used to access the internal data. As shown below, the data is displayed as an HTML table.

PHP

 <?php //Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name');  //Output any connection error if ($mysqli->connect_error) {     die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); }  //MySqli Select Query $results = $mysqli->query("SELECT id, product_code, product_desc, price FROM products");  print '<table border="1">'; while($row = $results->fetch_assoc()) {     print '<tr>';     print '<td>'.$row["id"].'</td>';     print '<td>'.$row["product_code"].'</td>';     print '<td>'.$row["product_name"].'</td>';     print '<td>'.$row["product_desc"].'</td>';     print '<td>'.$row["price"].'</td>';     print '</tr>'; } print '</table>';  // Frees the memory associated with a result $results->free();  // close connection $mysqli->close(); ?>

Selecting (SELECT) the resulting row as an array (associative, normal, or both)

Function fetch_array() : returns an array with the combined functionality mysqli_fetch_row and mysqli_fetch assoc. This function is an extended version of mysqli_fetch_row(); both string and numbers can be used to access data.

PHP

 <?php //Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name');  //Output any connection error if ($mysqli->connect_error) {     die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); }  //MySqli Select Query $results = $mysqli->query("SELECT id, product_code, product_desc, price FROM products");  print '<table border="1"'; while($row = $results->fetch_array()) {     print '<tr>';     print '<td>'.$row["id"].'</td>';     print '<td>'.$row["product_code"].'</td>';     print '<td>'.$row["product_name"].'</td>';     print '<td>'.$row["product_desc"].'</td>';     print '<td>'.$row["price"].'</td>';     print '</tr>';  } print '</table>';  // Frees the memory associated with a result $results->free(); // close connection $mysqli->close(); ?>

Selecting (SELECT) the resulting row as an object

fetch_object() : to get the result set as an object, you need to use MySqli fetch_object(). The object's attributes will display the names of the fields found inside the result set.

PHP

 <?php //Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name');  //Output any connection error if ($mysqli->connect_error) {     die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); }  //MySqli Select Query $results = $mysqli->query("SELECT id, product_code, product_desc, price FROM products");  print '<table border="1">'; while($row = $results->fetch_object()) {     print '<tr>';     print '<td>'.$row->id.'</td>';     print '<td>'.$row->product_code.'</td>';     print '<td>'.$row->product_name.'</td>';     print '<td>'.$row->product_desc.'</td>';     print '<td>'.$row->price.'</td>';     print '</tr>'; }  print '</table>';  // close connection $mysqli->close(); ?>

SELECT a single value

You can get a single value from the database using fetch_object (Cameron Spear method).

PHP

 <?php //Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name');  //Output any connection error if ($mysqli->connect_error) {     die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); }  //chained PHP functions $product_name = $mysqli->query("SELECT product_name FROM products WHERE id = 1")->fetch_object()->product_name; print $product_name; //output value  $mysqli->close(); ?>

Retrieve (SELECT COUNT) the number of rows in the table

Sometimes you need to know the number of rows in a table, especially when paginating.

PHP

 <?php //Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name');  //Output any connection error if ($mysqli->connect_error) {     die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); }  //get total number of records $results = $mysqli->query("SELECT COUNT(*) FROM users"); $get_total_rows = $results->fetch_row(); //hold total records in variable  $mysqli->close(); ?>

SELECT using prepared statements

prepared statements — a special DBMS tool that allows you to speed up the sequential execution of repetitive queries built according to the same template.

One of the features of MySqli is the ability to use already written templates: that is, it is enough to write a query once, after which it can be repeatedly executed with different parameters. Using already written templates improves performance for large tables and complex queries. To prevent malicious code from entering, each request is analyzed separately by the server.

The code below uses a template (Prepared statement) to get data from the database. The  ? placeholder in an SQL query acts as a marker and will be replaced by a parameter, which in turn can be a string, integer, double, or blob. In our case, this is the string $search_product.

PHP

 $search_product = "PD1001"; //product id  //create a prepared statement $query = "SELECT id, product_code, product_desc, price FROM products WHERE product_code=?"; $statement = $mysqli->prepare($query);  //bind parameters for markers, where (s = string, i = integer, d = double, b = blob) $statement->bind_param('s', $search_product);  //execute query $statement->execute();  //bind result variables $statement->bind_result($id, $product_code, $product_desc, $price);  print '<table border="1">';  // fetch records while($statement->fetch()) {     print '<tr>';     print '<td>'.$id.'</td>';     print '<td>'.$product_code.'</td>';     print '<td>'.$product_desc.'</td>';     print '<td>'.$price.'</td>';     print '</tr>';  } print '</table>';  //close connection $statement->close();

The same query with multiple parameters:

PHP

 $search_ID = 1; $search_product = "PD1001";  $query = "SELECT id, product_code, product_desc, price FROM products WHERE ID=? AND product_code=?"; $statement = $mysqli->prepare($query); $statement->bind_param('is', $search_ID, $search_product); $statement->execute(); $statement->bind_result($id, $product_code, $product_desc, $price);  print '<table border="1">'; while($statement->fetch()) {     print '<tr>';     print '<td>'.$id.'</td>';     print '<td>'.$product_code.'</td>';     print '<td>'.$product_desc.'</td>';     print '<td>'.$price.'</td>';     print '</tr>';  } print '</table>';  //close connection $statement->close();

Insert record

The entry below inserts a new row into the table.

PHP

 <?php //values ​​to be inserted in database table $product_code = '"'.$mysqli->real_escape_string('P1234').'"'; $product_name = '"'.$mysqli->real_escape_string('42 inch TV').'"'; $product_price = '"'.$mysqli->real_escape_string('600').'"';  //MySqli Insert Query $insert_row = $mysqli->query("INSERT INTO products (product_code, product_name, price) VALUES($product_code, $product_name, $product_price)");  if($insert_row){     print 'Success! ID of last inserted record is : ' .$mysqli->insert_id .'<br />'; }else{     die('Error : ('. $mysqli->errno .') '. $mysqli->error); }  ?>

The passage below inserts the same values ​​through a Prepared Statement. As we have said, templates are extremely effective against SQL injection. For the given example, their use is the best option.

PHP

 //values ​​to be inserted in database table $product_code = 'P1234'; $product_name = '42 inch TV'; $product_price = '600';  $query = "INSERT INTO products (product_code, product_name, price) VALUES(?, ?, ?)"; $statement = $mysqli->prepare($query);  //bind parameters for markers, where (s = string, i = integer, d = double, b = blob) $statement->bind_param('sss', $product_code, $product_name, $product_price);  if($statement->execute()){     print 'Success! ID of last inserted record is : ' .$statement->insert_id .'<br />'; }else{     die('Error : ('. $mysqli->errno .') '. $mysqli->error); } $statement->close();

Insert multiple records

Inserting multiple rows at the same time is done by including a row of column values, where each row of values ​​must be surrounded by brackets and separated from the others by a comma. Sometimes you need to know how many records were inserted, updated or deleted, you can use mysqli_affected_rows.

PHP

 //product 1 $product_code1 = '"'.$mysqli->real_escape_string('P1').'"'; $product_name1 = '"'.$mysqli->real_escape_string('Google Nexus').'"'; $product_price1 = '"'.$mysqli->real_escape_string('149').'"';  //product 2 $product_code2 = '"'.$mysqli->real_escape_string('P2').'"'; $product_name2 = '"'.$mysqli->real_escape_string('Apple iPad 2').'"'; $product_price2 = '"'.$mysqli->real_escape_string('217').'"';  //product 3 $product_code3 = '"'.$mysqli->real_escape_string('P3').'"'; $product_name3 = '"'.$mysqli->real_escape_string('Samsung Galaxy Note').'"'; $product_price3 = '"'.$mysqli->real_escape_string('259').'"';  //Insert multiple rows $insert = $mysqli->query("INSERT INTO products(product_code, product_name, price) VALUES ($product_code1, $product_name1, $product_price1), ($product_code2, $product_name2, $product_price2), ($product_code3, $product_name3, $product_price3)");  if($insert){     //return total inserted records using mysqli_affected_rows     print 'Success! Total ' .$mysqli->affected_rows .' rows added.<br />'; }else{     die('Error : ('. $mysqli->errno .') '. $mysqli->error); }

Update/Delete Records

The principle of updating and deleting records is the same. It is enough to replace the query string with MySql update or delete (didn't understand, see for yourself).

PHP

 //MySqli Update Query $results = $mysqli->query("UPDATE products SET product_name='52 inch TV', product_code='323343' WHERE ID=24");  //MySqli Delete Query //$results = $mysqli->query("DELETE FROM products WHERE ID=24");  if($results){     print 'Success! record updated / deleted'; }else{     print 'Error : ('.$mysqli->errno .') '. $mysqli->error; }

Updating with prepared statements

An example of updating a record using prepared statements is shown below.

PHP

 $product_name = '52 inch TV'; $product_code = '9879798'; $find_id = 24;  $query = "UPDATE products SET product_name=?, product_code=? WHERE ID=?"; $statement = $mysqli->prepare($query);  //bind parameters for markers, where (s = string, i = integer, d = double, b = blob) $results = $statement->bind_param('ssi', $product_name, $product_code, $find_id);  if($results){     print 'Success! record updated'; }else{     print 'Error : ('.$mysqli->errno .') '. $mysqli->error; }

Delete old posts

All records that have been on the server for more than 1 day are subject to deletion; you can set the number of days yourself.

PHP

 //MySqli Delete Query $results = $mysqli-<query("DELETE FROM products WHERE added_timestamp > (NOW() - INTERVAL 1 DAY)");  if($results){      print 'Success! deleted one day old records'; }else{      print 'Error : ('. $mysqli-<errno .') '. $mysqli-<error; }

Conclusion

Without a doubt, MySqli is significantly better than the standard PHP MySql extension, although the principles of their work are quite similar. I hope the above information will be useful when creating and migrating projects in the future. For convenience, the ability to download example files has been implemented below. This can be done by clicking on the download button.