March 31, 2026 in Php
Give MCQs test (placement preparation)
🔹 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 KEY5. 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 rowfetchAll()→ 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 changesrollback()→ 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.”
