Connecting to a MySQL database is a fundamental skill for backend PHP developers. This connection allows you to store, retrieve, and manage data in your web applications. In this guide, you will learn how to connect to a MySQL database in PHP using the mysqli extension and explore both object-oriented and procedural approaches.

Step 1: Setting Up Your MySQL Database

Before connecting to a MySQL database, ensure that you have a MySQL server running and that you have created a database. You will also need the following information:

  • Database Name: The name of your database.
  • Host: Typically localhost if you are running the database on the same machine.
  • Username: Your MySQL username.
  • Password: Your MySQL password.

For this example, let’s assume you have a MySQL database named my_database, with a user admin and password password123.

Step 2: Using the mysqli Extension (Object-Oriented Approach)

The mysqli extension in PHP provides an easy way to connect to MySQL databases. We’ll first cover the object-oriented method.

Example: Connecting to a Database

<?php
$servername = "localhost";
$username = "admin";
$password = "password123";
$dbname = "my_database";

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

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

Explanation:

  • $servername, $username, $password, $dbname: Variables that store your connection credentials.
  • new mysqli(): Creates a new connection object.
  • $conn->connect_error: Checks if there is an error in the connection. If so, it stops the script and outputs an error message.

This script will display "Connected successfully" if the connection is established.

Step 3: Using the mysqli Extension (Procedural Approach)

If you prefer the procedural approach, the steps are slightly different but still straightforward.

Example: Procedural Connection

<?php
$servername = "localhost";
$username = "admin";
$password = "password123";
$dbname = "my_database";

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

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Explanation:

  • mysqli_connect(): Establishes a connection to the MySQL database.
  • mysqli_connect_error(): Returns an error message if the connection fails.

This method works similarly to the object-oriented approach but uses procedural syntax.

Step 4: Closing the Database Connection

Once you are done working with the database, it's a good practice to close the connection. This helps free up resources on both the web server and the MySQL server.

Example: Closing the Connection (Object-Oriented)

<?php
$conn->close();
?>

Example: Closing the Connection (Procedural)

<?php
mysqli_close($conn);
?>

Explanation:

  • $conn->close(): Closes the connection using the object-oriented method.
  • mysqli_close($conn): Closes the connection using the procedural method.

Step 5: Handling Connection Errors

It’s essential to handle potential connection errors gracefully. You can customize your error messages to be more user-friendly or log errors for further debugging.

Example: Custom Error Handling

<?php
$servername = "localhost";
$username = "admin";
$password = "password123";
$dbname = "my_database";

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

// Check connection
if ($conn->connect_error) {
  error_log("Connection failed: " . $conn->connect_error, 3, "/var/log/myphp.log");
  die("Sorry, we're having trouble connecting to the database.");
}
echo "Connected successfully";
?>

Explanation:

  • error_log(): Logs errors to a file for further review.
  • die(): Stops script execution with a custom error message. In this case, we display a generic error message to users while logging detailed errors to a log file.

Step 6: Performing Database Queries

Now that you know how to connect to a MySQL database, let's execute a simple SQL query. For example, you can retrieve all records from a table called users.

Example: Retrieving Data from the Database

<?php
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // Output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

Explanation:

  • $conn->query(): Executes an SQL query.
  • $result->num_rows: Checks if the query returned any rows.
  • $row = $result->fetch_assoc(): Fetches each row from the result set as an associative array.
  • $conn->close(): Closes the connection after the query.

Conclusion

Connecting to a MySQL database in PHP is a fundamental task for backend development. Whether you prefer the object-oriented or procedural approach, mastering these methods allows you to interact with databases, perform queries, and manage data effectively. Remember to handle connection errors and close the connection when you're done to ensure optimal performance and resource management.