How to Connect to a MySQL Database Using PHP

Connecting to a MySQL Database Using PHP

Connecting to a MySQL database with PHP is an essential skill for building dynamic web applications. Let’s break it down step-by-step, so you can understand how to establish a connection and interact with your database seamlessly.

Step 1: Set Up Your Environment

Before we dive into coding, ensure you have the following set up:

• A web server (like Apache or Nginx)
• PHP (version 7.0 or higher is recommended)
• MySQL server

You can set this up locally using software like XAMPP or MAMP, which packages everything you need.

Step 2: Create a MySQL Database

For our examples, let’s create a simple database named test_db with a table called users. You can run the following SQL commands in your MySQL client:

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Step 3: Connect to the Database Using PHP

Now, let’s write some PHP code to connect to our new database. Create a file named db_connect.php and add the following code:

<?php
// Database configuration
$host = 'localhost';
$username = 'your_username'; // replace with your MySQL username
$password = 'your_password'; // replace with your MySQL password
$dbname = 'test_db';

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

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

echo "Connected successfully";
?>

Explanation:

• mysqli: This is a built-in PHP class for connecting to MySQL databases.
• new mysqli(): This creates a new connection to the MySQL database.
• $conn->connect_error: This checks if there was an error during the connection.

Step 4: Testing the Connection

Save the db_connect.php file and run it in your browser (e.g., http://localhost/db_connect.php). If everything is set up correctly, you should see the message: Connected successfully. If you see an error message, double-check your database credentials.

Step 5: Querying the Database

Now that we’re connected, let’s insert a new user into the users table. Update your db_connect.php file with the following code:

// Insert a new user
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
if (\(conn->query(\)sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . \(sql . "<br>" . \)conn->error;
}

// Close the connection
$conn->close();
?>

Explanation:

• $sql: This variable holds the SQL command to insert a new user.
• $conn->query(): This method runs the SQL command against the database.
• $conn->close(): This closes the connection to the database after the operation is complete.

Conclusion

Congratulations! You’ve successfully connected to a MySQL database using PHP and executed a simple database operation. This foundational knowledge will help you as you continue to build dynamic web applications. Keep experimenting with different queries and database operations to enhance your skills!