Mastering PHP: From Basics to Building Dynamic Web Applications

Chapter 7: PHP with MySQL (Database Integration)

🔹 1. Introduction

One of PHP’s strongest features is its seamless integration with MySQL, a powerful relational database management system. Together, PHP and MySQL power dynamic web applications like blogs, forums, and e-commerce stores.

This chapter covers:

• Setting up a MySQL database
• Connecting PHP to MySQL using mysqli
• Performing CRUD operations (Create, Read, Update, Delete)
• Using prepared statements to prevent SQL injection

🔹 2. Setting Up MySQL

Most local servers (XAMPP, MAMP, Laragon) include phpMyAdmin, a graphical tool to manage MySQL.

✅ Steps:

1. Go to http://localhost/phpmyadmin
2. Create a new database (e.g. myapp)
3. Create a table called users:

🔹 3. Connecting PHP to MySQL

✅ Using mysqli:

$host = "localhost";

$user = "root";

$pass = "";

$db = "myapp";

$conn = mysqli_connect($host, $user, $pass, $db);

if (!$conn) {

  die("Connection failed: " . mysqli_connect_error());

}

echo "Connected successfully!";

✅ Use this connection on top of every page that needs to interact with the database.

🔹 4. Inserting Data into MySQL

$name = "Alice";

$email = "alice@example.com";

$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

if (mysqli_query($conn, $sql)) {

  echo "New user added!";

} else {

  echo "Error: " . mysqli_error($conn);

}

⚠️ This is vulnerable to SQL injection — better use prepared statements (see below).

🔹 5. Reading (SELECT) Data

$sql = "SELECT * FROM users";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

  echo "Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";

}

🔹 6. Updating Records

$sql = "UPDATE users SET name='Bob' WHERE id=1";

if (mysqli_query($conn, $sql)) {

  echo "Record updated successfully";

}

🔹 7. Deleting Records

$sql = "DELETE FROM users WHERE id=2";

if (mysqli_query($conn, $sql)) {

  echo "User deleted!";

}

🔹 8. Prepared Statements (Secure Way)

✅ Inserting Securely:

$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email) VALUES (?, ?)");

mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name = "John";

$email = "john@gmail.com";

mysqli_stmt_execute($stmt);

✅ Use prepared statements to avoid SQL injection.

🔹 9. Displaying Data in a Table

echo "<table border='1'>";

echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>";

$result = mysqli_query($conn, "SELECT * FROM users");

while($row = mysqli_fetch_assoc($result)){

  echo "<tr>

          <td>{$row['id']}</td>

          <td>{$row['name']}</td>

          <td>{$row['email']}</td>

        </tr>";

}

echo "</table>";

🔹 10. Closing the Connection

mysqli_close($conn);

Always close connections when you're done — this helps optimize resources.

✅ Summary Table: MySQL Functions