Creating a basic CRUD Create, Read, Update, Delete application
CRUD applications in PHP web development scripts are known as the backbone of web development systems. CRUD in PHP development means that you explore the following elements in a CRUD operation:
- Create – Inserts new data into a database.
- Read – Removes old data from an existing database.
- Update – Updates existing data as needed.
- Delete – Deletes old data from an existing database if needed.
In PHP web development scripts, CRUD operations are typically performed using a MySQL database. This guide provides step-by-step instructions for creating a simple CRUD application.
First, set up a database.
First, create a database and a table in MySQL.
CREATE DATABASE crude_database_app;
USE crude_database_app;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(140),
email VARCHAR(130),
int emp_age
);
Create a database connection file.
Create the db.php file to create a new database connection in a PHP web development script.
<?php
$conn = mysqli_connect(“localhost”, “root”, “”, “crude_database_app”);
if (!$conn) {
die(“Database Connection failed – ” . mysqli_connect_error());
}
?>
Create a form and insert data into the database.
Create HTML form schema.
<form method=”POST” action=””>
Emp_Name – <input type=”text” name=”emp_name” required><br>
Email – <input type=”email” name=”email” required><br>
emp_age – <input type=” text ” name=” emp_age ” required><br>
<input type=”submit” name=”submit” value=”Add New User”>
</form>
PHP data insert code.
<?php
include ‘db.php’;
if (isset($_POST[‘submit’])) {
$name = $_POST[’emp_name ‘];
$email = $_POST[’email’];
$emp_age = $_POST[’emp_age’];
$stmt = mysqli_prepare($conn, “INSERT INTO users (emp_name, email, emp_age) VALUES (?, ?)”);
mysqli_stmt_bind_param($stmt, “ss”, $emp_name, $email, $emp_age);
mysqli_stmt_execute($stmt);
echo “New Database User added successfully in database”;
}
?>
Read data from user database (display data).
<?php
include ‘db.php’;
$result = mysqli_query($conn, “SELECT * FROM users”);
echo “<table border=’1′>
<tr>
<th>ID</th>
<th>emp_name</th>
<th> email </th>
<th>emp_age</th>
<th>Actions</th>
</tr>”;
while ($row = mysqli_fetch_assoc($result)) {
echo “<tr>
<td>”.$row[‘id’].”</td>
<td>”.$row[‘ emp_name ‘].”</td>
<td>”.$row[’email’].”</td>
<td>”.$row[‘ emp_age ‘].”</td>
<td>
<a href=’update.php?id=”.$row[‘id’].”‘>Edit</a> |
<a href=’delete.php?id=”.$row[‘id’].”‘>Delete</a>
</td>
</tr>”;
}
echo “</table>”;
?>
Update existing database (edit data).
Here update the update.php user file as per requirement.
<?php
include ‘db.php’;
$id = $_GET[‘id’];
$result = mysqli_query($conn, “SELECT * FROM users WHERE id=$id”);
$row = mysqli_fetch_assoc($result);
?>
<form method=”POST”>
Name: <input type=”text” name=” emp_name ” value=”<?php echo $row[‘ emp_name ‘]; ?>”><br>
Email: <input type=”email” name=”email” value=”<?php echo $row[’email’]; ?>”><br>
Email: <input type=” emp_age ” name=” emp_age ” value=”<?php echo $row[‘ emp_age ‘]; ?>”><br>
<input type=”submit” name=”update” value=”Update”>
</form>
<?php
if (isset($_POST[‘update’])) {
$name = $_POST[‘name’];
$email = $_POST[’email’];
$email = $_POST[’emp_age’];
$stmt = mysqli_prepare($conn, “UPDATE users SET name=?, email=?, emp_age =?, WHERE id=?”);
mysqli_stmt_bind_param($stmt, “ssi”, $name, $email, $emp_age, $id);
mysqli_stmt_execute($stmt);
header(“Location: index.php”);
}
?>
Delete data from a database table.
Manage user delete.php file in database table.
<?php
include ‘db.php’;
$id = $_GET[‘id’];
$stmt = mysqli_prepare($conn, “DELETE FROM users WHERE id=?”);
mysqli_stmt_bind_param($stmt, “i”, $id);
mysqli_stmt_execute($stmt);
header(“Location: index.php”);
?>
Detailed project database file structure explanation.
crude_database_app/
│
├── db.php
├── index.php (create + read)
├── update.php
├── delete.php
Best practice steps for CRUD application security.
- Apply the prepared statement here, as done here.
- Validate and properly sanitize any user input in the database table.
- Use the htmlspecialchars() function method to prevent XSS attacks in web scripts.
- Avoid displaying any errors in the existing database.
- Use proper redirects after proper operations.
Improvements to your app that web developers can add.
- Display a form validation message in your database.
- Create pagination for large-volume database data.
- Set up proper search functionality.
- Set up a proper authentication system for new and existing database users.
- Use CSS styling (Bootstrap for a better UI) for your app’s commercial user interface.
Creating a basic CRUD Create, Read, Update, Delete application concludes.
This PHP web development script demonstrates a basic CRUD application, showing how a database user interacts with a database file in the proper order. By combining HTML forms, SQL queries, and PHP logic, PHP web developers can create dynamic applications that help database users easily manage or store data. Using prepared statements ensures database system security. Properly structuring your app project makes your app software scalable and maintainable.
