Pb28 CRUD with MySQLi Prepared Statement using PHP
In this tutorial, we are going to see how to implement the CRUD operations using MySQLi / prepared statement. Using prepared statement is advantageous from many fronts like, performance and security.
Using this method the query is compiled for the first time and the resource will be created and stored in a prepared statement. This object will be used for the next upcoming cases which will reduce the execution time.
In this example, I am creating an employee table in the database to do the CRUD operations. We connect the database and prepare the statement for the INSERT, UPDATE, DELETE and SELECT queries to perform CRUD.
Then, we bind the values to the query parameters if needed. In a previous tutorial, we have seen about PHP CRUD using PDO.
Create using Prepared Statment
This code shows the HTML form for creating new records in the database. It has three fields to collect employee details. On submitting this form, the fields are posted to the PHP page.
<form name="frmUser" method="post" action="">
<div class="button_link"><a href="index.php"> Back to List </a></div>
<table border="0" cellpadding="10" cellspacing="0" width="500" align="center" class="tbl-qa">
<thead>
<tr>
<th colspan="2" class="table-header">Add New Employee</th>
</tr>
</thead>
<tbody>
<tr class="table-row">
<td><label>Department</label></td>
<td><input type="text" name="department" class="txtField"></td>
</tr>
<tr class="table-row">
<td><label>Name</label></td>
<td><input type="text" name="name" class="txtField"></td>
</tr>
<tr class="table-row">
<td><label>Email</label></td>
<td><input type="text" name="email" class="txtField"></td>
</tr>
<tr class="table-row">
<td colspan="2"><input type="submit" name="submit" value="Submit" class="demo-form-submit"></td>
</tr>
</tbody>
</table>
</form>
In PHP code, it will create the prepared statement for the INSERT query and bind the form fields values to the query parameters.
<?php
if (isset($_POST['submit'])) {
require_once("db.php");
$sql = $conn->prepare("INSERT INTO tbl_emp_details (department,name,email) VALUES (?, ?, ?)");
$department=$_POST['department'];
$name = $_POST['name'];
$email= $_POST['email'];
$sql->bind_param("sss", $department, $name, $email);
if($sql->execute()) {
$success_message = "Added Successfully";
} else {
$error_message = "Problem in Adding New Record";
}
$sql->close();
$conn->close();
}
?>
Read Records
The following code is used to read the list of employees from the database. This list contains actions to edit and delete each row.
<?php
require_once("db.php");
$sql = "SELECT * FROM tbl_emp_details";
$result = $conn->query($sql);
$conn->close();
?>
<html>
<head>
<link href="style.css" rel="stylesheet" type="text/css" />
<title>Employee</title>
</head>
<body>
<div class="button_link"><a href="add.php">Add New</a></div>
<table class="tbl-qa">
<thead>
<tr>
<th class="table-header" width="20%">Department </th>
<th class="table-header" width="20%"> Name </th>
<th class="table-header" width="20%"> Email </th>
<th class="table-header" width="20%" colspan="2">Action</th>
</tr>
</thead>
<tbody>
<?php
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
?>
<tr class="table-row" id="row-<?php echo $row["id"]; ?>">
<td class="table-row"><?php echo $row["department"]; ?></td>
<td class="table-row"><?php echo $row["name"]; ?></td>
<td class="table-row"><?php echo $row["email"]; ?></td>
<!-- action -->
<td class="table-row" colspan="2"><a href="edit.php?id=<?php echo $row["id"]; ?>" class="link"><img title="Edit" src="icon/edit.png"/></a> <a href="delete.php?id=<?php echo $row["id"]; ?>" class="link"><img name="delete" id="delete" title="Delete" onclick="return confirm('Are you sure you want to delete?')" src="icon/delete.png"/></a></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</body>
</html>
Update using Prepared Statement
On clicking the edit action on the list page, it shows the Employee edit form to the user. After submitting the edited form data, the prepared statement is created for the update query.
The PHP code sets the value to the update query params and executes it using the prepared statement object. After updating the database row, it reads the latest data and populates in the edit form fields.
<?php
require_once("db.php");
if (isset($_POST['submit'])) {
$sql = $conn->prepare("UPDATE tbl_emp_details SET department=? , name=? , email=? WHERE id=?");
$department=$_POST['department'];
$name = $_POST['name'];
$email= $_POST['email'];
$sql->bind_param("sssi",$department, $name, $email,$_GET["id"]);
if($sql->execute()) {
$success_message = "Edited Successfully";
} else {
$error_message = "Problem in Editing Record";
}
}
$sql = $conn->prepare("SELECT * FROM tbl_emp_details WHERE id=?");
$sql->bind_param("i",$_GET["id"]);
$sql->execute();
$result = $sql->get_result();
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
}
$conn->close();
?>
<html>
<head>
<link href="style.css" rel="stylesheet" type="text/css" />
<style>
.tbl-qa{border-spacing:0px;border-radius:4px;border:#6ab5b9 1px solid;}
</style>
<title>employee edit </title>
</head>
<body>
<?php if(!empty($success_message)) { ?>
<div class="success message"><?php echo $success_message; ?></div>
<?php } if(!empty($error_message)) { ?>
<div class="error message"><?php echo $error_message; ?></div>
<?php } ?>
<form name="frmUser" method="post" action="">
<div class="button_link"><a href="index.php" >Back to List </a></div>
<table border="0" cellpadding="10" cellspacing="0" width="500" align="center" class="tbl-qa">
<thead>
<tr>
<th colspan="2" class="table-header">Employee Edit</th>
</tr>
</thead>
<tbody>
<tr class="table-row">
<td><label>Department</label></td>
<td><input type="text" name="department" class="txtField" value="<?php echo $row["department"]?>"></td>
</tr>
<tr class="table-row">
<td><label>Name</label></td>
<td><input type="text" name="name" class="txtField" value="<?php echo $row["name"]?>"></td>
</tr>
<tr class="table-row">
<td><label>Email</label></td>
<td><input type="text" name="email" class="txtField" value="<?php echo $row["email"]?>"></td>
</tr>
<tr class="table-row">
<td colspan="2"><input type="submit" name="submit" value="Submit" class="demo-form-submit"></td>
</tr>
</tbody>
</table>
</form>
</body>
</html>
Delete using Prepared Statement
The code shows the prepared statement for the delete query. This page will be executed when the delete option on the list page is selected.
The employee id is passed in the delete URL query-string. This id value will be bound to the delete query parameter to delete a specified record from the database.
<?php
require_once("db.php");
$sql = $conn->prepare("DELETE FROM tbl_emp_details WHERE id=?");
$sql->bind_param("i", $_GET["id"]);
$sql->execute();
$sql->close();
$conn->close();
header('location:index.php');
?>
Download