How to Connect PHP to MySQL: Step-by-Step Tutorial for Beginners

By
How to Connect PHP to MySQL: Step-by-Step Tutorial for Beginners
Share

Are you looking to build dynamic, data-driven web applications? If so, learning how to connect PHP to MySQL is a critical skill. PHP and MySQL are a powerful duo in web development, allowing you to create interactive websites that store and retrieve data seamlessly. Whether you’re a beginner or an experienced developer brushing up on the basics, this PHP MySQL connection tutorial will guide you through every step.

In this article, you’ll discover a detailed, beginner-friendly walkthrough on connecting PHP to a MySQL database. We’ll cover setting up your environment, writing the connection code, testing it, and troubleshooting common issues—all with practical examples. By the end, you’ll have the confidence to integrate databases into your PHP projects like a pro. Let’s dive in!

Why Use PHP and MySQL Together?

Before we jump into the PHP MySQL step-by-step process, let’s understand why this combination is so popular. PHP is a server-side scripting language designed for web development, while MySQL is an open-source relational database management system. Together, they enable developers to:

  • Store user data (e.g., registrations, orders, or comments).
  • Retrieve and display dynamic content on web pages.
  • Build scalable applications like e-commerce platforms or content management systems.

With that in mind, let’s get started with the essentials of a PHP MySQL database connection.

Prerequisites for Connecting PHP to MySQL

To follow this PHP MySQL connection tutorial, you’ll need a few things in place:

  1. A Web Server: Install a local server like XAMPP, WAMP, or MAMP, which includes Apache, PHP, and MySQL.
  2. PHP Installed: Ensure PHP is running (version 7.4 or higher recommended).
  3. MySQL Database: Set up a MySQL server and create a database to connect to.
  4. Text Editor: Use any code editor like VS Code, Sublime Text, or Notepad++.

If you’re new to setting up a local environment, check out this XAMPP installation guide for a quick setup.

Step-by-Step Guide: How to Connect PHP to MySQL

Now, let’s walk through the process of establishing a MySQL database connection in PHP. We’ll use two popular methods: MySQLi (MySQL Improved) and PDO (PHP Data Objects). Both are effective, but PDO offers more flexibility across database systems.

Step 1: Create a MySQL Database

First, you need a database to connect to. Log in to your MySQL server (e.g., via phpMyAdmin or the MySQL command line) and create a database. Here’s an example using the MySQL command line:


CREATE DATABASE my_database;

Replace my_database with your preferred database name.

Step 2: Set Up Your Database Credentials

To connect PHP to MySQL, you’ll need four pieces of information:

  • Host: Usually localhost for a local server.
  • Username: Default is often root for local setups.
  • Password: Leave blank if none is set (common in XAMPP), or use your MySQL password.
  • Database Name: The name you created (e.g., my_database).

Keep these handy—we’ll use them in the next step.

Step 3: Write the PHP MySQL Connection Code

Let’s explore both MySQLi and PDO methods with PHP MySQL example code.

Method 1: Using MySQLi (Procedural Style)

Create a file named connect.php in your web server’s root directory (e.g., htdocs in XAMPP). Add the following code:


<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

Save the file and access it via your browser (e.g., http://localhost/connect.php). If successful, you’ll see “Connected successfully using MySQLi!”

Method 2: Using PDO

Alternatively, here’s how to connect PHP to MySQL using PDO:


<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    $conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
    // Set error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully using PDO!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Run this code the same way. PDO’s advantage is its support for multiple database types (e.g., PostgreSQL, SQLite) and better error handling.

Step 4: Test the Connection

Visit your file in the browser. If you see a success message, congratulations—you’ve mastered the PHP MySQL database connection! If not, don’t worry—we’ll troubleshoot next.

Troubleshooting Common Connection Errors

Encountering issues? Here are common problems and fixes:

  • “Access Denied” Error: Double-check your username and password.
  • “Unknown Database” Error: Ensure the database exists and the name matches your code.
  • “Connection Refused” Error: Verify your MySQL server is running (e.g., start it in XAMPP).

Best Practices for PHP MySQL Connections

To make your PHP MySQL step-by-step implementation secure and efficient, follow these tips:

  1. Close Connections: Always close your database connection when done (e.g., mysqli_close($conn); for MySQLi or $conn = null; for PDO).
  2. Use Prepared Statements: Prevent SQL injection by using parameterized queries.
  3. Store Credentials Securely: Move sensitive data to a separate config file outside the web root.

Here’s an example of a secure config file (config.php):


<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'my_database');
?>

Then include it in your connection script:


<?php
require_once 'config.php';
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully!";
?>

Real-World Example: Fetching Data

Let’s put your connection to work. Create a table and fetch data:


// Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

// Insert sample data
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

Now, fetch it with PHP (MySQLi example):


<?php
$conn = mysqli_connect("localhost", "root", "", "my_database");
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$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"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

This displays all users in your table—proof your connection works!

Conclusion

Congratulations! You’ve learned how to use PHP with MySQL through a comprehensive, hands-on tutorial. From setting up your environment to writing secure connection code, you’re now equipped to build dynamic web applications. Whether you choose MySQLi or PDO, the key is practice—start experimenting with your own projects today!

For more coding guides, check out our PHP tutorials category for additional resources.

Related Posts

Table of Contents

← Table of Contents
Scroll to Top