MySQLi vs PDO in PHP: Which Should You Use?
MySQLi works with MySQL only; PDO speaks 12+ databases with one API and cleaner prepared statements. Comparison, same query in both styles, and a clear verdict.
The one-line answer
Both are PHP's doors to a database. MySQLi speaks to MySQL/MariaDB only; PDO speaks to 12+ databases (MySQL, PostgreSQL, SQLite, SQL Server...) through ONE identical API. Both support the thing that actually matters — prepared statements — but PDO's are cleaner, especially named placeholders.
The same query, both styles — judge for yourself
<?php
// ---------- PDO ----------
$pdo = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"db_user", "db_pass",
array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);
$stmt = $pdo->prepare("SELECT * FROM students WHERE class = :class AND section = :sec");
$stmt->execute(array(':class' => '10', ':sec' => 'A')); // named - readable!
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// ---------- MySQLi ----------
$mysqli = new mysqli("localhost", "db_user", "db_pass", "school_db");
$stmt = $mysqli->prepare("SELECT * FROM students WHERE class = ? AND section = ?");
$stmt->bind_param("ss", $class, $section); // "ss" = two strings - must count types!
$class = '10'; $section = 'A';
$stmt->execute();
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
?>Spot the differences: PDO uses :named placeholders and passes values straight into execute(); MySQLi uses ? plus a type string ("ss", "sid"...) in bind_param that you must keep in sync by hand — a classic source of bugs when a query grows to 8 parameters.
Why prepared statements are the real headline
<?php
// NEVER: string-glued query (SQL injection!)
$q = "SELECT * FROM users WHERE name = '" . $_POST['name'] . "'";
// attacker types: ' OR '1'='1 -> query returns ALL users!
// ALWAYS: prepared statement (PDO or MySQLi - both protect)
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
$stmt->execute(array(':name' => $_POST['name']));
// user input travels separately from SQL - injection impossible
?>Whichever you choose, the non-negotiable rule is the same: user input NEVER gets glued into SQL strings — always placeholders. The MySQLi-vs-PDO debate is secondary; prepared-vs-glued is life and death.
Comparison table
| Point | MySQLi | PDO |
|---|---|---|
| Databases | MySQL/MariaDB only | 12+ via drivers |
| Placeholders | ? positional only | ? positional AND :named |
| Bind style | bind_param + type string | Array into execute() |
| API style | Procedural + OOP both | OOP only |
| Error mode | Manual checking (or mysqli_report) | Clean exceptions (ERRMODE_EXCEPTION) |
| fetchAll to assoc | get_result()->fetch_all() | fetchAll(PDO::FETCH_ASSOC) |
| Switch DB later | Rewrite DB code | Change connection string |
Verdict for real projects
- New projects: PDO. Named placeholders, exceptions, portability, one API to remember. On shared hosting (PHP 7.2/7.3) PDO is always available.
- MySQLi is fine when maintaining legacy code that already uses it, or migrating old mysql_* code (function names map closely).
- Frameworks agree: Laravel, Symfony, WordPress's newer layers — all ride on PDO underneath.
- Interview line: "Both do prepared statements; PDO adds multi-database support, named placeholders and exception-based errors, which is why it's the modern default — but the real rule is prepared statements everywhere, whichever API."
One-line answer
दोनों database तक PHP के दरवाज़े हैं. MySQLi सिर्फ MySQL/MariaDB से बात करता है; PDO एक ही identical API से 12+ databases (MySQL, PostgreSQL, SQLite, SQL Server...) से. जो चीज़ असल में matter करती है — prepared statements — दोनों में है, पर PDO की ज़्यादा साफ है, खासकर named placeholders.
Same query, दोनों styles — खुद फैसला कीजिए
<?php
// ---------- PDO ----------
$pdo = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"db_user", "db_pass",
array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);
$stmt = $pdo->prepare("SELECT * FROM students WHERE class = :class AND section = :sec");
$stmt->execute(array(':class' => '10', ':sec' => 'A')); // named - पढ़ने लायक!
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// ---------- MySQLi ----------
$mysqli = new mysqli("localhost", "db_user", "db_pass", "school_db");
$stmt = $mysqli->prepare("SELECT * FROM students WHERE class = ? AND section = ?");
$stmt->bind_param("ss", $class, $section); // "ss" = दो strings - types गिनने पड़ते हैं!
$class = '10'; $section = 'A';
$stmt->execute();
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
?>अंतर पकड़िए: PDO :named placeholders use करता है और values सीधे execute() में जाती हैं; MySQLi में ? plus bind_param की type string ("ss", "sid"...) जिसे हाथ से sync रखना पड़ता है — query के 8 parameters होते ही bugs की classic जड़.
असली headline: prepared statements क्यों
<?php
// KABHI NAHI: string-चिपकाई query (SQL injection!)
$q = "SELECT * FROM users WHERE name = '" . $_POST['name'] . "'";
// attacker लिखता है: ' OR '1'='1 -> query SAARE users दे देती है!
// HAMESHA: prepared statement (PDO या MySQLi - दोनों बचाते हैं)
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
$stmt->execute(array(':name' => $_POST['name']));
// user input SQL से अलग सफर करता है - injection impossible
?>जो भी चुनें, non-negotiable rule same है: user input SQL strings में KABHI नहीं चिपकता — हमेशा placeholders. MySQLi-vs-PDO बहस secondary है; prepared-vs-glued ज़िंदगी-मौत का फर्क है.
Comparison table
| Point | MySQLi | PDO |
|---|---|---|
| Databases | सिर्फ MySQL/MariaDB | Drivers से 12+ |
| Placeholders | सिर्फ ? positional | ? positional AND :named |
| Bind style | bind_param + type string | execute() में array |
| API style | Procedural + OOP दोनों | सिर्फ OOP |
| Error mode | Manual checking (या mysqli_report) | साफ exceptions (ERRMODE_EXCEPTION) |
| fetchAll to assoc | get_result()->fetch_all() | fetchAll(PDO::FETCH_ASSOC) |
| बाद में DB बदलना | DB code दोबारा लिखो | Connection string बदलो |
असली projects के लिए verdict
- नए projects: PDO. Named placeholders, exceptions, portability, याद रखने को एक API. Shared hosting (PHP 7.2/7.3) पर PDO हमेशा available है.
- MySQLi ठीक है जब legacy code maintain कर रहे हों जो पहले से उसे use करता है, या पुराना mysql_* code migrate कर रहे हों (function names करीब-करीब map होते हैं).
- Frameworks की भी यही राय: Laravel, Symfony, WordPress की नई layers — सब नीचे PDO पर चलती हैं.
- Interview line: "Prepared statements दोनों करते हैं; PDO multi-database support, named placeholders और exception-based errors जोड़ता है, इसलिए modern default है — पर असली rule है हर जगह prepared statements, API कोई भी हो."
Frequently Asked Questions
What is the difference between MySQLi and PDO?
MySQLi works only with MySQL/MariaDB and binds parameters via a type string, while PDO supports 12+ databases with one API, offers named placeholders, and reports errors as clean exceptions — making PDO the modern default.
Do both MySQLi and PDO prevent SQL injection?
Yes — both provide prepared statements that keep user input separate from the SQL text; injection happens only when input is concatenated into query strings, which must never be done in either API.
Which should I use for a new PHP project?
PDO — named placeholders are more readable, ERRMODE_EXCEPTION gives clean error handling, and switching databases later needs only a new connection string.