MySQL with PHP

PHP MySQL Extensions

🔹 MySQLi Connection

MySQLi (MySQL Improved) is a PHP extension providing both procedural and object-oriented interfaces for MySQL databases. It offers improved performance, better security with prepared statements, and support for multiple statements. MySQLi is specifically designed for MySQL and provides MySQL-specific features.

<?php
// Object-oriented style
$servername = "localhost";
$username = "root";
$password = "your_password";
$database = "testdb";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

// Close connection
$conn->close();
?>

<?php
// Procedural style
$conn = mysqli_connect("localhost", "root", "password", "testdb");

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

mysqli_close($conn);
?>

🔹 PDO Connection

PDO (PHP Data Objects) provides a consistent interface for accessing multiple database types including MySQL, PostgreSQL, and SQLite. It uses object-oriented syntax and offers excellent security through prepared statements. PDO is the recommended choice for new projects due to its flexibility and database portability.

<?php
$servername = "localhost";
$username = "root";
$password = "your_password";
$database = "testdb";

try {
    // Create PDO connection
    $conn = new PDO(
        "mysql:host=$servername;dbname=$database",
        $username,
        $password
    );
    
    // Set error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Connected successfully";
    
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// Connection closes automatically
?>

🔹 Creating Tables

Create database tables using CREATE TABLE statements executed through PHP. Define table structure with columns, data types, primary keys, and constraints. Always check if the query executed successfully and handle errors appropriately. Use proper data types to ensure data integrity and optimal performance.

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

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL to create table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

🔹 Inserting Data

Insert data into MySQL tables using INSERT statements. Always use prepared statements to prevent SQL injection attacks. Prepared statements separate SQL logic from data, making your application secure. You can insert single records or use loops for multiple records efficiently.

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

// Using prepared statements (secure)
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $name, $email, $age);

// Insert single record
$name = "John Doe";
$email = "[email protected]";
$age = 28;

if ($stmt->execute()) {
    echo "New record created successfully. ID: " . $stmt->insert_id;
} else {
    echo "Error: " . $stmt->error;
}

// Insert multiple records
$users = [
    ["Alice Smith", "[email protected]", 25],
    ["Bob Johnson", "[email protected]", 32],
    ["Carol White", "[email protected]", 29]
];

foreach ($users as $user) {
    $name = $user[0];
    $email = $user[1];
    $age = $user[2];
    $stmt->execute();
}

echo "Multiple records inserted";

$stmt->close();
$conn->close();
?>

🔹 Querying Data

Retrieve data from MySQL using SELECT statements. Fetch results as associative arrays, numeric arrays, or objects. Use prepared statements with parameters for queries with user input. Loop through results to display or process data. Always handle cases where no results are found.

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

// Simple query
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>All Users:</h3>";
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"];
        echo " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No results found";
}

// Query with WHERE clause using prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE age > ?");
$min_age = 25;
$stmt->bind_param("i", $min_age);
$stmt->execute();
$result = $stmt->get_result();

echo "<h3>Users older than 25:</h3>";
while($row = $result->fetch_assoc()) {
    echo $row["name"] . " - " . $row["age"] . "<br>";
}

$stmt->close();
$conn->close();
?>

🔹 Updating and Deleting Data

Modify existing records with UPDATE statements and remove records with DELETE statements. Always use WHERE clauses to target specific records and prepared statements for security. Check affected_rows to verify how many records were modified. Provide user feedback about the operation results.

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

// Update record using prepared statement
$stmt = $conn->prepare("UPDATE users SET age = ? WHERE name = ?");
$age = 30;
$name = "John Doe";
$stmt->bind_param("is", $age, $name);

if ($stmt->execute()) {
    echo "Record updated successfully. ";
    echo "Rows affected: " . $stmt->affected_rows;
} else {
    echo "Error updating record: " . $stmt->error;
}

$stmt->close();

// Delete record
$stmt = $conn->prepare("DELETE FROM users WHERE age < ?");
$min_age = 20;
$stmt->bind_param("i", $min_age);

if ($stmt->execute()) {
    echo "Records deleted successfully. ";
    echo "Rows affected: " . $stmt->affected_rows;
} else {
    echo "Error deleting records: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

🔹 Using PDO with Prepared Statements

PDO prepared statements provide the best security and flexibility for database operations. They work with multiple database types and offer clean, readable syntax. Use named or positional placeholders for parameters. PDO automatically handles data types and escaping, making your code safer and more maintainable.

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=mydb", "root", "password");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Insert with named placeholders
    $stmt = $conn->prepare("INSERT INTO users (name, email, age) 
                            VALUES (:name, :email, :age)");
    $stmt->execute([
        ':name' => 'David Lee',
        ':email' => '[email protected]',
        ':age' => 27
    ]);
    echo "Record inserted. ID: " . $conn->lastInsertId();
    
    // Select with positional placeholders
    $stmt = $conn->prepare("SELECT * FROM users WHERE age > ?");
    $stmt->execute([25]);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($users as $user) {
        echo $user['name'] . " - " . $user['email'] . "<br>";
    }
    
    // Update
    $stmt = $conn->prepare("UPDATE users SET age = :age WHERE id = :id");
    $stmt->execute([':age' => 35, ':id' => 1]);
    echo "Updated rows: " . $stmt->rowCount();
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null; // Close connection
?>

🔹 Building a Simple CRUD Application

Combine HTML forms with PHP and MySQL to create a complete CRUD (Create, Read, Update, Delete) application. Handle form submissions, validate input, execute database operations, and display results. This pattern forms the foundation of most web applications and demonstrates practical database integration.

<?php
// config.php - Database connection
$conn = new mysqli("localhost", "root", "password", "mydb");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create user
if (isset($_POST['create'])) {
    $stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
    $stmt->bind_param("ssi", $_POST['name'], $_POST['email'], $_POST['age']);
    $stmt->execute();
    echo "User created successfully!";
    $stmt->close();
}

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

<!DOCTYPE html>
<html>
<head>
    <title>User Management</title>
</head>
<body>
    <h2>Add New User</h2>
    <form method="POST">
        <input type="text" name="name" placeholder="Name" required><br>
        <input type="email" name="email" placeholder="Email" required><br>
        <input type="number" name="age" placeholder="Age" required><br>
        <button type="submit" name="create">Add User</button>
    </form>

    <h2>Users List</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Age</th>
        </tr>
        <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?php echo $row['id']; ?></td>
            <td><?php echo $row['name']; ?></td>
            <td><?php echo $row['email']; ?></td>
            <td><?php echo $row['age']; ?></td>
        </tr>
        <?php endwhile; ?>
    </table>
</body>
</html>

<?php $conn->close(); ?>