PHP to MySQL Database Connection | mysqli vs PDO Tutorial

# CHAPTER 27

Connecting SQL Databases with PHP #

1. Introduction #

2. Learning Objectives #

• Understand the role of PHP as the intermediary layer.

• Differentiate between the mysqli and PDO extensions.

• Establish a database connection using credentials.

• Handle connection errors securely.

• Execute a basic SQL query from a PHP script.

3. The Architecture (Browser -> PHP -> SQL) #

1. 1. The User visits index.php in their browser.

1. 2. The PHP script boots up on the server.

1. 3. The PHP script securely logs into the SQL database using a username and password.

1. 4. The PHP script sends a text string containing SQL (e.g., "SELECT * FROM users") to the database.

1. 5. The database processes the SQL and hands the data back to PHP.

1. 6. PHP converts the data into HTML and sends it to the user's browser.

4. mysqli vs PDO #

• mysqli (MySQL Improved): Designed specifically and exclusively for MySQL databases. It is slightly faster but limits you to one database engine.

• PDO (PHP Data Objects): The modern, universal standard. PDO can connect to MySQL, PostgreSQL, SQLite, and Microsoft SQL Server using the exact same PHP code.

*We will cover both, but PDO is the industry standard for modern enterprise applications.*

5. Connecting with mysqli (Procedural) #

<?php
// 1. Define the connection credentials
$host = "localhost";
$username = "root"; // Default XAMPP username
$password = "";     // Default XAMPP password is empty
$database = "school_system";

// 2. Establish the connection!
$conn = mysqli_connect($host, $username, $password, $database);

// 3. Check for catastrophic failure
if (!$conn) {
    die("Database Connection Failed: " . mysqli_connect_error());
}

echo "Successfully connected to the database using mysqli!";
?>

6. Connecting with PDO (Object-Oriented) #

<?php
$host = &#039;localhost';
$db   = &#039;school_system';
$user = &#039;root';
$pass = &#039;';

// The DSN strictly defines the engine (mysql) and the charset
$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";

try {
    // Attempt to instantiate the PDO connection object
    $pdo = new PDO($dsn, $user, $pass);
    
    // Set PDO to throw an Exception if a query fails
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Successfully connected to the database using PDO!";
    
} catch (\PDOException $e) {
    // If the connection fails, catch the error so the website doesn't crash!
    die("Database Connection Failed: " . $e->getMessage());
}
?>

7. Executing a Simple Query (PDO) #

<?php
// ... (PDO connection code from above) ...

// 1. Write the SQL string
$sql = "SELECT id, first_name FROM students LIMIT 5";

// 2. Query the database
$stmt = $pdo->query($sql);

// 3. Loop through the returned rows and format them for the browser!
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "Student ID: " . $row[&#039;id'] . " - Name: " . $row['first_name'] . "<br>";
}
?>

8. Common Mistakes #

• Hardcoding Credentials in Public Files: Beginners often put their database password directly inside index.php. If the PHP server crashes, the raw code might be displayed to the user, exposing the password! Passwords should always be stored in a separate, hidden .env file that PHP reads securely.

• Suppressing Errors: Using @mysqliconnect() suppresses error messages. If the connection fails, you will get a blank white screen and have no idea why. Always handle connection errors properly!

$dsn = "mysql:host=localhost;dbname=ecommerce_db;charset=utf8mb4";

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain the role of the backend server (PHP/Node.js) in a modern web architecture. Why doesn't the user's browser (JavaScript) simply connect directly to the SQL database? (Hint: Security and Credential exposure).

• Q: Compare and contrast the mysqli and PDO extensions. Which would you choose for a new enterprise project and why?

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #