Mysql - MySql Prepared Statement in PHP

Home > Mysql, PHP > MySql Prepared Statement in PHP

MySql Prepared Statement in PHP

January 22nd, 2009 Hitesh Agrawal Leave a comment Go to comments

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

Regular Expression in MySQL

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;
?>

Custom Search

Techie Zone

MySql Prepared Statement in PHP

Popular Articles:

Java Tutorial

Translator