Connecting PHP to MySQL: APIs, Drivers & Extensions
1. What is a Database API?
API (Application Programming Interface): A set of rules and protocols that allows one software application to communicate with another. In the PHP-MySQL context, a database API provides the functions and classes PHP needs to talk to a MySQL database server.
Components Involved:
| Component | Role |
|---|---|
| PHP Script | Your application code that wants to store/retrieve data |
| PHP Extension | MySQLi or PDO — the "translator" between PHP and MySQL |
| MySQL Driver | Low-level C library that actually sends commands to MySQL server |
| MySQL Server | The database server process that stores and manages data |
2. Two Main PHP Database Extensions
a) MySQLi Extension (MySQL Improved)
- "i" stands for "Improved" — a major upgrade over the old (deprecated)
mysql_extension. - Designed specifically for MySQL databases only.
- Supports both Procedural style (function-based) and Object-Oriented style (class-based).
- Supports prepared statements, multiple statements, and transactions.
b) PDO (PHP Data Objects)
- A Database Abstraction Layer (DAL).
- Supports 12+ different database types (MySQL, PostgreSQL, SQLite, Oracle, etc.) through swappable drivers.
- Object-Oriented ONLY — no procedural interface.
- Makes it easy to switch databases without rewriting your PHP code.
3. MySQLi vs PDO — Comprehensive Comparison
| Feature | MySQLi | PDO |
|---|---|---|
| Database Support | MySQL Only | 12+ databases (portable) |
| Programming Style | OOP + Procedural | OOP Only |
| Prepared Statements | Yes (both client & server side) | Yes (client side) |
| Named Parameters | No (uses ? placeholders) | Yes (uses :name placeholders) |
| Error Handling | Checking connect_error property | Exceptions (Try-Catch blocks) |
| Performance | Slightly faster for MySQL | Slightly slower (abstraction overhead) |
| When to Use | When MySQL is the only DB you'll ever use | When you might switch databases, or want cleaner error handling |
4. Connection — Three Approaches
Approach 1: MySQLi Object-Oriented (Recommended for MySQL projects)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDatabase";
// Create connection (new mysqli is the OOP approach)
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully using MySQLi OOP!";
?>Approach 2: MySQLi Procedural
<?php
$conn = mysqli_connect("localhost", "root", "", "myDatabase");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected using MySQLi Procedural!";
?>Approach 3: PDO with Try-Catch
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=myDatabase", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected using PDO!";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>5. Closing the Connection
Always close the database connection when your script finishes using it. This frees up server resources.
// MySQLi OOP
$conn->close();
// MySQLi Procedural
mysqli_close($conn);
// PDO — simply nullify the object
$pdo = null;6. Connection Best Practices
- Never hardcode credentials in your PHP files. Store them in a separate config file and exclude it from version control (add to
.gitignore). - Always check for connection errors before executing queries.
- Use prepared statements (covered in CRUD lesson) to prevent SQL Injection.
- Always close connections after use.