How to Create a Database and Perform CRUD Operations Using PHP

by

In web development, managing data is crucial to building dynamic and interactive websites. Whether you’re creating a contact form, user registration system, or content management platform, a database is the backbone that stores and organizes your data. This article will guide you through creating a database and performing CRUD (Create, Read, Update, Delete) operations using PHP.

What is a Database?

A database is an organized collection of data stored electronically. It allows developers to store, retrieve, and manipulate data efficiently. In this article, we’ll create and manage our database using MySQL, a popular relational database management system.

What is PHP?

PHP (Hypertext Preprocessor) is a server-side scripting language widely used for web development. It excels in interacting with databases to build dynamic web applications.

Step 1: Setting Up the Database

First, create a database to store the information. This example involves ID, name, email, contact, country, company, and explanation.

SQL Commands to Create the Database and Table:

CREATE DATABASE my_database;

USE my_database;

CREATE TABLE records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    contact VARCHAR(15),
    country VARCHAR(50),
    company VARCHAR(100),
    explanation TEXT
);

Step 2: Creating the HTML Form

Create an HTML form to interact with the database. This form allows users to insert, update, and delete records.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Operations</title>
</head>
<body>
    <h2>Manage Records</h2>
    <form action="process.php" method="post">
        <label for="id">ID (for Update/Delete):</label>
        <input type="text" name="id" id="id"><br><br>

        <label for="name">Name:</label>
        <input type="text" name="name" id="name" required><br><br>

        <label for="email">Email:</label>
        <input type="email" name="email" id="email" required><br><br>

        <label for="contact">Contact:</label>
        <input type="text" name="contact" id="contact" required><br><br>

        <label for="country">Country:</label>
        <input type="text" name="country" id="country" required><br><br>

        <label for="company">Company:</label>
        <input type="text" name="company" id="company" required><br><br>

        <label for="explanation">Explanation:</label>
        <textarea name="explanation" id="explanation" required></textarea><br><br>

        <button type="submit" name="action" value="insert">Insert</button>
        <button type="submit" name="action" value="update">Update</button>
        <button type="submit" name="action" value="delete">Delete</button>
    </form>
</body>
</html>

Step 3: Writing the PHP Script

Use PHP to handle the form submissions and perform operations on the database.

PHP Script (process.php):

<?php
// Database connection
$host = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

$conn = new mysqli($host, $username, $password, $dbname);

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

// Get form inputs
$id = $_POST['id'] ?? null;
$name = $_POST['name'] ?? '';
$email = $_POST['email'] ?? '';
$contact = $_POST['contact'] ?? '';
$country = $_POST['country'] ?? '';
$company = $_POST['company'] ?? '';
$explanation = $_POST['explanation'] ?? '';
$action = $_POST['action'] ?? '';

// Handle different actions
if ($action === 'insert') {
    $sql = "INSERT INTO records (name, email, contact, country, company, explanation)
            VALUES ('$name', '$email', '$contact', '$country', '$company', '$explanation')";
} elseif ($action === 'update' && $id) {
    $sql = "UPDATE records
            SET name='$name', email='$email', contact='$contact', country='$country', company='$company', explanation='$explanation'
            WHERE id=$id";
} elseif ($action === 'delete' && $id) {
    $sql = "DELETE FROM records WHERE id=$id";
} else {
    die("Invalid action or missing ID for update/delete.");
}

// Execute the query
if ($conn->query($sql) === TRUE) {
    echo "Operation successful!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Step 4: Viewing the Records

To view the database records, create a separate PHP script.

PHP Script (view.php):

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

$conn = new mysqli($host, $username, $password, $dbname);

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

$sql = "SELECT * FROM records";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'>";
    echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Contact</th><th>Country</th><th>Company</th><th>Explanation</th></tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr>
                <td>{$row['id']}</td>
                <td>{$row['name']}</td>
                <td>{$row['email']}</td>
                <td>{$row['contact']}</td>
                <td>{$row['country']}</td>
                <td>{$row['company']}</td>
                <td>{$row['explanation']}</td>
              </tr>";
    }
    echo "</table>";
} else {
    echo "No records found.";
}

$conn->close();
?>

Conclusion

This guide teaches you how to create a database and perform PHP CRUD operations. This system can be extended further with features like validation, authentication, and pagination. It serves as a strong foundation for building dynamic, database-driven web applications.

Related Posts:

Leave a Comment