How to Connect PHP to MySQL | mysqli & PDO Guide

# CHAPTER 17

Connecting PHP Applications with MySQL #

1. Introduction #

2. Learning Objectives #

• Understand the PHP-to-MySQL connection flow.

• Compare mysqli vs PDO extensions.

• Write a secure database connection script.

• Execute basic SQL queries via PHP.

• Prevent SQL Injection using Prepared Statements.

3. The Two Extensions: mysqli vs PDO #

1. 1. mysqli (MySQL Improved): This extension *only* works with MySQL databases. It is slightly easier for absolute beginners to learn.

1. 2. PDO (PHP Data Objects): This is the industry standard. PDO works with 12 different database engines (MySQL, PostgreSQL, SQLite). If you build your app with PDO, and later decide to switch from MySQL to PostgreSQL, you don't have to rewrite all your code!

*Note: In this course, we will use mysqli for simplicity, but acknowledge PDO as the enterprise standard.*

4. Establishing the Connection #

<?php
// 1. Define the connection credentials
$host = "localhost";
$username = "root";       // Your XAMPP user
$password = "";           // Blank by default in XAMPP
$database = "company_db"; // The DB you created in phpMyAdmin

// 2. Attempt the connection using mysqli
$conn = mysqli_connect($host, $username, $password, $database);

// 3. Check if the connection failed
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully to the database!";
?>

If you open http://localhost/db_connect.php in your browser and see the success message, your PHP app is officially talking to your database!

5. Executing a Simple Query (Read/SELECT) #

<?php
require &#039;db_connect.php'; // Pulls in the connection we just wrote

$sql = "SELECT id, username, email FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // Loop through the results and print them
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results found.";
}
?>

6. The Threat: SQL Injection #

$user_email = $_POST['email']; // Data from a form
$sql = "SELECT * FROM users WHERE email = '$user_email'";

If a hacker types ' OR 1=1 -- into the email box, the SQL statement becomes: SELECT * FROM users WHERE email = '' OR 1=1 --' Because 1 always equals 1, the database ignores the email check and logs the hacker into the first account it finds (usually the Admin)!

7. The Solution: Prepared Statements #

GOOD CODE (Secure):

<?php
$user_email = "alice@example.com"; // Imagine this came from a form

// 1. Prepare the SQL statement with a ? placeholder
$stmt = $conn->prepare("SELECT username FROM users WHERE email = ?");

// 2. Bind the actual data to the placeholder ("s" means String)
$stmt->bind_param("s", $user_email);

// 3. Execute the secure query
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row[&#039;username'];
}
?>

Even if the user types malicious code, the database treats it strictly as a text string, not as an executable command!

8. Mini Project: The Registration Form #

1. 1. HTML Form sends $POST['username'] and $POST['password'].

1. 2. dbconnect.php establishes the connection.

1. 3. PHP hashes the password: $hashedpw = passwordhash($POST['password'], PASSWORDDEFAULT);

1. 5. The query executes. You can verify it worked by opening phpMyAdmin and checking the Browse tab!

9. Common Mistakes #

• Putting Credentials in Public Repositories: Never hardcode your actual production database password into dbconnect.php and then upload that file to a public GitHub repository. Hackers scan GitHub for the word "mysqliconnect" and steal passwords daily. Use .env environment variables to hide credentials!

10. Best Practices #

• Close the Connection: Although PHP automatically closes connections when the script ends, it is a professional best practice to explicitly close it yourself using mysqliclose($conn); at the very bottom of your file to free up server RAM instantly.

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Compare and contrast the mysqli and PDO extensions in PHP. If you were architecting an enterprise SaaS application from scratch today, which would you choose and why?

• Q: Explain the mechanics of an SQL Injection attack (' OR 1=1 --). Detail the exact step-by-step workflow of how a Prepared Statement completely mitigates this vulnerability at the database engine level.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #