CRUD stands for Create, Read, Update, and Delete. These are the four basic functions of persistent storage. CRUD applications are used in nearly every web application where data management is required.
Technologies Used
PHP: A popular server-side scripting language used to develop web applications.
MySQL: An open-source relational database management system.
phpMyAdmin: A web-based tool for managing MySQL databases.
Prerequisites
XAMPP/WAMP: XAMPP and WAMP are software packages that provide a complete web server environment, including Apache, MySQL, and PHP. After installing XAMPP/WAMP, open the control panel. Start Apache and MySQL services by clicking the ‘Start’ button next to each service.
Step 1: Create a Database
Open your browser and navigate to http://localhost/phpmyadmin. Click on the new button in the sidebar. Then enter the database name.
Database creation
Step 2: Create a Table
Now navigate to your database organization. Select the SQL tab to insert a SQL statement. Then add the following query to add a new table named employees.
CREATE TABLE employees ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, address VARCHAR(255) NOT NULL, salary INT(10) NOT NULL );
Or else you can manually add the table.
Press enter or click to view image in full size
Add table
Step 3: Connecting to the Database
After creating the table, we need create a PHP script in order to connect to the MySQL database server. Let’s create a file named config.php and add the following code inside it.
/* Connecting to MySQL database */ $mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check connection if($mysqli === false){ die("ERROR: Could not connect. " . $mysqli->connect_error); } ?>
The constructor of the mysqli class takes four parameters:
DB_SERVER: The server name or IP address where MySQL is running.
DB_USERNAME: The MySQL username.
DB_PASSWORD: The MySQL password.
DB_NAME: The name of the database to connect to.
Step 4: Creating the Landing Page
First step is to create a landing page for our CRUD application that contains a data grid showing the records from the employees table. It also has action icons for each record displayed in the grid, that you may choose to view its details, update it, or delete it.
Get Dharshitha Senevirathne’s stories in your inbox
Join Medium for free to get updates from this writer.
We’ll also add a create button on the top of the data grid that can be used for creating new records in the employees table. Create a file named index.php and put the following code in it.
To test the landing page, you can add a record via the phpMyAdmin.
Press enter or click to view image in full size
Step 5: Create the Insert/ Create Page
Now we will focus on the Create functionality of our CRUD application.
Let’s create a file named create.php and put the following code inside it. It will generate a web form that can be used to insert records in the employees table.
<?php // Include config file require_once "config.php";
// Processing form data when form is submitted if($_SERVER["REQUEST_METHOD"] == "POST"){ // Validate name $input_name = trim($_POST["name"]); if(empty($input_name)){ $name_err = "Please enter a name."; } elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){ $name_err = "Please enter a valid name."; } else{ $name = $input_name; }
// Validate address $input_address = trim($_POST["address"]); if(empty($input_address)){ $address_err = "Please enter an address."; } else{ $address = $input_address; }
// Validate salary $input_salary = trim($_POST["salary"]); if(empty($input_salary)){ $salary_err = "Please enter the salary amount."; } elseif(!ctype_digit($input_salary)){ $salary_err = "Please enter a positive integer value."; } else{ $salary = $input_salary; }
// Check input errors before inserting in database if(empty($name_err) && empty($address_err) && empty($salary_err)){ // Prepare an insert statement $sql = "INSERT INTO employees (name, address, salary) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){ // Bind variables to the prepared statement as parameters $stmt->bind_param("sss", $param_name, $param_address, $param_salary);
// Attempt to execute the prepared statement if($stmt->execute()){ // Records created successfully. Redirect to landing page header("location: index.php"); exit(); } else{ echo "Oops! Something went wrong. Please try again later."; } }
Now we will focus on the Read functionality of our CRUD application.
Let’s create a file named read.php and put the following code inside it. It will simply retrieve the records from the employees table based the id attribute of the employee.
<?php // Check existence of id parameter before processing further if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){ // Include config file require_once "config.php";
// Prepare a select statement $sql = "SELECT * FROM employees WHERE id = ?";
if($stmt = $mysqli->prepare($sql)){ // Bind variables to the prepared statement as parameters $stmt->bind_param("i", $param_id);
// Set parameters $param_id = trim($_GET["id"]);
// Attempt to execute the prepared statement if($stmt->execute()){ $result = $stmt->get_result();
if($result->num_rows == 1){ /* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */ $row = $result->fetch_array(MYSQLI_ASSOC);
// Retrieve individual field value $name = $row["name"]; $address = $row["address"]; $salary = $row["salary"]; } else{ // URL doesn't contain valid id parameter. Redirect to error page header("location: error.php"); exit(); }
} else{ echo "Oops! Something went wrong. Please try again later."; } }
// Close statement $stmt->close();
// Close connection $mysqli->close(); } else{ // URL doesn't contain id parameter. Redirect to error page header("location: error.php"); exit(); } ?>
Now we can build the Update functionality of our CRUD application.
Let’s create a file named update.php and put the following code inside it. It will update the existing records in the employees table based the id attribute of the employee.
<?php // Include config file require_once "config.php";
// Attempt to execute the prepared statement if($stmt->execute()){ // Records updated successfully. Redirect to landing page header("location: index.php"); exit(); } else{ echo "Oops! Something went wrong. Please try again later."; } }
// Close statement $stmt->close(); }
// Close connection $mysqli->close(); } else{ // Check existence of id parameter before processing further if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){ // Get URL parameter $id = trim($_GET["id"]);
// Prepare a select statement $sql = "SELECT * FROM employees WHERE id = ?"; if($stmt = $mysqli->prepare($sql)){ // Bind variables to the prepared statement as parameters $stmt->bind_param("i", $param_id);
// Set parameters $param_id = $id;
// Attempt to execute the prepared statement if($stmt->execute()){ $result = $stmt->get_result();
if($result->num_rows == 1){ /* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */ $row = $result->fetch_array(MYSQLI_ASSOC);
// Retrieve individual field value $name = $row["name"]; $address = $row["address"]; $salary = $row["salary"]; } else{ // URL doesn't contain valid id. Redirect to error page header("location: error.php"); exit(); }
} else{ echo "Oops! Something went wrong. Please try again later."; } }
// Close statement $stmt->close();
// Close connection $mysqli->close(); } else{ // URL doesn't contain id parameter. Redirect to error page header("location: error.php"); exit(); } } ?>
Finally, now we will build the Delete functionality of our CRUD application.
Let’s create a file named delete.php and put the following code inside it. It will delete the existing records from the employees table based the id attribute of the employee.
<?php // Process delete operation after confirmation if(isset($_POST["id"]) && !empty($_POST["id"])){ // Include config file require_once "config.php";
// Prepare a delete statement $sql = "DELETE FROM employees WHERE id = ?";
if($stmt = $mysqli->prepare($sql)){ // Bind variables to the prepared statement as parameters $stmt->bind_param("i", $param_id);
// Set parameters $param_id = trim($_POST["id"]);
// Attempt to execute the prepared statement if($stmt->execute()){ // Records deleted successfully. Redirect to landing page header("location: index.php"); exit(); } else{ echo "Oops! Something went wrong. Please try again later."; } }
// Close statement $stmt->close();
// Close connection $mysqli->close(); } else{ // Check existence of id parameter if(empty(trim($_GET["id"]))){ // URL doesn't contain id parameter. Redirect to error page header("location: error.php"); exit(); } } ?>
Let’s create one more file “error.php”. This page will be displayed if request is invalid i.e. if id parameter is missing from the URL query string or it is not valid.
I am a software developer specializing in creating scalable software solutions and providing IT consulting services. Feel free to reach out to me at dharshithasrimal@gmail.com.
