Performing CRUD Operations in PHP with MySQL

What is CRUD?

CRUD stands for:

Create – Add data to the database
Read – Retrieve data from the database
Update – Modify existing data
Delete – Remove data from the database

These four operations cover all you need to build any database-driven application.

Setting Up Your Environment

To run PHP with MySQL, install:

XAMPP (or WAMP, LAMP)
A code editor (VS Code recommended)
A web browser

Start Apache and MySQL from the XAMPP Control Panel.

Create a MySQL Table

Use phpMyAdmin or this SQL query:

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PHP MySQL Connection

MySQLi (Object-Oriented)

$conn = new mysqli("localhost", "root", "", "testdb");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

PDO

try {
    $pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

CREATE: Insert Data into MySQL

MySQLi

$name = "John Doe";
$email = "john@example.com";

$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
$conn->query($sql);

PDO (Secure with Prepared Statements)

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["John Doe", "john@example.com"]);

READ: Fetch Data from MySQL

MySQLi

$result = $conn->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . " - " . $row['email'] . "
";
}

PDO

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['name'] . " - " . $row['email'] . "
";
}

UPDATE: Edit Existing Data

MySQLi

$sql = "UPDATE users SET name='Jane Doe' WHERE id=1";
$conn->query($sql);

PDO

$stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->execute(["Jane Doe", 1]);

DELETE: Remove Data from MySQL

MySQLi

$sql = "DELETE FROM users WHERE id=1";
$conn->query($sql);

PDO

$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);

Full PHP CRUD Example Using MySQLi

Create a simple index.php:

<?php
$conn = new mysqli("localhost", "root", "", "testdb");

if (isset($_POST['submit'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $conn->query("INSERT INTO users (name, email) VALUES ('$name', '$email')");
}

$result = $conn->query("SELECT * FROM users");
?>

<form method="POST">
  Name: <input type="text" name="name" required>
  Email: <input type="email" name="email" required>
  <button name="submit">Add User</button>
</form>

<h2>Users List</h2>
<?php while($row = $result->fetch_assoc()): ?>
  <p><?= $row['name'] ?> - <?= $row['email'] ?></p>
<?php endwhile; ?>

PDO Version: Full CRUD in PHP

<?php
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute([$_POST['name'], $_POST['email']]);
}

$stmt = $pdo->query("SELECT * FROM users");
?>

<form method="POST">
  Name: <input type="text" name="name">
  Email: <input type="email" name="email">
  <button type="submit">Submit</button>
</form>

<h2>Users List</h2>
<?php while ($row = $stmt->fetch(PDO::FETCH_ASSOC)): ?>
  <p><?= $row['name'] ?> - <?= $row['email'] ?></p>
<?php endwhile; ?>

Security Tips for CRUD in PHP

Always use prepared statements to avoid SQL injection
Validate and sanitize all user inputs
Limit data shown to users
Hide error messages from public
Use HTTPS and secure database credentials

Common PHP CRUD Errors & Fixes

Error	Cause	Fix
Undefined index	Form not submitted	Use isset() before access
Connection failed	Wrong DB credentials	Double-check host/user/pass
SQL syntax error	Quotes or spacing	Use prepared statements
No data shown	Empty table	Insert records first