Insert using Prepared Statement in PHP MySQL
As in our earlier article we have learned simple database connection using PHP Code. Now we are going to demonstrate an example of Insert Query using Prepared Statement in PHP. For the same we have used the Same database and table, which was used in previous example.
We have created a Simple for using BootStrap like
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Simple Insert Demo</title>
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<!-- jQuery library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<!-- Latest compiled JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>
<h3>Insert Website Details</h3>
<form name="frmWebs" action="insertdatacode.php" method="post">
<div class="row">
<div class="form-group">
<label class="control-label col-sm-2" for="websitename">Website Name:</label>
<div class="col-sm-3">
<input type="url" class="form-control" id="websitename" name="websitename" placeholder="Enter Website Name">
</div>
</div>
</div>
<div class="row">
<div class="form-group">
<label class="control-label col-sm-2" for="websitedetails">Website Details:</label>
<div class="col-sm-3">
<textarea class="form-control" id="websitedetails" name="websitedetails">
</textarea>
</div>
</div>
</div>
<div class="row">
<div class="form-group">
<label class="control-label col-sm-2" for="websiterank">Website Rank:</label>
<div class="col-sm-3">
<input type="number" class="form-control" id="websiterank" placeholder="Enter Website Rank" name="websiterank"/>
</div>
</div>
</div>
<div class="row">
<div class="form-group">
<label class="control-label col-sm-2" for="websitedate">Website Creation Date</label>
<div class="col-sm-3">
<input type="date" class="form-control" id="websitedate" name="websitedate" placeholder="Website Details">
</div>
</div>
</div>
<label class="control-label col-sm-3"></label>
<button type="submit" class="btn btn-default" name="Submit">Submit</button>
</form>
</body>
</html>
As you checked we have created a new file name insertdatacode.php and in action attribute of <form> we have placed the same. <form action=”insertdatacode.php”…> in above code. Now when you press submit button insertdatacode.php file will be executed it has code like:
<?php
print_r($_POST);
if(isset($_POST["Submit"]))
{
//Get the Data from Form
$nm=$_POST["websitename"]; //name of Controls
$det=$_POST["websitedetails"];
$rank=$_POST["websiterank"];
$dt=$_POST["websitedate"];
//Code for Connection to Database
$server="localhost";
$user="root";
$psw="";
$dbname ="dbphpdemo";
$con=new MySQLi($server,$user,$psw,$dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//Code for Insert
$stmt = $con->prepare("INSERT INTO tblwebsitedetails (strWebsiteName,strWebsiteDetails,dtmCreationDate,intRank) VALUES (?, ?, ?,?)");
if($stmt==false)
{
echo "<br/>Please Check Query/Table/Column Names";
return;
}
$stmt->bind_param("sssi", $nm, $det, $dt,$rank); //s for string or date, i for integer
$x=$stmt->execute();
if($x>0)
{
header("location:selectdata.php");
}
}
?>Here Database configuration is same like our Select Example (Previous Article). $con=new MySQLi($server,$user,$psw,$dbname); is used for connecting to Database and Server. $con->connect_error is used to check if database connection is done or failed. Now $stmt = $con->prepare(“INSERT INTO tblwebsitedetails (strWebsiteName,strWebsiteDetails,dtmCreationDate,intRank) VALUES (?, ?, ?,?)”); will create a statement with parameters. If there is any error in table name or column names then value for $stmt will be false and you can not bind_param to false statement.
$stmt->bind_param(“sssi”, $nm, $det, $dt,$rank); is used to bind the values to parameters. “sssi” is String,String,String,Integer Means First Parameter is String, Second is String,Third is String and Forth is Integer.
$x=$stmt->execute(); is used to execute the Statement. If insertion has been done means x>0 then we have forwarded to the page selectdata.php which was created during our previous Example.