MySql Prepared Statement in PHP
I am currently working on Java Platform and as i have worked on PHP before but now had to perform some task using PHP. I was wondering whether PHP also give a feature of Prepared Statement as you have in Java JDBC Connection. Prepared Statements are always better than normal statements as it prevent SQL Injection etc. This article will discuss on how we can make Prepared Statement Call to MySql Database Call using MySqli Extension of PHP.
MySqli Class in PHP:
MySqli is the newest addition to PHP, it has great feature when compared to traditional mysql Class in PHP. For More Information on MySqli you can check out PHP MySqli Extension
NOTE:
To use MySqli you need to “Configure PHP Installation With –with-mysqli parameter”
To test the prepared statement operation i am going to create table “test” inside master database of mysql.
CREATE TABLE master.test(id int PRIMARY KEY AUTO_INCREMENT, name varchar(100));
Before we go into examples listing the bind parameters required by mysqli.
| Bind Type | Column Type |
| i | Integer Column |
| s | String Column |
| d | Double and Float Column |
| b | Blob and Text Column |
Insert Record Using Prepared Statements in PHP:
< ? $testing = "NewData"; $connection = new mysqli("localhost", "root", "", "master"); $result = $connection->prepare("Insert into test(name) values(?)"); $result->bind_param("s", $testing); $result->execute(); $result->close(); ?>
Reading Records Using Prepared Statement in PHP:
< ? $id = 1; $connection = new mysqli("localhost", "root", "", "master"); $result = $connection->prepare("select * from test where id=?"); $result->bind_param("i", $id); $result->execute(); $result->bind_result($id, $name); while ($row = $result->fetch()) { printf ("%d (%s)\n", $id, $name); } ?>
Deleting Records Using Prepared Statement in PHP:
< ? $id = 1; $connection = new mysqli("localhost", "root", "", "master"); $result = $connection->prepare("delete from test where id=?"); $result->bind_param("i", $id); $result->execute(); $rowsAffected = $result->affected_rows; echo "Rows Affected: ".$rowsAffected; ?>
Updating Records Using Prepared Statement in PHP:
< ? $id = 1; $name = "Hello"; $connection = new mysqli("localhost", "root", "", "master"); $result = $connection->prepare("update test set name = ? where id = ?"); $result->bind_param("s", $name); $result->bind_param("i", $id); $result->execute(); $rowsAffected = $result->affected_rows; echo "Rows Affected: ".$rowsAffected; ?>
Thank you for providing the code which is easier to understand and for its simplicity. I could execute it immediately without any doubts.
You’ve confused result set and statement.
Everywhere in your example that you use $result, that object is a MySQLi_STMT , rather than a MySQLi_Result . Your code will still work, but your variable names are misleading.