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. INSERT Record
  10. INSERT Record (Prepared Statement)
  11. Insert Multiple Records
  12. Update & Delete Records
  13. Update using Prepared Statement
  14. Update or Insert record in single query
  15. 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();

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

  1. very good tutorial

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

     Reply
  3. 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
  4. thats very good stuff

     Reply
  5. Thanks for the terrific tutorial and sample pages!

     Reply
  6. 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
  7. So nice to find a comprehensive post on this topic
    Many thanks :)

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

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

     Reply
  10. 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
  11. Update record using Prepared Statement.
    Missing $insert_row = …

     Reply
  12. Thanks for this informative post

     Reply
  13. Good Tutorial! Thanks for sharing

     Reply
  14. 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
  15. 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
  16. Thanks for sharing its help me lot!

     Reply
  17. 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
  18. hai i works with your coding.but count is not work.what can i do?

     Reply
  19. 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
  20. Very well done … nice tutorial for new mysqli users

     Reply
  21. What about sum ?

     Reply
  22. 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
  23. i liked – we learn faster with the lesson

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

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

     Reply
  26. 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
  27. 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
  28. 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
  29. 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
  30. 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.

  31. 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
Message Type : Question Comment ?
  • Question : Can include code, jsFiddle, codePen etc using Markdown Syntax.
  • Comment : Short comments and questions.