The Project | About Us | Contribute | Donations | License

HOME

In this section, we will learn how to connect to a MySQL database using PHP. This is a crucial skill for any web developer, as databases are often used to store and manage data for web applications.

Key Concepts

MySQL Database: A popular relational database management system.
PHP Data Objects (PDO): A database access layer providing a uniform method of access to multiple databases.
MySQLi: An improved version of the original MySQL extension, offering both procedural and object-oriented interfaces.

Prerequisites

Before we start, ensure you have:

A MySQL server running.
A database created on the MySQL server.
PHP installed and configured on your development environment.

Connecting to MySQL using MySQLi

Procedural Style

<?php
$servername = "localhost";
$username = "root";
$password = "";
$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";
?>

Object-Oriented Style

<?php
$servername = "localhost";
$username = "root";
$password = "";
$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: The hostname of the MySQL server (usually "localhost").
$username: The MySQL username.
$password: The MySQL password.
$dbname: The name of the database you want to connect to.
mysqli_connect(): A function to establish a connection to the MySQL server.
$conn->connect_error: A property that holds the error message if the connection fails.

Connecting to MySQL using PDO

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

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

Explanation

new PDO(): Creates a new PDO instance representing a connection to a database.
mysql:host=$servername;dbname=$dbname: The Data Source Name (DSN) specifying the host and database name.
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION): Sets the error mode to exception, which helps in better error handling.
catch(PDOException $e): Catches any exceptions thrown by the PDO instance.

Practical Exercise

Task

Create a PHP script to connect to a MySQL database using both MySQLi and PDO.
Ensure the script handles connection errors gracefully.

Solution

// MySQLi Procedural
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

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

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully using MySQLi Procedural";
mysqli_close($conn);

// MySQLi Object-Oriented
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully using MySQLi Object-Oriented";
$conn->close();

// PDO
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully using PDO";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
$conn = null;

Common Mistakes

Incorrect Credentials: Ensure the username, password, and database name are correct.
Server Not Running: Make sure the MySQL server is running.
Firewall Issues: Check if the firewall is blocking the connection.

Summary

In this section, we learned how to connect to a MySQL database using both MySQLi and PDO in PHP. We covered both procedural and object-oriented styles for MySQLi and discussed the advantages of using PDO for database interactions. This knowledge is fundamental for performing database operations in PHP.

Next, we will explore how to perform CRUD (Create, Read, Update, Delete) operations on a MySQL database using PHP.

Connecting to a MySQL Database

Key Concepts

Prerequisites

Connecting to MySQL using MySQLi

Procedural Style

Object-Oriented Style

Explanation

Connecting to MySQL using PDO

Explanation

Practical Exercise

Task

Solution

Common Mistakes

Summary

PHP Programming Course

Module 1: Introduction to PHP

Module 2: Control Structures

Module 3: Functions

Module 4: Arrays

Module 5: Working with Forms

Module 6: Working with Files

Module 7: Object-Oriented Programming (OOP)

Module 8: Working with Databases

Module 9: Advanced PHP Techniques

Module 10: PHP Frameworks and Best Practices

Module 11: Project: Building a Web Application