/ php / By

Step-by-Step Tutorial: PHP CRUD Application to Insert and Fetch Data

create a step-by-step tutorial for building a PHP application that inserts data into a MySQL database using a form and fetches data from it. The example will include a simple form to collect user information (name and email) and display the stored data in a table

Step 1: Set Up the Environment

  • Requirements: Ensure you have PHP, MySQL, and a web server (e.g., Apache) installed. You can use XAMPP, WAMP, or a similar stack for local development.
  • Database Setup: Create a MySQL database and a table to store user data.

Step 2: Create the Database and Table

  1. Open your MySQL client (e.g., phpMyAdmin or MySQL command line).
  2. Create a database named user_db.
  3. Create a table named users with columns for id, name, and email.

Step 3: Create the Project Structure

  • Create a project folder (e.g., php_crud) in your web server’s root directory (e.g., htdocs for XAMPP).
  • Inside the folder, create the following files:
  • index.php: Main file with the form and data display.
  • db_connect.php: Database connection script.
  • insert.php: Script to handle form submission and insert data.
  • fetch.php: Script to fetch and display data.

Step 4: Write the Database Connection Script

Create db_connect.php to establish a connection to the MySQL database.

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

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Step 5: Create the Form and Display Page

Create index.php with a form to collect user input and a table to display data. Include Bootstrap for styling.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP CRUD Example</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2>PHP CRUD: Insert and Fetch Data</h2>

        <!-- Form to Insert Data -->
        <form action="insert.php" method="POST" class="mb-4">
            <div class="mb-3">
                <label for="name" class="form-label">Name</label>
                <input type="text" class="form-control" id="name" name="name" required>
            </div>
            <div class="mb-3">
                <label for="email" class="form-label">Email</label>
                <input type="email" class="form-control" id="email" name="email" required>
            </div>
            <button type="submit" class="btn btn-primary">Submit</button>
        </form>

        <!-- Display Data -->
        <h3>Users List</h3>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                </tr>
            </thead>
            <tbody>
                <?php include 'fetch.php'; ?>
            </tbody>
        </table>
    </div>
</body>
</html>

Step 6: Handle Form Submission

Create insert.php to process form data and insert it into the database.

<?php
include 'db_connect.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = $_POST['name'];
    $email = $_POST['email'];

    try {
        $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        header("Location: index.php?success=Data inserted successfully");
    } catch(PDOException $e) {
        header("Location: index.php?error=" . $e->getMessage());
    }
}
?>

Step 7: Fetch and Display Data

Create fetch.php to retrieve and display data from the database.

<?php
include 'db_connect.php';

try {
    $sql = "SELECT * FROM users";
    $stmt = $conn->query($sql);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['id']) . "</td>";
        echo "<td>" . htmlspecialchars($row['name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['email']) . "</td>";
        echo "</tr>";
    }
} catch(PDOException $e) {
    echo "<tr><td colspan='3'>Error: " . $e->getMessage() . "</td></tr>";
}
?>

Step 8: Create the Database Table

Run the following SQL to create the users table in your user_db database:

CREATE DATABASE IF NOT EXISTS user_db;
USE user_db;
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Step 9: Test the Application

  1. Start your web server and MySQL (e.g., via XAMPP).
  2. Place the project files in the web server’s root directory (e.g., htdocs/php_crud).
  3. Run the SQL from Step 8 in your MySQL client to set up the database and table.
  4. Access index.php in your browser (e.g., http://localhost/php_crud/index.php).
  5. Enter a name and email in the form and submit.
  6. Verify that the data appears in the table below the form.
  7. Check for success or error messages in the URL (e.g., ?success=Data inserted successfully).

Step 10: Security Considerations

  • Input Validation: The example uses htmlspecialchars to prevent XSS when displaying data. Add further validation (e.g., email format, string length) in insert.php for production use.
  • Prepared Statements: The code uses PDO prepared statements to prevent SQL injection.
  • Error Handling: Basic error handling is included. Enhance it for production by logging errors securely.
  • Database Credentials: In a production environment, store credentials securely (e.g., in environment variables).

Related Posts

Scroll to Top