Building a Database with PHP, PDO, and MySQL on Linux

 Step 1: Set Up Database 

 Assuming you have PHP, Apache, and MySQL already installed (if not, refer to the previous  blog post), let’s create a new database and table.

 In the MySQL shell, create a new database named "library_db".

CREATE DATABASE library_db;

 Switch to the newly created database and  create a table called 'books' to store our data:

USE library_db;
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    genre VARCHAR(100) NOT NULL,
    publication_year INT NOT NULL
);

 Step 2: PHP Setup for Database Connection

Create a PHP file named crud_operations.php to establish a connection to the MySQL database using PDO.

<?php

$dsn = "mysql:host=localhost;dbname=library_db";

$username = "your_username";

$password = "your_password";

try {

    $pdo = new PDO($dsn, $username, $password);

    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Connected successfully";

} catch (PDOException $e) {

    echo "Connection failed: " . $e->getMessage();

}

?>

Step 3: Implementing CRUD Operations

1. Create (Insert):

Let’s create a file named "create.php":

<?php

include "crud_operations.php";

$title = "The Catcher in the Rye";

$author = "J.D. Salinger";

$genre = "Fiction";

$publicationYear = 1951;

$stmt = $pdo->prepare("INSERT INTO books (title, author, genre, publication_year) VALUES (?, ?, ?, ?)");

$stmt->execute([$title, $author, $genre, $publicationYear]);

?>

2. Read (Select):

Create a file named "read.php":

<?php

include "crud_operations.php";

$stmt = $pdo->prepare("SELECT * FROM books");

$stmt->execute();

$books = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($books as $book) {

    echo "ID: {$book['id']}, Title: {$book['title']}, Author: {$book['author']}, Genre: {$book['genre']}, Year: {$book['publication_year']}<br>";

}

?>

3. Update:

Create a file named "update.php":

<?php

include "crud_operations.php";

$newGenre = "Classic Fiction";

$bookId = 1;

$stmt = $pdo->prepare("UPDATE books SET genre = ? WHERE id = ?");

$stmt->execute([$newGenre, $bookId]);

?>

4. Delete:

Create a file named "delete.php":

<?php

include "crud_operations.php";

$bookIdToDelete = 2;

$stmt = $pdo->prepare("DELETE FROM books WHERE id = ?");

$stmt->execute([$bookIdToDelete]);

?>