PHP mysqli prepared statement insert example

Posted on May 6, 2014 by agurchand

MySQLi is the improved version of MySQL extension and it has more feature which is included in PHP Version 5.

The advantage using MySQLi is:

1. Prevent SQL Injection
2. Able to Prepare statement and bind params.
3. OOPS Interface and more.

In this tutorial you can learn how to insert values into the table using mysqli prepared statement.

First create a table, use the below SQL query:

--
-- Table structure for table `product`
--

CREATE TABLE IF NOT EXISTS `product` (
  `prod_id` int(5) NOT NULL AUTO_INCREMENT,
  `prod_name` varchar(100) NOT NULL,
  `prod_code` int(6) NOT NULL,
  `prod_price` double NOT NULL,
  PRIMARY KEY (`prod_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `product`
--

Totally four fields in the above table with three different data types,

1. int
2. varchar
3. double

2. Here is the PHP script:

<?php 

//connect to mysql server and select db
$mysqli = new mysqli( 'localhost', 'root', '', 'blog' );

//if connection failed show error
if(mysqli_connect_errno()){
	echo "connect error: ". mysqli_connect_errno();
}

//check if the form is submitted
if($_POST){

	//prepare statement
	$stmt = $mysqli->prepare( "insert into product (prod_name, prod_code, prod_price) values (?,?,?)" );

	//get values from form
	$prod_name 	= $_POST['prod_name'];
	$prod_code 	= $_POST['prod_code'];
	$prod_salary = $_POST['prod_salary'];
	

	//bind variable to the prepared statement
	$stmt->bind_param( "sid", $prod_name, $prod_code, $prod_salary );

	if( $stmt->execute() ){
		echo "Product Inserted";
	}
	
	//close statement 
	$stmt->close();

}

//close mysqli connection
$mysqli->close();

?>
<html>
<body>
<form action="" method="post">
	<table>
		<tr>  
			<td>Product Name:</td>
			<td><input type="text" name="prod_name" /></td>	
		</tr>
		<tr>  
			<td>Product Code:</td>	
			<td>
				<select name="prod_code">
					<option>1093</option>
					<option>1094</option>
					<option>1095</option>
				</select>
			</td>	
		</tr>
		<tr>  
			<td>Product Price:</td>	
			<td><input type="text" name="prod_salary" /></td>	
		</tr>
		<tr>  
			<td>&nbsp;</td>	
			<td><input type="submit"  /></td>	
		</tr>
	</table>
</form>
</body>
</html>

The code is self explanatory, just read the comments.

If you already worked in other technologies such as Java or .Net you would know about prepared statement. But PHP developer not used this even though introduced long time back.

These are the steps you have follow:

1. prepare statement (?,?)
2. bind params with the proper data type

s - string
i - integer
d - double
b - blob

3. execute

Post Views: 489

PHP mysqli prepared statement insert example

Like this:

2 thoughts on “PHP mysqli prepared statement insert example”

Leave a ReplyCancel reply

Share this:

Like this:

2 thoughts on “PHP mysqli prepared statement insert example”

Leave a ReplyCancel reply