PHP & MySQL: CRUD Operation

Introduction

Welcome to this comprehensive tutorial on how to Create, Retrieve, Update, and Delete (CRUD) data in a MySQL database using PHP. In this guide, you’ll learn the basics of database operations and how you can use PHP to interact with MySQL databases effectively. We assume that you have a basic understanding of PHP and MySQL.

Setting Up the Environment

Before beginning, ensure that you have a development environment set up with PHP, MySQL, and a server (like Apache). You can install these individually or use an all-in-one package such as XAMPP or MAMP. Additionally, you’ll need a MySQL database management tool like phpMyAdmin for managing your database more easily.

Creating a Database

CREATE DATABASE db_example;

Creating a Table

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL,
    registration_date TIMESTAMP
);

Connecting to MySQL Database

Use the PDO (PHP Data Objects) extension or MySQLi for connecting to the database. PDO is more flexible and supports multiple database types.

$dsn = 'mysql:host=localhost;dbname=db_example';
$username = 'root';
$password = '';
try {
    $pdo = new PDO($dsn, $username, $password);
    echo 'Connection successful!';
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

CRUD Operation in PHP & MySQL

Creating Data – INSERT

Use the INSERT INTO statement to add new records to your table.

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);

// Setting example variables
$name = 'John Doe';
$email = '[email protected]';
$stmt->execute();

// Checking if insert was successful
if($stmt->rowCount()) {
    echo 'User added successfully.';
} else {
    echo 'Error adding user.';
}

Reading Data – SELECT

To retrieve data, use the SELECT statement. It’s also a good practice to use prepared statements to prevent SQL injection.

$stmt = $pdo->prepare('SELECT name, email FROM users WHERE id = :id');
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 1;
$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if($user) {
    echo 'User: ' . $user['name'] . ' - Email: ' . $user['email'];
} else {
    echo 'No user found.';
}

Updating Data – UPDATE

To update existing data, use the UPDATE statement in conjunction with prepared statements.

$stmt = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
$stmt->bindParam(':id', $id);
$stmt->bindParam(':email', $email);
$id = 1;
$email = '[email protected]';
$stmt->execute();

if($stmt->rowCount()) {
    echo 'User updated.';
} else {
    echo 'No changes made.';
}

Deleting Data – DELETE

To remove records, the DELETE statement is used. Always be cautious with the delete operation as it is irreversible.

$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->bindParam(':id', $id);
$id = 1;
$stmt->execute();

if($stmt->rowCount()) {
    echo 'User deleted.';
} else {
    echo 'No user found with that ID.';
}

Conclusion

We’ve covered the essentials of performing CRUD operations with PHP and MySQL. Remember to practice safe coding habits, and always secure your applications. Happy coding!

It’s crucial to ensure that your application is secure. Use prepared statements as they safeguard against SQL injection. Use try-catch blocks for error handling to manage exceptions that can occur during database operations.