12. Database (MySQL) (PHP)

Featured

12. Database (MySQL) (PHP)

March 31, 2026 in 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

FeatureMySQLiPDO
Database SupportOnly MySQLMultiple DBs
API StyleProcedural + OOPOOP only
SecurityGoodBetter (more flexible)
Prepared StatementsYesYes

👉 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
🇮🇳 Amazon India 🌍 Amazon Global

aditya singh