🔹 Basic Level Answers

1. What is MySQL?

MySQL is a relational database management system (RDBMS) that stores data in tables.

👉 SQL = language
👉 MySQL = software that uses SQL

---

2. What is PDO? Why use it?

PDO (PHP Data Objects) is a database abstraction layer.

✅ Supports multiple DBs
✅ Secure (prepared statements)
✅ OOP based

---

3. MySQLi vs PDO

• MySQLi → Only MySQL
• PDO → Multiple DBs (MySQL, PostgreSQL, etc.)

👉 Interview line: “PDO is more flexible and reusable.”

---

4. Primary Key

Unique identifier for each row.

id INT AUTO_INCREMENT PRIMARY KEY

---

5. CRUD

• Create → INSERT
• Read → SELECT
• Update → UPDATE
• Delete → DELETE

---

6. PDO Connection

$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

---

7. fetch() vs fetchAll()

• fetch() → single row
• fetchAll() → multiple rows

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

---

🔹 Intermediate Answers

1. Prepared Statements 🔥

Precompiled SQL queries that prevent SQL injection.

$stmt = $conn->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);

---

2. SQL Injection

Malicious SQL input.

❌ Unsafe:

$sql = "SELECT * FROM users WHERE email = '$email'";

✅ Safe:

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

---

3. bindParam vs bindValue

• bindParam() → binds variable (by reference)
• bindValue() → binds actual value

---

4. rowCount()

Returns affected rows.

$stmt->rowCount();

---

5. Error Handling

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

---

6. lastInsertId()

$conn->lastInsertId();

---

7. Index

Improves query speed.

CREATE INDEX idx_email ON users(email);

---

8. INNER JOIN vs LEFT JOIN

• INNER → matching rows only
• LEFT → all left + matched right

---

🔹 Advanced Answers

1. How PDO prevents SQL Injection

• Separates SQL & data
• Escapes input automatically

---

2. Transactions 🔥

Used for multiple queries safely.

$conn->beginTransaction();try {
    $conn->exec("INSERT INTO users (name) VALUES ('A')");
    $conn->exec("INSERT INTO orders (user_id) VALUES (1)");
    $conn->commit();
} catch(Exception $e) {
    $conn->rollback();
}

---

3. commit vs rollback

• commit() → save changes
• rollback() → undo changes

---

4. Optimize queries

• Use indexes
• Use LIMIT
• Avoid SELECT *
• Use proper joins

---

5. CHAR vs VARCHAR

• CHAR → fixed length
• VARCHAR → dynamic

---

6. Password Security 🔥

$password = password_hash($pass, PASSWORD_DEFAULT);
password_verify($pass, $hash);

---

7. ACID Properties

• Atomicity
• Consistency
• Isolation
• Durability

---

🔹 Practical Coding Answers

1. Insert User

$stmt = $conn->prepare("INSERT INTO users(name,email) VALUES (?,?)");
$stmt->execute([$name,$email]);

---

2. Login System

$stmt = $conn->prepare("SELECT * FROM users WHERE email=?");
$stmt->execute([$email]);
$user = $stmt->fetch();if($user && password_verify($pass,$user['password'])){
    echo "Login success";
}

---

3. Fetch Users

$stmt = $conn->query("SELECT * FROM users");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

---

4. Update

$stmt = $conn->prepare("UPDATE users SET email=? WHERE id=?");
$stmt->execute([$email,$id]);

---

5. Delete

$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->execute([$id]);

---

6. Search

$stmt = $conn->prepare("SELECT * FROM users WHERE name LIKE ?");
$stmt->execute(["%$name%"]);

---

7. Pagination

$limit = 10;
$offset = 0;$stmt = $conn->prepare("SELECT * FROM users LIMIT ? OFFSET ?");
$stmt->bindValue(1, $limit, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();

---

8. JOIN Query

$stmt = $conn->query("
SELECT users.name, orders.id 
FROM users 
INNER JOIN orders ON users.id = orders.user_id
");

---

🔹 Scenario Answers (Impress Interviewer)

1. DB hacked?

• Change credentials
• Backup restore
• Patch vulnerability
• Use prepared statements

---

2. Handle 1M records?

• Indexing
• Pagination
• Caching
• Optimized queries

---

3. Secure login system?

• password_hash()
• Prepared statements
• Sessions
• CSRF protection

---

🔥 Final Interview Line

Say this confidently:

“I always use PDO with prepared statements, password hashing, transactions where needed, and proper indexing to ensure both security and performance.”