PhpHow-ToBeginner · 4 min read

How to Use CRUD Operations in PHP: Simple Guide with Examples

In PHP, CRUD operations are done by connecting to a database and using SQL queries inside mysqli or PDO functions. You perform Create with INSERT, Read with SELECT, Update with UPDATE, and Delete with DELETE SQL commands.

📐

Syntax

CRUD operations in PHP use SQL commands inside PHP code to interact with a database. You first connect to the database, then run queries for each operation:

Create: Use INSERT INTO to add new data.
Read: Use SELECT to get data.
Update: Use UPDATE to change existing data.
Delete: Use DELETE FROM to remove data.

Each query runs with PHP functions like mysqli_query() or PDO::query().

php

<?php
// Connect to MySQL database
$conn = new mysqli('localhost', 'username', 'password', 'database');

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

// Create
$sql_create = "INSERT INTO tablename (column1, column2) VALUES ('value1', 'value2')";
$conn->query($sql_create);

// Read
$sql_read = "SELECT * FROM tablename";
$result = $conn->query($sql_read);

// Update
$sql_update = "UPDATE tablename SET column1='newvalue' WHERE id=1";
$conn->query($sql_update);

// Delete
$sql_delete = "DELETE FROM tablename WHERE id=1";
$conn->query($sql_delete);

$conn->close();
?>

💻

Example

This example shows a full PHP script that connects to a MySQL database and performs all four CRUD operations on a simple users table.

php

<?php
// Database connection
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'testdb';

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

// Create: Insert a new user
$sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully\n";
} else {
    echo "Error: " . $conn->error . "\n";
}

// Read: Select all users
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "\n";
    }
} else {
    echo "0 results\n";
}

// Update: Change user's email
$sql = "UPDATE users SET email='alice_new@example.com' WHERE name='Alice'";
if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully\n";
} else {
    echo "Error updating record: " . $conn->error . "\n";
}

// Delete: Remove user
$sql = "DELETE FROM users WHERE name='Alice'";
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully\n";
} else {
    echo "Error deleting record: " . $conn->error . "\n";
}

$conn->close();
?>

Output

New record created successfully id: 1 - Name: Alice - Email: alice@example.com Record updated successfully Record deleted successfully

⚠️

Common Pitfalls

Common mistakes when using CRUD in PHP include:

Not checking if the database connection succeeded.
Not escaping user input, which can cause SQL injection.
Forgetting to close the database connection.
Using wrong SQL syntax or table/column names.
Not handling query errors properly.

Always validate and sanitize inputs and check for errors after queries.

php

<?php
// Wrong: No error check and unsafe input
$conn = new mysqli('localhost', 'user', 'pass', 'db');
$name = $_GET['name'];
$sql = "INSERT INTO users (name) VALUES ('$name')"; // Unsafe!
$conn->query($sql);

// Right: Use prepared statements to avoid SQL injection
$stmt = $conn->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->bind_param('s', $name);
$stmt->execute();
$stmt->close();
$conn->close();
?>

📊

Quick Reference

Here is a quick summary of SQL commands used in PHP CRUD operations:

Operation	SQL Command	Purpose
Create	INSERT INTO table_name (columns) VALUES (values)	Add new records
Read	SELECT columns FROM table_name WHERE condition	Retrieve records
Update	UPDATE table_name SET column=value WHERE condition	Modify existing records
Delete	DELETE FROM table_name WHERE condition	Remove records

✅

Key Takeaways

Use SQL commands inside PHP to perform Create, Read, Update, and Delete operations on a database.

Always check for database connection errors and query execution results.

Use prepared statements to protect against SQL injection and handle user input safely.

Close your database connection after operations to free resources.

Test each CRUD operation separately to ensure correct behavior.