CRUD stands for Create, Read, Update, and Delete. These four operations represent the core functionality of most dynamic web applications. CRUD allows users to insert data into a database, fetch and display it on a webpage, and subsequently update or delete that data. In this tutorial, we will use a MySQL database to create a secure CRUD application using PHP. A CRUD system is essential for managing user data seamlessly.
Steps to Create a CRUD Application in PHP
You need to follow a few straightforward steps to build a CRUD application using PHP and MySQL. This involves creating a database table and writing PHP scripts to handle each operation securely using PDO (PHP Data Objects).
Table of Contents:
1. Create a MySQL database table.
2. Make a database connection file.
Now, let's implement the stepwise process:
1. Database Setup: We will create a table in MySQL for insert, read, update, and delete operations.
2. Connection: Create a configuration file to securely connect your PHP script to the database.
3. Create (Insert): Insert operation is a must for a CRUD application. It allows users to add data into a database using an HTML form and PHP prepared statements.
4. Read (Display): The next step is fetching and displaying the stored data on a webpage dynamically.
5. Update (Edit): The update operation allows users to modify existing records on the webpage using HTML text boxes.
6. Delete (Remove): Finally, the delete operation allows you to remove specific data from the MySQL database table with a single click.
Step 1: Create a MySQL Database Table
First, we need to create a MySQL database table where all our data will be stored. You can run the following SQL query in your phpMyAdmin to create the `users` table.
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`age` INT(3) NOT NULL,
`email` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
Step 2: Make a Database Connection File (PDO)
Create a connection file named config.php. We will use PDO (PHP Data Objects) to connect to the database, as it provides a secure way to prevent SQL injection.
<?php
$databaseHost = 'localhost';
$databaseName = 'test';
$databaseUsername = 'root';
$databasePassword = '';
try {
// Create a PDO connection
$pdo = new PDO("mysql:host=$databaseHost;dbname=$databaseName;charset=utf8mb4", $databaseUsername, $databasePassword);
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
?>
1. $databaseHost: Define your host (localhost or 127.0.0.1).
2. $databaseName: Enter your database name.
3. $databaseUsername: Enter your database username (usually 'root' for localhost).
4. $databasePassword: Enter your database password (usually blank for localhost).
Step 3: Insert Data into the Database (Create)
We use the insert operation to store data in the MySQL database. First, we create an HTML form. When the form is submitted, the PHP script will use PDO prepared statements to securely save the information.
HTML Form (add.html)
<form action="add.php" method="post" name="form1">
<table border="0" width="25%">
<tbody>
<tr>
<td>Name</td>
<td><input name="name" type="text" required /></td>
</tr>
<tr>
<td>Age</td>
<td><input name="age" type="number" required /></td>
</tr>
<tr>
<td>Email</td>
<td><input name="email" type="email" required /></td>
</tr>
<tr>
<td></td>
<td><input name="Submit" type="submit" value="Add User" /></td>
</tr>
</tbody>
</table>
</form>
CRUD Application CSS Design
You can design the CRUD application form and buttons using this simple CSS.
<style type="text/css">
input[type=submit] {
width: 100%;
padding: 10px;
margin: 5px 0;
box-sizing: border-box;
border: 2px solid #000000;
outline: none;
background-color: green;
color: white;
font-size: 18px;
cursor: pointer;
}
table tr {
color: #333;
}
input[type=text], input[type=number], input[type=email] {
width: 100%;
padding: 9px;
margin: 5px 0;
border: 1px solid #000000;
outline: none;
}
input[type=text]:focus, input[type=number]:focus, input[type=email]:focus {
background-color: lightblue;
}
</style>
Now, we create the PHP script (add.php). When the user fills out the form and clicks submit, the data will be saved to the database securely using PDO parameters.
Insert PHP Code (add.php)
<?php
// Include the database connection file
include_once("config.php");
if(isset($_POST['Submit'])) {
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
// Check for empty fields
if(empty($name) || empty($age) || empty($email)) {
echo "<font color='red'>All fields are required.</font><br/>";
} else {
// Use PDO Prepared Statements to prevent SQL injection
$sql = "INSERT INTO users (name, age, email) VALUES (:name, :age, :email)";
$stmt = $pdo->prepare($sql);
// Execute the query by binding the values
$stmt->execute([':name' => $name, ':age' => $age, ':email' => $email]);
echo "<font color='green'>Data added successfully.</font>";
echo "<br/><a href='index.php'>View Results</a>";
}
}
?>
Step 4: Display Data from MySQL using an HTML Table (Read)
After inserting data, we need a script to fetch and display it on the webpage. This is the Read operation. We will use a `SELECT` query and a `while` loop to print each row inside an HTML table.
Display Data in Table (index.php)
<?php
// Include the database connection file
include_once("config.php");
// Fetch data in descending order (latest entry first)
$sql = "SELECT * FROM users ORDER BY id DESC";
$stmt = $pdo->query($sql);
?>
<html>
<head>
<title>Homepage</title>
</head>
<body>
<a href="add.html">Add New Data</a><br/><br/>
<table width='80%' border=0>
<tr bgcolor='#CCCCCC'>
<td>Name</td>
<td>Age</td>
<td>Email</td>
<td>Update</td>
<td>Remove</td>
</tr>
<?php
// Fetch rows as an associative array
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";
echo "<td>".$row['name']."</td>";
echo "<td>".$row['age']."</td>";
echo "<td>".$row['email']."</td>";
echo "<td><a href=\"edit.php?id=$row[id]\">Edit</a></td>";
echo "<td><a href=\"delete.php?id=$row[id]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";
echo "</tr>";
}
?>
</table>
</body>
</html>
Step 5: Update Data in the Database (Update)
The update operation allows users to edit previously submitted data. First, we select the specific row using its unique `id` and display the existing values in an HTML form. Once the user submits the changes, we execute an `UPDATE` query using PDO prepared statements.
Update Data PHP Code (edit.php)
<?php
// Include the database connection file
include_once("config.php");
// Handle form submission to update data
if(isset($_POST['update'])) {
$id = $_POST['id'];
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
// Check for empty fields
if(empty($name) || empty($age) || empty($email)) {
echo "<font color='red'>All fields are required.</font><br/>";
} else {
// PDO Prepared Statement for Update
$sql = "UPDATE users SET name=:name, age=:age, email=:email WHERE id=:id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':name' => $name,
':age' => $age,
':email' => $email,
':id' => $id
]);
// Redirect back to the homepage
header("Location: index.php");
}
}
?>
<?php
// Fetch existing data to populate the form
$id = $_GET['id'];
// Securely fetch data based on ID
$sql = "SELECT * FROM users WHERE id=:id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$name = $row['name'];
$age = $row['age'];
$email = $row['email'];
?>
<html>
<head>
<title>Edit Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form name="form1" method="post" action="edit.php">
<table border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" value="<?php echo $name;?>"></td>
</tr>
<tr>
<td>Age</td>
<td><input type="number" name="age" value="<?php echo $age;?>"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="email" name="email" value="<?php echo $email;?>"></td>
</tr>
<tr>
<td><input type="hidden" name="id" value="<?php echo $_GET['id'];?>"></td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>
Step 6: Delete Data from MySQL (Delete)
This is the final operation of our CRUD application. When a user clicks the "Delete" link on the homepage, the row's `id` is passed via the URL. The `delete.php` script catches this ID and securely executes a `DELETE` query to remove the record from the database.
Delete Data (delete.php)
<?php
// Include the database connection file
include("config.php");
// Get the ID from the URL parameter
$id = $_GET['id'];
// Secure PDO Prepared Statement to delete data
$sql = "DELETE FROM users WHERE id=:id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
// Redirect back to the homepage
header("Location: index.php");
?>
Recommended Posts:-
