Creating CRUD Pages with PHP and MySQL: A Beginner's Guide

Creating a CRUD (Create, Read, Update, Delete) page using PHP and MySQL can seem like a daunting task for beginners. But fear not! In this blog post, we'll walk you through the basics of setting up a simple CRUD application step by step.

Prerequisites

Before we begin, make sure you have the following tools installed:

A text editor or IDE (e.g., Sublime Text, Visual Studio Code, or XAMPP)
PHP and MySQL installed on your local machine
Basic knowledge of HTML, CSS, and JavaScript

Setting Up the Database

First things first, let's create a new database and table to store our data.

Open your preferred MySQL client (e.g., phpMyAdmin) and create a new database named crud_example.
Inside the new database, create a new table called users with the following columns: id, name, email, and age. Set the data types as shown below:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  age INT NOT NULL
);

Creating the CRUD Pages

Now we'll create the HTML forms and PHP scripts to handle CRUD operations.

Create a new user

Create an index.php file with the following content:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Create New User</title>
</head>
<body>
  <!-- Your HTML form for creating a new user goes here -->
</body>
</html>

Inside the body tags, add an HTML form to create a new user.

Next, create a create.php file with the following content:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "crud_example";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Add code here to insert data into the database

$conn->close();
?>

Reading users

Next, let's create a page to display all of the users in our database. Create an all_users.php file with the following content:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>All Users</title>
</head>
<body>
  <!-- Your PHP code to display all users goes here -->
</body>
</html>

Inside the body tags, add the necessary PHP code to query the database and display the results.

Updating a user

Create an update.php file with the following content:

<?php
// Your SQL query to fetch a single user goes here

// Add code here to update data in the database

$conn->close();
?>

Deleting a user

Create a delete.php file with the following content:

<?php
// Your SQL query to delete a user goes here

$conn->close();
?>

Conclusion

Congratulations! You've created a simple CRUD application using PHP and MySQL. From this foundation, you can expand your knowledge by adding more features like input validation, error handling, and user authentication. Happy coding!

Published January, 2015