How to Build a CRUD App using PHP MYSQL
How to Build a CRUD App using PHP MySQL: A Step-by-Step Guide
Creating a CRUD (Create, Read, Update, Delete) application using PHP and MySQL is a foundational skill for web developers. In this guide, we’ll walk through the process, ensuring that you have a comprehensive understanding of each step.
Introduction to CRUD Applications
CRUD applications are the backbone of dynamic web development. They allow users to interact with databases, providing functionality for data manipulation. In this tutorial, we will use PHP for server-side scripting and MySQL as the database.
Setting Up Your Development Environment
To get started, ensure you have a local server environment like XAMPP or WAMP installed. These platforms come with PHP and MySQL, making it easier to develop and test your application locally.
- Install XAMPP or WAMP Begin by downloading and installing XAMPP or WAMP. This software package includes Apache, MySQL, and PHP, which are essential for running a CRUD application.
- Create a Database Open phpMyAdmin, which is included with XAMPP/WAMP, and create a new database. Name it
crud_appfor this tutorial.
Building the Frontend with HTML and Bootstrap
The frontend of our CRUD application will use HTML for structure and Bootstrap for styling. This combination provides a clean and responsive user interface.
- Design the Layout Start by creating an
index.htmlfile. Use Bootstrap to design a simple layout with a navigation bar, a form for data entry, and a table to display the records. - Create the Form In the form, include fields for data input, such as name, email, and age. These inputs will correspond to the fields in your MySQL database.
Connecting to the Database with PHP
Now, let’s connect our frontend to the backend using PHP. This step involves creating a connection to the MySQL database and writing the necessary scripts for CRUD operations.
- Database Connection Create a
db.phpfile and write a script to connect to your MySQL database. Ensure you handle any connection errors gracefully.
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- SQL to create a database and the required table CREATE DATABASE crud1; USE crud1; CREATE TABLE `users` ( `sno` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `number` varchar(15) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
2. Insert Data (Create) In your form’s action attribute, specify a PHP file, such as insert.php, to handle data submission. This file will contain the script to insert data into the MySQL database.
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- -- Dumping data for table `users` -- INSERT INTO `crud1` (`sno`, `name`, `number`, `address`, `age`, `email`, `created_at`) VALUES (39, 'Rajesh Mandal', '08686458831', 'hyderabad., Opp. Bharat Petrol Pump,', 25, 'kuntal8@gmai.com', '2024-07-31 08:37:45'), (40, 'Mahesh Mandal', '08686454481', 'Chandigarh., Opp. Bharat Petrol Pump,', 37, 'kntal28@gmail.com', '2024-07-31 08:37:55'), (41, 'Nahesh Mandal', '08696454831', 'Delhi., Opp. Bharat Petrol Pump,', 98, 'kuntl18@gmal.com', '2024-07-31 08:40:35'); |
Implementing Read Functionality
To display data from the database, we’ll implement the “Read” functionality. This involves fetching records from the database and displaying them in a table.
db.php
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "crud1"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } ?> |
- Fetch Records In your
index.html, include a PHP script to fetch and display records from the database. This script should loop through the database records and output them in table rows.index.php
0123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1"><title>CRUD Application</title><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"><link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.3/css/all.min.css"><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css"><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.2/css/buttons.dataTables.min.css"><style>body {background-color: #f8f9fa;font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;font-size: 12px;}.container {background-color: white;border-radius: 10px;padding: 20px;box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.1);}h2 {margin-bottom: 20px;}.form-group {margin-bottom: 15px;}.btn-primary, .btn-secondary {transition: background-color 0.3s;}.btn-primary:hover, .btn-secondary:hover {background-color: #0056b3;}.dataTables_wrapper .dataTables_length,.dataTables_wrapper .dataTables_filter,.dataTables_wrapper .dataTables_info,.dataTables_wrapper .dataTables_paginate {padding: 10px;}.spinner-border {display: none;}.alert-success, .alert-danger {display: none;}.container {text-align: center;margin-bottom: 5px;}.container img {width: 200px;}thead {background-color: black;color: #f8f9fa;font-size: 15px;font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;}</style></head><body><div class="container mt-5"><div class="container"><img src="https://bestwebteacher.com/wp-content/uploads/2024/07/cropped-png.png" alt="Centered Image"/><h2 class="text-center">CRUD Application with Search and Export Options</h2><div id="successMessage" class="alert alert-success">User added successfully!</div><div id="deleteMessage" class="alert alert-danger">User deleted successfully!</div><div id="updateMessage" class="alert alert-success">User updated successfully!</div><form id="userForm" method="POST" action="insert.php" class="mb-4"><div class="row"><div class="col-md-6 col-lg-4"><div class="form-group"><input type="text" class="form-control" name="name" placeholder="Name" required></div></div><div class="col-md-6 col-lg-4"><div class="form-group"><input type="text" class="form-control" name="number" placeholder="Phone Number" required></div></div><div class="col-md-6 col-lg-4"><div class="form-group"><input type="text" class="form-control" name="address" placeholder="Address" required></div></div><div class="col-md-6 col-lg-4"><div class="form-group"><input type="number" class="form-control" name="age" placeholder="Age" required></div></div><div class="col-md-6 col-lg-4"><div class="form-group"><input type="email" class="form-control" name="email" placeholder="Email" required></div></div><div class="col-md-6 col-lg-4"><div class="row"><div class="col-6"><button type="submit" class="btn btn-success w-100">Add User</button></div><div class="col-6"><button type="reset" class="btn btn-secondary w-100">Reset</button></div></div><div class="spinner-border text-primary mt-2" role="status"><span class="sr-only">Loading...</span></div></div></div></form><table id="userTable" class="display table table-striped table-bordered"><thead><tr><th>S.No</th><th>Name</th><th>Number</th><th>Address</th><th>Age</th><th>Email</th><th>Actions</th></tr></thead><tbody><?phpinclude('db.php');// Cleanup logic$sqlCount = "SELECT COUNT(*) as total FROM users";$resultCount = $conn->query($sqlCount);$total = $resultCount->fetch_assoc()['total'];if ($total > 10) {$sqlDelete = "DELETE FROM users ORDER BY created_at ASC LIMIT " . ($total - 10);$conn->query($sqlDelete);}// Cleanup logic end$sql = "SELECT * FROM users";$result = $conn->query($sql);while ($row = $result->fetch_assoc()) {echo "<tr><td>{$row['sno']}</td><td>{$row['name']}</td><td>{$row['number']}</td><td>{$row['address']}</td><td>{$row['age']}</td><td>{$row['email']}</td><td><button class='btn btn-primary btn-sm edit-btn' data-toggle='modal' data-target='#editModal' data-id='{$row['sno']}' data-name='{$row['name']}' data-number='{$row['number']}' data-address='{$row['address']}' data-age='{$row['age']}' data-email='{$row['email']}'><i class='fas fa-edit'></i></button><a href='delete.php?sno={$row['sno']}' class='btn btn-danger btn-sm'><i class='fas fa-trash-alt'></i></a></td></tr>";}?></tbody></table></div><!-- Edit Modal --><div class="modal fade" id="editModal" tabindex="-1" role="dialog" aria-labelledby="editModalLabel" aria-hidden="true"><div class="modal-dialog" role="document"><div class="modal-content"><div class="modal-header"><h5 class="modal-title" id="editModalLabel">Edit User</h5><button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button></div><form id="editForm" method="POST" action="edit.php"><div class="modal-body"><input type="hidden" name="sno" id="editSno"><div class="form-group"><input type="text" class="form-control" name="name" id="editName" placeholder="Name" required></div><div class="form-group"><input type="text" class="form-control" name="number" id="editNumber" placeholder="Phone Number" required></div><div class="form-group"><input type="text" class="form-control" name="address" id="editAddress" placeholder="Address" required></div><div class="form-group"><input type="number" class="form-control" name="age" id="editAge" placeholder="Age" required></div><div class="form-group"><input type="email" class="form-control" name="email" id="editEmail" placeholder="Email" required></div></div><div class="modal-footer"><button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button><button type="submit" class="btn btn-primary">Save changes</button></div></form></div></div></div></div><script src="https://code.jquery.com/jquery-3.5.1.js"></script><script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script><script src="https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js"></script><script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script><script src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.html5.min.js"></script><script src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.print.min.js"></script><script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.11.8/dist/umd/popper.min.js"></script><script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script><script>$(document).ready(function() {$('#userTable').DataTable({dom: 'Bfrtip',buttons: ['copy', 'csv', 'excel', 'pdf', 'print']});// Show success message if parameter is setif (window.location.search.includes('success=true')) {$('#successMessage').fadeIn().delay(5000).fadeOut();}// Show delete message if parameter is setif (window.location.search.includes('delete=true')) {$('#deleteMessage').fadeIn().delay(5000).fadeOut();}// Show update message if parameter is setif (window.location.search.includes('edit=true')) {$('#updateMessage').fadeIn().delay(5000).fadeOut();}// Populate edit modal$('#userTable').on('click', '.edit-btn', function() {var button = $(this);$('#editSno').val(button.data('id'));$('#editName').val(button.data('name'));$('#editNumber').val(button.data('number'));$('#editAddress').val(button.data('address'));$('#editAge').val(button.data('age'));$('#editEmail').val(button.data('email'));});});</script></body></html>
Updating and Deleting Records
For the “Update” and “Delete” functionalities, we will create additional PHP scripts. These will handle data updates and deletions based on user interactions.
insert.php
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<?php include('db.php'); if ($_SERVER['REQUEST_METHOD'] === 'POST') { $name = $_POST['name']; $number = $_POST['number']; $address = $_POST['address']; $age = $_POST['age']; $email = $_POST['email']; $sql = "INSERT INTO users (name, number, address, age, email) VALUES ('$name', '$number', '$address', '$age', '$email')"; if ($conn->query($sql) === TRUE) { header('Location: index.php'); } else { echo "Error: " . $sql . "<br>" . $conn->error; } } // After successful insertion header('Location: index.php?success=true'); exit(); ?> |
- pdate Data Create an
edit.phpfile. This script will retrieve a specific record, allow the user to make changes, and then update the record in the database.
edit.php
012345678910111213141516171819202122232425262728293031<?phpinclude('db.php');// Sanitize and validate input$sno = (int)$_POST['sno'];$name = $conn->real_escape_string($_POST['name']);$number = $conn->real_escape_string($_POST['number']);$address = $conn->real_escape_string($_POST['address']);$age = (int)$_POST['age'];$email = $conn->real_escape_string($_POST['email']);// Check for empty fieldsif (empty($name) || empty($number) || empty($address) || empty($age) || empty($email)) {header('Location: index.php?error=empty_fields');exit();}// Update data in the database$sql = "UPDATE users SET name='$name', number='$number', address='$address', age='$age', email='$email' WHERE sno='$sno'";if ($conn->query($sql) === TRUE) {header('Location: index.php?edit=true');} else {header('Location: index.php?error=update_failed');}exit();?>
- Delete Data Similarly, create a
delete.phpfile. This script will handle the deletion of records based on a unique identifier, such as an ID.delete.php
012345678910111213141516171819202122<?phpinclude('db.php');if (isset($_GET['sno'])) {$sno = $_GET['sno'];$sql = "DELETE FROM users WHERE sno = $sno";if ($conn->query($sql) === TRUE) {header('Location: index.php');} else {echo "Error deleting record: " . $conn->error;}}// After successful deletionheader('Location: index.php?delete=true');exit();?>
Conclusion and Best Practices
Building a CRUD application using PHP and MySQL is a practical way to learn about web development. Always validate and sanitize user inputs to prevent SQL injection and other security vulnerabilities. Additionally, consider using prepared statements for database interactions.
By following this guide, you’ve learned the essential steps to create a functional CRUD application. Continue to explore more advanced features and best practices in PHP and MySQL development to enhance your skills.
