CRUD operations using PHP MySQLi Prepared Statement
Updated on
...
04th September 2023
In this tutorial, we're going to show you how to use prepared statements in MySQLi to implement CRUD operations. Implementing prepared statements has benefits on several levels, including performance and security.
With this method, the SQL query is compiled for the first time, and then the resource will be created and stored in a prepared statement. The execution time will be reduced by using this object for the next upcoming scenarios.
To perform the CRUD operations in this example, I am creating a student table in the database. In order to do CRUD operations, First we have to connect the database and then construct the statement for the INSERT, UPDATE, DELETE, and SELECT queries.
Let's see the folder structure:
Create Database and table:
Now we will create our table after creating the database just past below SQL code in the PHP MY ADMIN SQL tab
CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`gender` varchar(20) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Indexes for table `students`
--
ALTER TABLE `students`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;
COMMIT;
Create dbconn.php for creating the database connection
Create an include folder inside the project directory and then create dbconn.php inside that folder and past the code.
<?php
session_start();
date_default_timezone_set('Asia/Kolkata');
$server = "localhost";
$user = "root";
$password = "";
$database = "mysqli_prepared_crud";
try {
$conn = new mysqli($server, $user, $password, $database);
if ($conn->connect_error) {
throw new Exception($conn->connect_error);
}
//$conn->close();
} catch (Exception $e) {
$errorLog = "[" . date('Y-m-d H:i:s') . "] Error in " . __FILE__ . ": " . $e->getMessage() . "\n";
file_put_contents('custom_error_log.txt', $errorLog, FILE_APPEND);
// Display a user-friendly error message (optional)
echo "An error occurred. Please try again later.";
$conn->close();
}
Create index.php file
In the main project directory create a file index.php and paste the below code.
This code shows the HTML table for showing the record and adds, edit, and delete buttons.
<?php
require_once('include/dbconn.php');
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css" integrity="sha512-z3gLpd7yknf1YoNbCzqRKc4qyor8gaKU1qmn+CShxbuBusANI9QpRohGBreCFkKxLhei6S9CQXFEbbKuqLg0DA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<div class="ath_container">
<?php if (isset($_SESSION['success_message'])) { ?>
<div class="success_msg">
<?php echo $_SESSION['success_message']; ?>
</div>
<?php } ?>
<div class="table_head">
<h2>Student record</h2>
<a href="create.php" class="add_btn"><i class="fa-solid fa-plus"></i> Add New</a>
</div>
<div style="overflow-x:auto;">
<table class="custom_table">
<tr>
<th>#</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Gender</th>
<th>Action</th>
</tr>
<?php
$query = "SELECT * FROM students ORDER BY id DESC";
$stmt = $conn->prepare($query);
$stmt->execute();
if ($stmt->execute()) {
$result = $stmt->get_result();
// $row = $result->fetch_assoc();
//print_r($row);
// Using while loop to fetch results
$num = 1;
while ($row = $result->fetch_object()) {
?>
<tr>
<td><?php echo $num++ ?></td>
<td><?php echo $row->name; ?></td>
<td><?php echo $row->email; ?></td>
<td><?php echo $row->phone; ?></td>
<td><?php echo $row->gender; ?></td>
<td>
<div class="action_btn">
<a href="edit.php?id=<?php echo $row->id; ?>" class="edit"><i class="fa-solid fa-pen-to-square"></i></a>
<a href="php_action/delete.php?id=<?php echo $row->id; ?>" class="delete"><i class="fa-solid fa-trash"></i></a>
</div>
</td>
</tr>
<?php
}
$stmt->close();
}
?>
</table>
</div>
</div>
</body>
</html>
<?php
unset($_SESSION['success_message']);
//unset($_SESSION['flash_message']);
?>
Create create.php and create_record.php file
Create create.php file:
In the main project directory create a file create.php and paste the below code.
This code shows the HTML form for creating a new record in the database.
<?php
require_once('include/dbconn.php');
if (isset($_SESSION['form_errors'])) {
$errors = $_SESSION['form_errors'];
print_r($errors);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css" integrity="sha512-z3gLpd7yknf1YoNbCzqRKc4qyor8gaKU1qmn+CShxbuBusANI9QpRohGBreCFkKxLhei6S9CQXFEbbKuqLg0DA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<div class="ath_container">
<div class="table_head">
<h2>Add record</h2>
<a href="index.php" class="add_btn"><i class="fa-solid fa-plus"></i> Go back to list</a>
</div>
<form action="php_action/create_record.php" class="ath_form" method="post">
<label for="name">Name</label>
<input type="text" name="name" id="name" class="c_input" value="<?php echo isset($_SESSION['submitted_values']['name']) ? $_SESSION['submitted_values']['name'] : ''; ?>">
<span class="error"><?php echo isset($errors['name']) ? $errors['name'] : ''; ?></span>
<label for="email">Email</label>
<input type="email" name="email" id="email" class="c_input" value="<?php echo isset($_SESSION['submitted_values']['email']) ? $_SESSION['submitted_values']['email'] : ''; ?>">
<span class="error"><?php echo isset($errors['email']) ? $errors['email'] : ''; ?></span>
<label for="phone">Phone</label>
<input type="text" name="phone" class="c_input" value="<?php echo isset($_SESSION['submitted_values']['phone']) ? $_SESSION['submitted_values']['phone'] : ''; ?>">
<span class="error"><?php echo isset($errors['phone']) ? $errors['phone'] : ''; ?></span>
<label for="gender">Select Gender</label>
<select name="gender" id="gender" class="c_input">
<option value="">-- Select --</option>
<option value="Male" <?php echo isset($_SESSION['submitted_values']['gender']) && $_SESSION['submitted_values']['gender'] == 'Male' ? 'selected' : ''; ?>>Male</option>
<option value="Female" <?php echo isset($_SESSION['submitted_values']['gender']) && $_SESSION['submitted_values']['gender'] == 'Female' ? 'selected' : ''; ?>>Female</option>
</select>
<span class="error"><?php echo isset($errors['gender']) ? $errors['gender'] : ''; ?></span>
<button type="submit" class="submit_btn">Submit</button>
</form>
</div>
</body>
</html>
<?php
unset($_SESSION['form_errors']);
unset($_SESSION['submitted_values']);
//unset($_SESSION['flash_message']);
?>
create create_record.php file:
In the main project directory create a file php_actions/create create_record.php and paste the below code.
This PHP code will insert the new record in the database and then return back to the index.php page. and it will return back with an error message if is there any form of validation fails.
<?php
require_once('../include/dbconn.php');
$errors = array(); // To store validation errors
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
$gender = trim($_POST['gender']);
// Store submitted values in session
$_SESSION['submitted_values'] = [
'name' => $name,
'email' => $email,
'phone' => $phone,
'gender' => $gender
];
// Validation
if (empty($name)) {
$errors['name'] = "Name is required.";
}
if (empty($email)) {
$errors['email'] = "Email is required.";
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors['email'] = "Invalid email format.";
}
if (empty($phone)) {
$errors['phone'] = "Mobile Number is required.";
}
if (empty($gender)) {
$errors['gender'] = "Gender is required.";
}
// Perform additional validation for other fields
// If there are no validation errors, proceed to insert
if (empty($errors)) {
$query = "INSERT INTO students (name, email, phone, gender) VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($query);
$stmt->bind_param('ssss', $name, $email, $phone, $gender);
if ($stmt->execute()) {
$_SESSION['success_message'] = "Record added successfully!";
header("Location: ../index.php"); // Redirect with success message
exit;
} else {
$_SESSION['error_message'] = "Error while adding record.";
}
} else {
// Errors were found, store them in session and redirect back
$_SESSION['form_errors'] = $errors;
header("Location: ../create.php"); // Redirect with success message
exit;
}
}
Create edit.php and update_record.php file
Create edit.php file:
In the main project create a file edit.php and paste the below code.
This code shows the HTML form with the existing record for editing existing records in the database.
<?php
require_once('include/dbconn.php');
if (isset($_SESSION['form_errors'])) {
$errors = $_SESSION['form_errors'];
print_r($errors);
}
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
$id = $_GET['id'];
$query = "SELECT * FROM students WHERE id = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param('i', $id);
if ($stmt->execute()) {
$result = $stmt->get_result();
$record = $result->fetch_object();
// print_r($record);
} else {
echo "Error executing the query.";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css" integrity="sha512-z3gLpd7yknf1YoNbCzqRKc4qyor8gaKU1qmn+CShxbuBusANI9QpRohGBreCFkKxLhei6S9CQXFEbbKuqLg0DA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<div class="ath_container">
<div class="table_head">
<h2>Add record</h2>
<a href="index.php" class="add_btn"><i class="fa-solid fa-plus"></i> Go back to list</a>
</div>
<form action="php_action/update_record.php?id=<?php echo $record->id; ?>" class="ath_form" method="post">
<label for="name">Name</label>
<input type="text" name="name" id="name" class="c_input" value="<?php echo !empty($record->name) ? $record->name : ''; ?>">
<span class="error"><?php echo isset($errors['name']) ? $errors['name'] : ''; ?></span>
<label for="email">Email</label>
<input type="email" name="email" id="email" class="c_input" value="<?php echo !empty($record->email) ? $record->email : ''; ?>">
<span class="error"><?php echo isset($errors['email']) ? $errors['email'] : ''; ?></span>
<label for="phone">Phone</label>
<input type="text" name="phone" class="c_input" value="<?php echo !empty($record->phone) ? $record->phone : ''; ?>">
<span class="error"><?php echo isset($errors['phone']) ? $errors['phone'] : ''; ?></span>
<label for="gender">Select Gender</label>
<select name="gender" id="gender" class="c_input">
<option value="">-- Select --</option>
<option value="Male" <?php echo ($record->gender == 'Male') ? 'selected' : ''; ?>>Male</option>
<option value="Female" <?php echo ($record->gender == 'Female') ? 'selected' : ''; ?>>Female</option>
</select>
<span class="error"><?php echo isset($errors['gender']) ? $errors['gender'] : ''; ?></span>
<button type="submit" class="submit_btn">Submit</button>
</form>
</div>
</body>
</html>
<?php
unset($_SESSION['form_errors']);
unset($_SESSION['submitted_values']);
//unset($_SESSION['flash_message']);
?>
create update_record.php file:
In the main project directory create a file php_actions/create create_record.php and paste the below code.
This PHP code will Update the existing on the basis of the record ID record in the database and then return back to the index.php page. and it will return back with an error message if is there any form of validation fails.
<?php
require_once('../include/dbconn.php');
$errors = array(); // To store validation errors
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_GET['id'])) {
$id = trim($_GET['id']);
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
$gender = trim($_POST['gender']);
// Store submitted values in session
$_SESSION['submitted_values'] = [
'name' => $name,
'email' => $email,
'phone' => $phone,
'gender' => $gender
];
// Validation
if (empty($name)) {
$errors['name'] = "Name is required.";
}
if (empty($email)) {
$errors['email'] = "Email is required.";
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors['email'] = "Invalid email format.";
}
if (empty($phone)) {
$errors['phone'] = "Mobile Number is required.";
}
if (empty($gender)) {
$errors['gender'] = "Gender is required.";
}
// Perform additional validation for other fields
// If there are no validation errors, proceed to insert
if (empty($errors)) {
$query = "UPDATE students SET name = ?, email = ?, phone = ?, gender = ? WHERE id = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param('ssssi', $name, $email, $phone, $gender, $id);
if ($stmt->execute()) {
$_SESSION['success_message'] = "Record updated successfully!";
header("Location: ../index.php"); // Redirect with success message
exit;
} else {
$_SESSION['error_message'] = "Error while updateding record.";
}
} else {
// Errors were found, store them in session and redirect back
$_SESSION['form_errors'] = $errors;
header("Location: ../edit.php/$id"); // Redirect with success message
exit;
}
}
Create delete.php file
Create delete.php inside the php_action folder for handling delete requests. just copy and paste the below code.
<?php
require_once('../include/dbconn.php');
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
$id = trim($_GET['id']);
// Perform additional validation for other fields
$query = "DELETE FROM students WHERE id = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param('i', $id);
if ($stmt->execute()) {
$_SESSION['success_message'] = "Record deleted successfully!";
header("Location: ../index.php"); // Redirect with success message
exit;
} else {
$_SESSION['error_message'] = "Error while deleting record.";
echo "Error";
}
}
Finally, create a style.css
Now finally we are going to create a style.css file inside assets/css folder for making some good UI.
* {
margin: 0px;
padding: 0px;
box-sizing: border-box;
}
body {
font-family: Arial, Helvetica, sans-serif;
}
.ath_container {
width: 700px;
max-width: 100%;
margin: auto;
box-shadow: 0 0 5px 0 rgba(0, 0, 0, .3);
padding: 15px;
margin-top: 20px;
}
h2 {
margin-bottom: 20px;
}
.custom_table {
border-collapse: collapse;
border-spacing: 0;
width: 100%;
border: 1px solid #ddd;
}
.custom_table th,
.custom_table td {
text-align: left;
padding: 8px;
}
.custom_table tr:nth-child(even) {
background-color: #f2f2f2
}
.action_btn a {
margin-right: 15px;
display: inline-block;
}
.action_btn a:hover {
color: green;
}
.action_btn a.delete:hover {
color: red;
}
.add_btn {
background: #4e59f1;
padding: 8px 15px;
display: inline-block;
border-radius: 5px;
color: #fff;
text-decoration: none;
}
.table_head {
display: flex;
justify-content: space-between;
align-items: center;
}
.ath_form label,
.ath_form input,
.ath_form select {
display: block;
width: 100%;
}
.ath_form label {
margin-bottom: 10px;
margin-top: 15px;
}
.ath_form .c_input {
padding: 8px;
border: 1px solid #ccc;
border-radius: 4px;
}
.ath_form .c_input:focus {
border: 1px solid #6890ff;
outline: none;
}
.ath_form .submit_btn {
background: #4e59f1;
padding: 10px 20px;
display: inline-block;
border-radius: 5px;
color: #fff;
text-decoration: none;
outline: none;
box-shadow: none;
border: none;
cursor: pointer;
margin-top: 15px;
}
.ath_form .submit_btn:hover {
background: #1120f1;
}
span.error {
color: red;
}
.success_msg {
color: #44c30f;
background: #ededed;
padding: 10px;
border-radius: 4px;
margin-bottom: 15px;
}
