PHP and MySQL Database

Introduction

PHP and MySQL are widely used together to build dynamic, data-driven web applications. PHP handles the application logic, while MySQL is used to store, retrieve, and manage data.

Why Use PHP with MySQL?

• Open-source and free

• Easy to learn and use

• Fast and reliable

• Supported by all major hosting providers

• Used in CMSs like WordPress

Connecting PHP with MySQL

PHP supports MySQL using MySQLi and PDO.
PDO is recommended because it supports multiple databases and prepared statements.

MySQL Connection Using MySQLi

Procedural Style

$conn = mysqli_connect("localhost", "root", "", "testdb");

if (!$conn) {
    die("Connection failed");
}

Object-Oriented Style

$conn = new mysqli("localhost", "root", "", "testdb");

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

MySQL Connection Using PDO

try {
    $pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo $e->getMessage();
}

Creating a Database

$sql = "CREATE DATABASE demo";
mysqli_query($conn, $sql);

Creating a Table

$sql = "CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
)";
mysqli_query($conn, $sql);

Inserting Data into MySQL

Using MySQLi

$sql = "INSERT INTO users (name, email) VALUES ('Amit', 'amit@gmail.com')";
mysqli_query($conn, $sql);

Using PDO (Prepared Statement)

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["Amit", "amit@gmail.com"]);

Fetching Data from MySQL

Using MySQLi

$result = mysqli_query($conn, "SELECT * FROM users");

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'];
}

Using PDO

$stmt = $pdo->query("SELECT * FROM users");

while ($row = $stmt->fetch()) {
    echo $row['name'];
}

Updating Data

$sql = "UPDATE users SET name='Ravi' WHERE id=1";
mysqli_query($conn, $sql);

Deleting Data

$sql = "DELETE FROM users WHERE id=1";
mysqli_query($conn, $sql);

Preventing SQL Injection

Always use prepared statements.

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);

Closing Database Connection

mysqli_close($conn);
$pdo = null;

Best Practices

• Use PDO for database access

• Validate and sanitize input

• Use prepared statements

• Handle database errors properly

• Avoid using root user in production

Common Use Cases

• Login & registration systems

• CRUD applications

• CMS development

• E-commerce websites

Conclusion

PHP and MySQL together provide a powerful platform for creating dynamic and scalable web applications. Mastering database operations is essential for backend development.