Connecting to a MySQL Database with PHP: A Step-by-Step Guide

Databases are the backbone of dynamic web applications. They allow you to store, retrieve, and manage data efficiently. MySQL is one of the most popular relational database management systems, and PHP is a versatile server-side scripting language. In this guide, we will explore how to connect to a MySQL database using PHP, with real-world examples to help you grasp the concepts.

Why Connect PHP to MySQL?

Before we dive into the technical details, let’s understand why connecting PHP to MySQL is essential:

1. Data Persistence: Databases like MySQL provide a reliable way to store data permanently. This is crucial for applications that need to remember information between user sessions.
2. Data Retrieval: You can fetch data from databases to display on your website. For example, you might retrieve user profiles, product information, or blog posts.
3. Data Manipulation: PHP can interact with databases to insert, update, or delete records. This enables users to perform actions like submitting forms or updating their profiles.

Now, let’s get started with connecting PHP to MySQL.

Prerequisites

To follow along with the examples, you’ll need:

• A web server with PHP and MySQL installed. You can use tools like XAMPP, WAMP, or install these components separately.
• Basic knowledge of PHP and SQL (Structured Query Language).

Step 1: Establish a Connection

The first step is to establish a connection to your MySQL database. PHP provides a built-in function called mysqli_connect for this purpose. Here’s how you can create a connection:

<?php
  $servername = "localhost"; // Change this if your MySQL server is on a different host
  $username = "your_username";
  $password = "your_password";
  $database = "your_database_name";

  // Create a connection
  $conn = mysqli_connect($servername, $username, $password, $database);

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

  echo "Connected successfully";
?>

Replace your_username, your_password, and your_database_name with your MySQL credentials. This code establishes a connection to the MySQL server running on your localhost.

Step 2: Perform Database Operations

Once you have a connection, you can perform various database operations such as querying data, inserting records, updating data, or deleting records. Here’s an example of retrieving data from a table:

<?php

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

  if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . "<br>";
        echo "Name: " . $row["name"] . "<br>";
        echo "Email: " . $row["email"] . "<br><br>";
    }
  } else {
    echo "No records found";
  }

  mysqli_close($conn);

In this example, we execute an SQL query to select data from a “users” table, fetch the results, and display them. Always remember to close the database connection using mysqli_close when you’re done.

Step 3: Error Handling

Error handling is crucial when working with databases. You should check for errors after executing SQL queries. Here’s an example:

<?php
  $sql = "INSERT INTO products (name, price) VALUES ('Widget', 19.99)";

  if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
  } else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }

  mysqli_close($conn);

This code inserts a new record into a “products” table and checks for errors. If an error occurs, it displays an error message with details.

Conclusion

Connecting PHP to a MySQL database is a fundamental skill for web developers. In this guide, you’ve learned how to establish a connection, perform database operations, and handle errors using PHP and MySQL. As you continue your journey in web development, you’ll find yourself working with databases extensively to create dynamic and data-driven websites.

Remember to secure your database credentials, sanitize user inputs to prevent SQL injection, and follow best practices for database design. With these skills and practices, you’ll be well-equipped to build robust web applications.