PHP MySqli Basic usage (select, insert & update)

After the deprecation of PHP MySQL extension in 2011, alternative extension MySqli and PDO are available in PHP. MySqli and PDO are improved version and offer an object-oriented API and number of enhancement over the regular MySql extension. These extensions are much faster, efficient and totally secure against SQL injections.

Today I’d like to show you the basic usage of MySqli, such as connect, select, insert, update and delete records. I hope this list will come in handy for you.

  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. SELECT all records from past X day, month or year
  10. INSERT Record
  11. INSERT Record (Prepared Statement)
  12. Insert Multiple Records
  13. Update & Delete Records
  14. Update using Prepared Statement
  15. Update or Insert record in single query
  16. 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 your php.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 recommended way to open a database connection is object oriented way, because it is secure, faster and efficient. The procedural style is much similar to old MySql and it may be helpful to users who are just switching to MySqli, but should keep away altogether.

1
2
3
4
5
//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.

1
2
3
4
5
6
7
8
9
10
<?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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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 assoc merged 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?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).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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();
?>

Above code will return error if there’s no result, so here’s safer way to retrieve the value.

1
2
3
4
$result = $mysqli->query("SELECT product_name FROM products WHERE id = 1");
if($result->num_rows > 0){
    echo $result->fetch_object()->product_name;
}

SELECT COUNT Total records of a table

Sometimes you may want to know total records of a table, especially for a pagination.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$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();

SELECT all records from past X day, month or year

Here’s another SELECT example that will fetch records from last month. Click here for more info about MySql date function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$search_ID = 1;
$search_product = "PD1001";

$query = "SELECT id, product_code, product_desc, price FROM products WHERE ID=? AND product_code=? AND post_date >= DATE(NOW() - INTERVAL 1 MONTH)";
$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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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);
}

?>

INSERT a Record (Prepared Statement)

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 any given situations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
//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.

1
2
3
4
5
6
7
8
9
10
11
//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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$product_name = '52 inch TV';
$product_code = '9879798';
$find_id = 1;

$statement = $mysqli->prepare("UPDATE products SET product_name=?, product_code=? WHERE ID=?");

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$statement->bind_param('ssi', $product_name, $product_code, $find_id);
$results =  $statement->execute();
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.

1
2
3
4
5
6
7
8
//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;
}

Insert or Update if record already exists

People often ask how to INSERT a new row or UPDATE if record already exists. The answer is simple, using ON DUPLICATE KEY UPDATE Syntax in MySql query. This clause simply looks for duplicate values in UNIQUE or PRIMARY key and performs INSERT or UPDATE statement. It is pretty useful when you want to INSERT a new record or UPDATE if record already exist.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$id = 0;
$product_code = "P1234";
$product_name = "42 inch TV";
$product_desc = "42 inch TV is good enough for movies";
$price = "1000";

//MySql query using ON DUPLICATE KEY UPDATE
$query = "INSERT INTO products (id, product_code, product_name, product_desc, price)
            VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE product_code=?, product_name=?, product_desc=?, price=?"
;

$statement = $mysqli->prepare($query); // Prepare query for execution

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$statement->bind_param('dsssdsssd', $id, $product_code, $product_name, $product_desc, $price, //insert vars
                                     $product_code, $product_name, $product_desc, $price); //update vars

$statement->execute();

Just pass the UNIQUE or PRIMARY value such as ID of a row to perform update, or 0 to insert new row.

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!

Download

38 Comments Add Comment

  • Thanks for this incisive tutorial. Presently, I’m developing a website and I noticed any time I post a comment on any page on the site, the same comment shows up in every other article on the site. Please can you help resolve this problem?

     Reply
  • 4739Jonah_Mariz_Aventurado

    hello, I’m glad visiting in your site, I’m glad to know more coding, help to do so

     Reply
  • very good tutorial

     Reply
  • Just amazing, I love the way how you explain.

     Reply
  • hi i am trying to do is using joins post data from php to database. please help me in that I have database and i am able to post the data with post method from php to db and in sql query i am getting joins using joins query but i want using joins post data from php to db.

     Reply
  • thats very good stuff

     Reply
  • Thanks for the terrific tutorial and sample pages!

     Reply
  • thanks. this very good

     Reply
  • So I love this post cause its very informative. With the insert code how would you change the code to insert data from a html form using Post Method? I am attempting to do this and so far I am having no luck. I know that the form is posting because I did a var_dump and all the variables are there but they are not inserting into the database. I know its connecting cause I am not getting any error.

    Do you have another recommendation on how to get html form data inserted into the database?

    Thanks

     Reply
  • So nice to find a comprehensive post on this topic
    Many thanks :)

     Reply
  • May i know that how to get back all the functionalities of lower mysql with php 5.5 version.

     Reply
  • am a complete newbie in php what does this symbol mean -> like when using it on mysqli->escape_real_string

     Reply
  • Hi great article learn alot from here as a beginner. I have one question why do you use ‘””‘ at the front n back?
    ‘”‘.$mysqli->real_escape_string(‘P1234’).'”‘

     Reply
  • Update record using Prepared Statement.
    Missing $insert_row = …

     Reply
  • Thanks for this informative post

     Reply
  • Good Tutorial! Thanks for sharing

     Reply
  • in the ‘Update using Prepared Statement’ example, why don’t we use “$statement->execute()” ? How come it works directly after binding?

    Also, in the ‘SELECT COUNT Total records of a table’ example, after:
    “$get_total_rows = $results->fetch_row();”
    can we print $get_total_rows value directly? Or do we have to use print $get_total_rows[0], or something like that?

     Reply
  • Great tutorial. But just noticed this, you use $mysqli as the connection name too. This can be confusing when you use ‘mysqli’ in other places in the example – hard to know if this is the connection name that you are using, or the mysqli that is appended before mysqli commands.

     Reply
  • Thanks for sharing its help me lot!

     Reply
  • anafa david mudi

    Wow wow, thanks a million.
    So simple and direct to the point for a beginner.
    Am Glad that i came across this page that teaches.
    Some books, on prepared statement, did not explain the i,s,b thing as you did, hence my initial apathy to mysqli or oop.

    Thanks and would like to express my thank you by clicking your page a million times. LOL

     Reply
  • hai i works with your coding.but count is not work.what can i do?

     Reply
  • Dear sir
    i would like to ask if you have time or not ?
    I’m looking for developer to make my own project
    thank and best regards

     Reply
  • Very well done … nice tutorial for new mysqli users

     Reply
  • What about sum ?

     Reply
  • I see you still haven’t bothered to fix the prepared statement update example.
    $statement->execute();
    That’s probably caused a lot of readers some confusion. Otherwise this is a very helpful tutorial.

     Reply
  • i liked – we learn faster with the lesson

     Reply
  • Update using Prepared Statement example missed the “$statement->execute();” statement

     Reply
  • Very Basic, Exact and Excellent tutorial. No other words…

     Reply
  • hi!, how can I retrieve the result set as json response through Jquery Ajax?

    I’m trying to do that but I get this error:

    SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data

     Reply
  • Nice work. Have browsed a lot of tutorials, but your code re: MySQLi is one of the only ones that actually work. Would like to request a tutorial on checking for and displaying all duplicate rows. Have been using the traditional Innerjoin routine / Count / Having > 1 Mysql approach, but I can’t get it to work grammatically with MySQLi.

     Reply
  • Hi saran. Thanks for sharing this good tutorial. Prepared statements are the way to go! My question would be that if i had very many lines of HTMl code, how should i include them in the while loop? Should i using one print like: print”; or add a print”; to every line?

    Thanks Saran

     Reply
  • Hi first thanks for the code its been a help in my project. I have followed all your advice and got it all to work, i have redesigned what i needed to make it work in the mobile phone solution to our site. It passes sandbox with success after reading the process.php in paypal-express-checkout i see the return from paypal is dealt with but no word or sql file on adding the table to the DB $insert_row = $mysqli->query(“INSERT INTO BuyerTable i take it that we have to do this manually? Or is there a SQL file to set this up for us. Just a thought as i can see so much work went into the php code shame that the ending wasn’t in place as sending the return data into the DB also initiating some emails to the buyer and notice to the seller would have made this 100% perfect. If you do have said code could i have it?

    Thanks again,

     Reply
  • Update record using Prepared Statement.
    Missing $insert_row = …

     Reply
    • I find it rude that you took my comment and put you name on it. I posted this almost a year ago. You shouldn’t alter user comments.

  • hello this is very interesting your code. I am newbie in js I would like to know how to add an item while clicking a button and not a right click with the opportunity to update a marker
    thank you

     Reply