12. Database (MySQL) (PHP)

1️⃣ Introduction to MySQL

• MySQL is a relational database management system (RDBMS).
• It stores data in tables (rows & columns).
• Uses SQL (Structured Query Language).

Key Concepts:

• Database → Collection of tables
• Table → Collection of records
• Row → Single record
• Column → Field (name, email, etc.)

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

---

2️⃣ Connecting PHP with MySQL

PHP connects to MySQL using:

• MySQLi (procedural & OOP)
• PDO (recommended for flexibility)

MySQLi Example:

$conn = mysqli_connect("localhost", "root", "", "test_db");if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

PDO Example:

try {
    $conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

---

3️⃣ MySQLi vs PDO

Feature	MySQLi	PDO
Database Support	Only MySQL	Multiple DBs
API Style	Procedural + OOP	OOP only
Security	Good	Better (more flexible)
Prepared Statements	Yes	Yes

👉 Best Practice: Use PDO in modern applications.

---

4️⃣ CRUD Operations

CRUD = Create, Read, Update, Delete

🔹 Create (Insert)

$sql = "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')";
$conn->exec($sql);

🔹 Read (Select)

$stmt = $conn->query("SELECT * FROM users");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);foreach ($data as $row) {
    echo $row['name'];
}

🔹 Update

$sql = "UPDATE users SET name='Jane' WHERE id=1";
$conn->exec($sql);

🔹 Delete

$sql = "DELETE FROM users WHERE id=1";
$conn->exec($sql);

---

5️⃣ Prepared Statements (Very Important 🔥)

👉 Prevents SQL Injection (security attacks)

❌ Unsafe Query:

$name = $_GET['name'];
$sql = "SELECT * FROM users WHERE name = '$name'";

✅ Safe Prepared Statement (PDO):

$stmt = $conn->prepare("SELECT * FROM users WHERE name = :name");
$stmt->bindParam(':name', $name);
$stmt->execute();$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

MySQLi Prepared:

$stmt = $conn->prepare("SELECT * FROM users WHERE name = ?");
$stmt->bind_param("s", $name);
$stmt->execute();

---

🔥 Pro Tips (Important for Students)

• Always use prepared statements
• Never trust user input ($_GET, $_POST)
• Use try-catch in PDO
• Use LIMIT in queries for performance
• Close connections properly