Step 1: Database Setup

First, we’ll set up a MySQL database named my_app and a table named users to store user information.

SQL to create database and table:

CREATE DATABASE my_app;

USE my_app;

CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50),
age INT(11)
);

Step 2: PHP Code to Connect to the Database

Create a file named db.php to handle the database connection.

<?php
$host = ‘localhost’;
$dbname = ‘my_app’;
$username = ‘root’; // default username for localhost
$password = ”; // default password for localhost

try {
$pdo = new PDO(“mysql:host=$host;dbname=$dbname”, $username, $password);
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully”;
} catch(PDOException $e) {
echo “Connection failed: ” . $e->getMessage();
}
?>

Step 3: Create a New User

To insert a new user into the database, create a PHP script named

<?php
require ‘db.php’;

$name = “John Doe”;
$email = “john@example.com”;
$age = 30;

$sql = “INSERT INTO users (name, email, age) VALUES (:name, :email, :age)”;
$stmt = $pdo->prepare($sql);
$stmt->execute([‘name’ => $name, ’email’ => $email, ‘age’ => $age]);

echo “New user created successfully”;
?>

Step 4: Read Users

To fetch and display all users, create

<?php
require ‘db.php’;

$sql = “SELECT id, name, email, age FROM users”;
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row[‘name’] . “, ” . $row[’email’] . “, ” . $row[‘age’] . “<br>”;
}
?>

Step 5: Update a User

To update a user’s details, use

<?php
require ‘db.php’;

$id = 1; // Example user ID
$newEmail = “jane@example.com”;

$sql = “UPDATE users SET email = :email WHERE id = :id”;
$stmt = $pdo->prepare($sql);
$stmt->execute([’email’ => $newEmail, ‘id’ => $id]);

echo “User updated successfully”;
?>

Step 6: Delete a User

For deleting a user, create

<?php
require ‘db.php’;

$id = 1; // Example user ID to delete

$sql = “DELETE FROM users WHERE id = :id”;
$stmt = $pdo->prepare($sql);
$stmt->execute([‘id’ => $id]);

echo “User deleted successfully”;
?>