How to Make Simple CRUD in PHP and MySQL

Key Takeaways

• The four fundamental operations—Create, Read, Update, and Delete—are the core of how you manage data in any web application.
• Using prepared statements with PDO is the best practice to prevent serious vulnerabilities like SQL injection, ensuring your application is safe.

Every modern web application starts with a simple function: the ability to manage data. This is where CRUD comes in. It’s an acronym for the four essential database operations: Create, Read, Update, and Delete. Think of a simple to-do list app:

• You Create a new task.
• You Read your list of all tasks.
• You Update a task when it’s done.
• You Delete a task when you no longer need it.

In this tutorial, I’ll walk you through how to build a simple guest management application using PHP.

First, I’ll show you how to build the application on your local machine using XAMPP, PHP, and MySQL, focusing on best practices like prepared statements to protect against SQL injection.

Once our app is working, I’ll then guide you on how to deploy it from your local environment to a live Cloudways server, making it accessible to everyone.

How to Build a CRUD App in PHP on a Local Environment

Now that you understand what CRUD operations are, let’s build a simple CRUD application to bring those concepts to life.

We’ll use XAMPP to set up a local Apache + MySQL + PHP environment, create a database, and build a basic app that lets users create, read, update, and delete records from that database.

Prerequisites: What You’ll Need

Before we get started, make sure you have the following set up on your computer:

• A local web server environment: Something like XAMPP, WAMP, or MAMP that includes Apache, PHP, and MySQL. This will allow you to run your PHP files and have a local database.
• A code editor: Any text editor will do, like VS Code, Sublime Text, or Notepad++.
• A web browser: Chrome, Firefox, or any other modern browser.

Step 1: Setting Up the Database

First, let’s create the database and table we’ll use. You have two options for doing this: using the graphical interface of phpMyAdmin or using the MySQL command-line shell.

Option 1: Using phpMyAdmin (Graphical Interface)

This is a great option for beginners who prefer a visual approach.

• Open your web server’s control panel (like XAMPP) and start the Apache and MySQL modules.

• Open your browser and go to http://localhost/phpmyadmin to access the database tool.
• On the left, click “New” to create a new database. Name it crud_app.
• Once the database is created, the right panel will prompt you to create a new table. Name it guests and specify 6 columns.
• Define the columns with the following properties:
  • id: INT, Length: 11, check the A_I (Auto Increment) box, which will automatically set it as the Primary key.
  • firstname: VARCHAR, Length: 50
  • lastname: VARCHAR, Length: 50
  • email: VARCHAR, Length: 100
  • subject: VARCHAR, Length: 255
• Click “Save” to create the table.

Option 2: Using the MySQL Shell (Command Line)

This method is faster for developers comfortable with a command-line interface. Let’s go with this approach.

First, open your web server’s control panel (like XAMPP) and start the Apache and MySQL modules. Next, click the “Shell” button next to the MySQL module.

This will open a command-line window for you.

To get into the MySQL environment, you first need to navigate to the correct directory and then launch the shell.

• First, change your current directory to the location where the MySQL executable is stored by typing cd mysql\bin and pressing Enter.
• Once you’re in the bin directory, you can launch the MySQL shell. Since XAMPP’s default MySQL installation has a root user with no password, the command is simple: mysql -u root -p. When prompted for a password, just press Enter.
• You will see the prompt change to MariaDB [(none)]>, which means you’re now in the correct environment to run SQL commands.

Now you can create your database and table directly from the command line.

• Create the database: Type CREATE DATABASE crud_app; and press Enter.
• Switch to the new database: Type USE crud_app; and press Enter.
• Create the guests table: Type the following command and press Enter:

SQL

CREATE TABLE guests (

id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

firstname VARCHAR(50),

lastname VARCHAR(50),

email VARCHAR(100),

subject VARCHAR(255)

);

After running the CREATE TABLE command, you’ll receive a confirmation message. This means your table has been successfully created.

After either of these methods, your database will be ready. You can verify the table’s existence and structure by navigating to http://localhost/phpmyadmin and inspecting the crud_app database.

Step 2: Creating the Database Connection File

Now, let’s write the PHP code to connect to our database. This is a crucial step, and we’ll do it in a separate file so we can easily reuse it.

• In your web server’s root directory (e.g., C:\xampp\htdocs), create a new folder called crud_tutorial.

• Inside this folder, create a new file named db.php and add the following code:

<?php

// Database credentials

$host = 'localhost';

$dbname = 'crud_app';

$username = 'root'; // Default username for XAMPP

$password = '';     // Default password for XAMPP




try {

// Create a new PDO instance (connect to the database)

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);




// Set the PDO error mode to exception for better error handling

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);




} catch (PDOException $e) {

// If connection fails, catch the exception and display a user-friendly error

die("Connection failed: " . $e->getMessage());

}

?>

This file sets up a secure and reliable connection to our crud_app database using PHP Data Objects (PDO), which is the recommended method. The try…catch block will help us manage any potential connection errors.

You can also check out my guide on how to connect a MySQL database to PHP using MySQLi and PDO for detailed steps.

Step 3: Creating the Main CRUD Operations File

Now that you have your database connection file, let’s create the central file that will contain our application’s logic and user interface. This single file will handle all the CRUD operations for simplicity in this tutorial.

The Create operation is the first part of CRUD. It involves using an HTML form to get user input and then processing that input with PHP to insert it into the database.

Add the following code to your index.php file, right after the require_once ‘db.php’; line. This block of PHP code will handle the form submission. It’s designed to be secure by using prepared statements to prevent SQL injection.

<?php

// Include the database connection file

require_once 'db.php';




// Check if the form has been submitted via POST

if ($_SERVER["REQUEST_METHOD"] == "POST") {

// Sanitize user inputs to prevent any potential risks

$firstname = filter_var($_POST['firstname'], FILTER_SANITIZE_STRING);

$lastname = filter_var($_POST['lastname'], FILTER_SANITIZE_STRING);

$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL);

$subject = filter_var($_POST['subject'], FILTER_SANITIZE_STRING);




// Ensure all required fields are not empty

if (!empty($firstname) && !empty($lastname) && !empty($email)) {

try {

// SQL query with placeholders (?) to prevent SQL injection

$sql = "INSERT INTO guests (firstname, lastname, email, subject) VALUES (?, ?, ?, ?)";

// Prepare the statement

$stmt = $pdo->prepare($sql);

// Bind the parameters and execute the statement

$stmt->execute([$firstname, $lastname, $email, $subject]);




// Redirect back to index.php to prevent form resubmission

header("Location: index.php");

exit();

} catch (PDOException $e) {

echo "Error: " . $e->getMessage();

}

} else {

echo "Please fill in all the required fields.";

}

}

?>

Below the PHP code you just added, add the following HTML form. This is what the user will see in their browser to enter new guest information.

<form method="post" action="index.php">

<h3>Add a New Guest</h3>

<input type="text" name="firstname" placeholder="First Name" required>

<br><br>

<input type="text" name="lastname" placeholder="Last Name" required>

<br><br>

<input type="email" name="email" placeholder="Email" required>

<br><br>

<textarea name="subject" placeholder="Subject"></textarea>

<br><br>

<button type="submit">Add Guest</button>

</form>

This is our complete update file code:

Now, open your web browser and navigate to http://localhost/crud_tutorial/index.php. You should see the form.

Fill in the fields and click “Add Guest“. The page will reload, and the data will be added to your database. You can check phpMyAdmin to confirm the entry. As you can see, the new entry we made has been added to our database.

Let’s make a few more entries before we move to the next step.

Step 4: The Read Operation (Displaying All Guests)

Now that we can add guests, let’s display them in a table. The Read operation is all about retrieving data from the database.

Add the following HTML and PHP code to your index.php file, right below the </form> tag from the previous step.

<h2>Guest List</h2>

<table>

<thead>

<tr>

<th>ID</th>

<th>First Name</th>

<th>Last Name</th>

<th>Email</th>

<th>Subject</th>

<th>Actions</th>

</tr>

</thead>

<tbody>

<?php

// Read all guests from the database

$stmt = $pdo->query("SELECT id, firstname, lastname, email, subject FROM guests ORDER BY id DESC");

// Loop through the results and display each guest

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

echo "<tr>";

echo "<td>" . htmlspecialchars($row['id']) . "</td>";

echo "<td>" . htmlspecialchars($row['firstname']) . "</td>";

echo "<td>" . htmlspecialchars($row['lastname']) . "</td>";

echo "<td>" . htmlspecialchars($row['email']) . "</td>";

echo "<td>" . htmlspecialchars($row['subject']) . "</td>";

echo "<td><a href='edit.php?id=" . $row['id'] . "'>Edit</a> | <a href='delete.php?id=" . $row['id'] . "'>Delete</a></td>";

echo "</tr>";

}

?>

</tbody>

</table>

Save the file and refresh your browser at http://localhost/crud_tutorial/index.php. You should now see all the guest data you added displayed in a neat table.

Explanation: The PHP code uses $pdo->query() to execute a SELECT statement that pulls all guest records from the database. It then uses a while loop to iterate through each row returned by the query, building an HTML table row (<tr>) for every guest.

The htmlspecialchars() function is used for security, escaping any special characters in the data to prevent cross-site scripting (XSS) vulnerabilities. This step provides the user with a complete view of all the data in the database.

Step 5: The Delete Operation

Now that you can create and read records, let’s implement the Delete operation. This will allow you to remove a guest from the database. We’ll use a separate file to handle the deletion logic.

Create a new file named delete.php in your crud_tutorial folder.

Add the following code to delete.php. This script will safely receive the id of the guest to be deleted and execute a prepared statement to remove the corresponding record.

<?php

// Include the database connection file

require_once 'db.php';




// Check if an 'id' was passed in the URL

if (isset($_GET['id'])) {

// Sanitize the ID to ensure it's an integer

$id = filter_var($_GET['id'], FILTER_SANITIZE_NUMBER_INT);




try {

// Use a prepared statement to safely delete the record

$sql = "DELETE FROM guests WHERE id = ?";

$stmt = $pdo->prepare($sql);

$stmt->execute([$id]);




// Redirect back to the main page after successful deletion

header("Location: index.php");

exit();




} catch (PDOException $e) {

echo "Error: " . $e->getMessage();

}

} else {

// If no ID is provided, redirect back to the main page

header("Location: index.php");

exit();

}

?>

After creating and saving the delete.php file, go back to your web browser and visit http://localhost/crud_tutorial/index.php.

To test it, simply click on the “Delete” link next to any guest record in your guest list table.

Clicking the link will trigger the delete.php script, which will remove that guest’s record from the database and then redirect you back to the index.php page.

I deleted Muneeb Khan from the table and as you can see, that the guest is no longer in the list, confirming the delete operation was successful.

Explanation: This script grabs the guest’s id from the URL. It uses a prepared statement with a placeholder (?) to ensure the deletion is secure and safe from SQL injection. After the query is executed, it redirects back to the main index.php page, so you see the updated guest list immediately.

Step 6: The Update Operation (In-depth)

The Update operation is the most complex one as it requires two steps: getting the data of the guest to be edited and then saving the changes.

• Create a new file called edit.php in the crud_tutorial folder.
• Add the following code to edit.php.

<?php

// Include the database connection file

require_once 'db.php';




// Initialize a variable to hold guest data

$guest = null;




// --- Part 1: Fetch the guest's data for editing ---

if (isset($_GET['id'])) {

// Sanitize the ID to ensure it's a valid integer

$id = filter_var($_GET['id'], FILTER_SANITIZE_NUMBER_INT);




try {

// Prepare and execute a SELECT statement to get the guest's data

$sql = "SELECT * FROM guests WHERE id = ?";

$stmt = $pdo->prepare($sql);

$stmt->execute([$id]);

$guest = $stmt->fetch(PDO::FETCH_ASSOC);




// If no guest is found with that ID, redirect back to the main page

if (!$guest) {

header("Location: index.php");

exit();

}

} catch (PDOException $e) {

echo "Error: " . $e->getMessage();

}

}




// --- Part 2: Handle the form submission (updating the data) ---

if ($_SERVER["REQUEST_METHOD"] == "POST") {

// Sanitize and get the data from the form

$id = filter_var($_POST['id'], FILTER_SANITIZE_NUMBER_INT);

$firstname = filter_var($_POST['firstname'], FILTER_SANITIZE_STRING);

$lastname = filter_var($_POST['lastname'], FILTER_SANITIZE_STRING);

$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL);

$subject = filter_var($_POST['subject'], FILTER_SANITIZE_STRING);




try {

// Prepare and execute an UPDATE statement

$sql = "UPDATE guests SET firstname=?, lastname=?, email=?, subject=? WHERE id=?";

$stmt = $pdo->prepare($sql);

$stmt->execute([$firstname, $lastname, $email, $subject, $id]);




// Redirect to the main page after a successful update

header("Location: index.php");

exit();

} catch (PDOException $e) {

echo "Error: " . $e->getMessage();

}

}

?>




<h2>Edit Guest Information</h2>

<?php if ($guest): ?>

<form method="post" action="edit.php">

<input type="hidden" name="id" value="<?= htmlspecialchars($guest['id']); ?>">

<label for="firstname">First Name:</label>

<br>

<input type="text" name="firstname" value="<?= htmlspecialchars($guest['firstname']); ?>" required>

<br><br>




<label for="lastname">Last Name:</label>

<br>

<input type="text" name="lastname" value="<?= htmlspecialchars($guest['lastname']); ?>" required>

<br><br>




<label for="email">Email:</label>

<br>

<input type="email" name="email" value="<?= htmlspecialchars($guest['email']); ?>" required>

<br><br>




<label for="subject">Subject:</label>

<br>

<textarea name="subject"><?= htmlspecialchars($guest['subject']); ?></textarea>

<br><br>




<button type="submit">Update Guest</button>

</form>

<?php endif; ?>

Save the file. You can test this by going to http://localhost/crud_tutorial/index.php and clicking the “Edit” link next to any guest. The edit.php page will load with that guest’s information pre-filled in the form, allowing you to make changes and submit them.

Congratulations! You’ve just created a secure and functional CRUD application using PHP and MySQL.

Move Your CRUD App From Local Environment to a Live Server

So far, all the hard work you’ve put into building your CRUD app has been happening on your computer’s local server (XAMPP). This is fantastic for development and testing, but in a real-world scenario, you’d want to move your application to a live server so it’s accessible to everyone.

If you’re a Cloudways customer, this process is incredibly streamlined. Your core PHP and database code won’t change; we just need to get it set up and connected on your live Cloudways environment.

We’ll handle three main tasks: getting your live database ready, updating your database connection file, and uploading all your project files.

Let’s get started.

Step 1: Deploying Your Server and Application

• Log in to your Cloudways account using your credentials.

 

• From your main dashboard, click on “Servers” in the top navigation bar. If you don’t have a server yet, you’ll be prompted to launch your first one. Since I already have a few servers, I’ll click on the + Add Server option to create a new server.

• Now select your application from the list. For this tutorial, choose “Custom App”. And give your app and server a name.
• Next, choose a cloud provider (e.g., DigitalOcean, AWS, Google Cloud).

• Choose your server size and location based on your needs. For this tutorial, a basic server size is more than enough.

• Finally, review the estimated cost and click “Launch Now”.

Cloudways will now begin provisioning your server and deploying your application. This process typically takes a few minutes.

Step 2: Exporting Your Database from Localhost

After your server is running, the first task is to set up your database. Since you’ve been working locally, your database is in your local phpMyAdmin.

The easiest way to move your database to your live server is by exporting it as an SQL file and then importing it into your live server’s database. This saves you from having to manually recreate the tables.

• Open your browser and navigate to http://localhost/phpmyadmin.

• Click on your crud_app database in the left-hand menu.
• Click the “Export” tab at the top.
• Make sure the format for the file you wanna download is SQL file (e.g., crud_app.sql) and the click on Export.
• Now the database will download on your computer.

Step 3: Importing Your Database to Cloudways

Now, let’s get that database file into your live Cloudways environment. You should already have a Cloudways server and a PHP application running.

• In your Cloudways dashboard, navigate to your PHP application.
• Go to “Access Details” and click “Launch Database Manager“. This will open a web-based database management tool.

• Click the “Import” tab.

• Click “Choose files” and select the crud_app.sql file you just exported from your computer.

• Click “Execute” to start the import. The tool will automatically create your guests table on the live server.

Step 4: Updating Your Database Connection File (db.php)

Your PHP files are currently configured to connect to your local XAMPP database. To make them work on your live server, you need to change the database credentials.

• Go back to your Cloudways application’s “Access Details” page and find the “Database” section.
• You’ll see DB Name, Username, and Password. These are the new credentials you’ll use. You’ll also need to update Host, which is your application URL.

• Open your db.php file in your code editor. This file is located inside your crud_tutorial folder on your computer.
• Replace the existing credentials with the new ones from Cloudways. Be careful to copy them exactly as they appear.

Your db.php file should now look something like this:

<?php

// Database credentials for the live server

$host = 'your_db_host';     // Example: 'mysql-12345-0.cloudwaysapps.com'

$dbname = 'your_db_name';   // Example: 'u_application_db'

$username = 'your_db_user'; // Example: 'a12345'

$password = 'your_db_pass'; // Example: 'your_strong_password'




try {

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

die("Connection failed: " . $e->getMessage());

}

?>

Here is my file:

Step 5: Uploading Your Files to the Live Server

This is the final step! You’ll use an FTP/SFTP client to move your entire project from your computer to the Cloudways server. I’m talking about our “crud_tutorial” project folder we created earlier.

We’ll use FileZilla for this:

• From your Cloudways dashboard, go to the server on which your PHP app is hosted. There you’ll find the Master Credentials to login to your server.

• Use an FTP client (like FileZilla) to connect to your server.

• On the server side (the right panel in FileZilla), navigate to the public_html directory.
• On your local computer (the left panel), find your crud_tutorial folder.
• Upload the entire crud_tutorial folder to the public_html directory on the server. There might already be an index.php file there; you can ignore it.

That’s it! Once the upload is complete, your application will be live. You can access it by opening your browser and visiting http://your_application_url.com/crud_tutorial/index.php. In my case the URL will be: https://phpstack-1522928-5860660.cloudwaysapps.com/crud_tutorial/index.php.

As you can see, our project and database has been imported to our Cloudways live server.

To test, I’ll add a test user to the form and it should not only reflect on our app, but also in our database that we imported to Cloudways.

As you can see, everything is working properly on our live server. You can now perform all CRUD operations on the live server, and they will all work as expected.

Wrapping Up!

There you have it! You’ve successfully built a secure and functional CRUD application and, most importantly, you’ve deployed it from a local environment to a live Cloudways server.

In this tutorial, we’ve not only covered the fundamental concepts of CRUD but have also learned the best practices for handling database interactions with prepared statements. By using Cloudways, you’ve seen how simple it is to move a project from development to a live, professional environment.

The key to making your PHP app shine is to choose a reliable hosting platform, and Cloudways PHP hosting checks all those boxes as our optimized stack, featuring Nginx, Apache, and advanced caching, is built for high performance.