After the deprecation of PHP MySQL
extension in 2011, people are slowly upgrading to better
methods like PDO or MySqli to interact with database. Both PDO and MySqli
are improved versions and offer an object-oriented API and number of
enhancement over the regular MySql extension. But this article isn’t about
choosing the best one, we are here to discuss the basic usage of MySqli. So
without further ado, I’d like to show you how we can connect, select, insert,
update and delete records using PHP MySqli. I am sure this list will come in
handy in times of need.
1. Installing MySqli
2. Connect to Database
3. SELECT Multiple Records as Associative array
4. SELECT Multiple Records as Array
5. SELECT Multiple Records as Objects
6. SELECT Single value
7. SELECT COUNT Total records of a table
8. SELECT Using Prepared Statements
9. INSERT Record
10.
Insert Multiple Records
11.
Update & Delete Records
12.
Update using Prepared
Statement
13.
Delete Old Records
Installing MySqli
If you are running PHP version 5.3.0 +, MySqli should be
available to use it right away, but in old PHP 5.0, 5.1, 5.2, extension is not
enabled by default on Windows Systems, you must enable php_mysqli.dll DLL inside of php.ini. To enable the extension you need to
edit yourphp.ini and remove comment (semi-colon) from the
start of the line extension=php_mysqli.dll. In linux too when you install php5
mysql package, MySQLi automatically gets installed, more details about
installation in Linux and Windows can be found here.
Connect to Database
MySqli offers two ways to connect to the database,
procedural and object oriented, the official recommended way to open a database
connection is object oriented way. The procedural style is much similar to old
MySql and it may be helpful to users who are just switching to MySqli, but it
is not a recommended way.
<?php
//procedural style
$mysqli =
mysqli_connect('host','username','password','database_name');
//object oriented style (recommended)
$mysqli = new mysqli('host','username','password','database_name');
?>
Here’s how you open a database connection “object-oriented”
style, which is a recommended way and we will only be using this style in all
the examples below.
<?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);
}
?>
SELECT Multiple Records as Associative array
mysqli_fetch_assoc() : Below is the code to fetch multiple
records as an associative array. The returned array holds the strings fetched
from database, where the column names will be the key used to access the
internal data. As you can see below, data is displayed in an HTML table.
<?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();
?>
SELECT Multiple Records as Array
fetch_array() : Function returns an array of both mysqli_fetch_row and mysqli_fetch assocmerged together, it is an
extended version of the mysqli_fetch_row() function and both numeric and string
can be used as keys to access the data.
<?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();
?>
SELECT Multiple Records as Objects
fetch_object() : To fetch database result set as an
objects, just use MySqli fetch_object(). The attributes of the object represent
the names of the fields found within the result set.
<?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 Single value
How about
getting a single value from database using fetch_object (Cameron
Spear style).
<?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();
?>
SELECT COUNT Total records of a table
Sometimes you
may want to know total records of a table, especially for a pagination.
<?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
Another
important feature of MySqli is the Prepared Statements, it allows us to write
query just once and then it can be executed repeatedly with different
parameters. Prepared Statements significantly improves performance on larger
table and more complex queries. The queries are parsed separately by the
server, making it resilient to malicious code injection.
The code below
uses Prepared statement to fetch records from the database. ? placeholder in the SQL query acts like
marker and will be replaced by a parameter, which could be string, integer,
double or blob. In our case it’s a 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();
?>
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 a Record
Following MySQLi
statement inserts a new row in the table.
<?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);
}
?>
Snippet below inserts same values using Prepared Statement.
As discussed earlier the Prepared statements are very effective against SQL
injection, you should always use prepared statement in situations like this.
<?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
To insert
multiple rows at once, include multiple lists of column values, each enclosed
within parentheses and separated by commas. Sometimes you want to know how many
records have been inserted, updated or deleted, you can use mysqli_affected_rows for that occasion.
<?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 a Records
Updating and
deleting records works similar way, just change to query string to MySql Update
or delete.
<?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;
}
?>
Update using Prepared Statement
Here’s how you
update record using Prepared Statement.
<?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 Records
Delete all
records that is 1 day old, or specify X days records you want to delete.
<?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
MySqli is
clearly a winner over the regular MySql extension in PHP, and the
implementation is also not that different. I just hope this article will help
you migrate/build your projects in future. I have also included some example
files in download section, download it for the reference and happy coding!
Note: This article is taken from http://www.sanwebe.com/2013/03/basic-php-mysqli-usage
