PHP MySQL Database Connection

# Chapter 21: PHP MySQL Database Connection

1. Introduction #

2. Learning Objectives #

• Understand basic relational database concepts.

• Navigate phpMyAdmin to create a database.

• Connect to MySQL using the mysqli procedural method.

• Connect to MySQL using the modern PDO object-oriented method.

• Handle database connection errors gracefully.

3. MySQL Basics and phpMyAdmin #

1. 1. Open your XAMPP Control Panel.

1. 2. Start both Apache and MySQL.

1. 3. Click the Admin button next to MySQL (or go to http://localhost/phpmyadmin in your browser).

1. 4. Click New on the left sidebar, name your database php_course, and click Create.

4. PHP Database Extensions: mysqli vs PDO #

• MySQLi (MySQL Improved): Designed specifically for MySQL databases. It has both procedural and object-oriented syntax. It is slightly faster but only works with MySQL.

• PDO (PHP Data Objects): A robust, modern, purely object-oriented extension. The biggest advantage of PDO is that it works with over 12 different database systems (MySQL, PostgreSQL, SQLite). If you switch databases later, you don't have to rewrite your entire codebase.

*Industry Standard:* PDO is highly recommended for modern development. We will show both, but focus on PDO.

5. Connecting with MySQLi (Object-Oriented) #

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "php_course";

// Create connection object
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully using MySQLi";
?>

6. Connecting with PDO #

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "php_course";

try {
    // Construct the DSN
    $dsn = "mysql:host=$servername;dbname=$dbname";
    
    // Instantiate PDO
    $pdo = new PDO($dsn, $username, $password);
    
    // Set the PDO error mode to exception (crucial for debugging)
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Connected successfully using PDO";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

7. Real-World Examples #

8. Output Explanations #

9. Common Mistakes #

• Wrong Credentials on Live Servers: Beginners often upload their config.php to Hostinger or cPanel and wonder why it breaks. Your live server does NOT use root with an empty password. You must create a user in your cPanel MySQL databases tab and update the script.

• Forgetting to start MySQL: If XAMPP Apache is running but MySQL is stopped, the connection will time out and fail.

• Leaking Credentials: Uploading config.php to a public GitHub repository. Hackers scan GitHub for these files to steal database access. Always use .gitignore or .env files for credentials.

10. Best Practices #

• Use PDO. The object-oriented syntax is cleaner, and switching database engines in the future requires minimal changes.

• Wrap PDO connections in try/catch blocks so you can display a friendly "Website Under Maintenance" page to users if the database goes down, rather than exposing raw MySQL errors.

11. Exercises #

1. 1. Open phpMyAdmin and manually create a database named test_db.

1. 2. Write a config.php file using PDO to connect to test_db.

1. 3. Force an error by changing the username to wrong_user and observe the catch block output.

12. Mini Project: Reusable Database Connection #

db.php

<?php
// Configuration Variables
$host = &#039;localhost';
$db   = &#039;php_course';
$user = &#039;root';
$pass = &#039;';
$charset = &#039;utf8mb4'; // Standard for proper character encoding (Emojis!)

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

// Advanced PDO Options
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on error
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Return arrays by default
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Native prepared statements
];

try {
    // This $pdo object is what we will use in all future chapters!
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    // Log the error securely and stop the script
    die("Database connection failed. Please contact support.");
}
?>

index.php

<?php
// Simply require the file. If no error prints, we are connected!
require_once &#039;db.php';
?>
<!DOCTYPE html>
<html>
<body>
    <h2>App Status</h2>
    <p style="color: green;">Database is connected and ready for queries.</p>
</body>
</html>

13. Coding Challenges #

14. MCQs with Answers #

2. Why is PDO generally preferred over MySQLi? A) PDO is the only one that works on Windows. B) PDO supports multiple different database systems, whereas MySQLi only supports MySQL. C) PDO does not require a password. D) PDO uses less server memory. *Answer: B*

3. What does die() do in a connection error block? A) Deletes the database. B) Prints a message and immediately terminates the execution of the PHP script. C) Kills the MySQL server. D) Pauses the script for 5 seconds. *Answer: B*

15. Interview Questions #

Q: Why is charset=utf8mb4 important in your DSN? *A:* While standard utf8 in MySQL only supports 3-byte characters, utf8mb4 fully supports 4-byte characters. This is absolutely required if you want your database to safely store and retrieve modern Emojis or complex international characters without data truncation.

16. FAQs #

17. Summary #

18. Next Chapter Recommendation #