PHP MySQL Database Introduction

MySQL is the most widely used database system in conjunction with PHP.

PHP provides the ability to connect to and interact with databases.

• MySQL is a web-friendly database management system.
• It operates seamlessly on server environments.
• Designed for versatility, it works well with applications of any size, from small-scale projects to enterprise-level solutions.
• Known for its speed, dependability, and user-friendly nature, MySQL is widely adopted.
• The system adheres to standard SQL protocols.
• It is compatible with various platforms, ensuring broad usability.
• MySQL is available as a free download, making it accessible to everyone.
• Oracle Corporation is responsible for its development, distribution, and support.
• The name "MySQL" was inspired by co-founder Monty Widenius's daughter, My.

In a MySQL database, data is organized into tables. Each table is a collection of related information, structured with columns and rows. Databases are essential for categorizing and storing data efficiently. 

For example, a company's database might include tables for:

• Staff 
• Inventory
• Clients
• Sales Orders

PHP + MySQL Database System

PHP and MySQL are cross-platform, allowing you to develop on Windows and deploy on a Unix-based server.

Database Queries

A query is a way to ask for or request specific data.

By querying a database, we can retrieve specific information and receive a set of matching records.

Example:

SELECT Name FROM Staff;

The query above retrieves all the entries from the "Name" column in the "Staff" table.

Facts About MySQL Database

MySQL is the go-to database system for websites handling massive volumes of data and high traffic, such as Facebook, Twitter, and Wikipedia.

One of the key advantages of MySQL is its flexibility; it can also be scaled down for embedded database applications.

Using MySQL Database

If you don’t have a PHP server with a MySQL database, you can download both for free from mysql.com.

Or check our tutorial: Install XAMPP with PHP and MySQL.

PHP - Connect to MySQL

In PHP 5 and later, you can interact with a MySQL database using:

• MySQLi extension, where "i" stands for improved
• PDO, which means PHP Data Objects

Earlier versions of PHP utilized the MySQL extension, but it was deprecated in 2012.

MySQLi vs PDO?

You can choose any option depending on your choice and requirement.

Both MySQLi and PDO come with their own advantages:

• PDO is versatile and supports 12 different database systems, whereas MySQLi is specifically designed for MySQL databases.
• If switching to another database system is a possibility in the future, PDO makes the transition easier—you’ll only need to modify the connection settings and adjust some queries. In contrast, MySQLi would require a complete overhaul of your codebase.
• Both options are object-oriented, though MySQLi also provides a procedural API for developers who prefer that style.
• Prepared Statements are available in both, offering protection against SQL injection and enhancing the security of your applications.

In the end, the decision depends on your specific needs and preferences.

Now we'll look at different examples to integrate PHP with MySQL by using the following techniques:

• MySQLi (Object-Oriented Style)
• MySQLi (Procedural Style)
• PDO (PHP Data Objects)

MySQLi Installation

Most Linux web servers, including shared hosting servers, come with pre-installed MySQLi extension. In our case, we are going to use the XAMPP package, which also provides MySQLi extension by default.

For XAMPP installation guide, refer to: XAMPP with PHP and MySQL.

Installing PDO

XAMPP also comes with PDO extension by default. For manual installation, check the official documentation: PDO Installation Guide.

Getting Starting

To start learning MySQL in PHP by utilizing XAMPP, follow these steps:

1. Create a new folder named mysqldemo in the htdocs folder inside the xampp directory.
2. Create a new file named index.php in the mysqldemo folder.
3. Now you'll be able to access the PHP file for testing MySQL in PHP at localhost/mysqldemo.
4. Now visit in your browser: localhost/phpmyadmin. Click on Databases tab in the top right corner, and create a new database named testDB by specifying the database name. For a clear idea, please watch the above video.

We'll use the above setup throughout this guide.

Connecting to MySQL

To interact with a MySQL database, the first step is to establish a connection to the server.

In case of XAMPP, the default username is root and the password is empty.

Object-Oriented Style Example (MySQLi):

<?php
$server = "localhost";
$username = "root";
$password = "";

// Create a new MySQLi connection
$connection = new mysqli($server, $username, $password);

// Verify the connection
if ($connection->connect_error) {
    exit("Failed to connect: " . $connection->connect_error);
}

echo "Connection established successfully!";
?>

/*
Output:
Connection established successfully!
*/

Important Note: The $connect_error property is not supported in PHP versions earlier than 5.2.9 or 5.3.0. To ensure compatibility with these older versions, you can use the alternative code provided below.

// Validate the connection
if (mysqli_connect_errno()) {
    exit("Unable to establish a database connection: " . mysqli_connect_errno());
}

Procedural Style Example (MySQLi):

<?php
$server = "localhost";
$username = "root";
$password = "";

// Establish the connection
$connection = mysqli_connect($server, $username, $password);

// Verify if the connection was successful
if ($connection === false) {
    exit("Unable to connect to the database: " . mysqli_connect_error());
}

echo "Connection established successfully!";
?>

/*
Output:
Connection established successfully!
*/

Object-Oriented Style Example (PDO):

<?php
$server = "localhost";
$username = "root";
$password = "";

try {
    // Attempt to establish a PDO connection
    $connection = new PDO("mysql:host=$server;dbname=myDB", $username, $password);
    
    // Set PDO to throw exceptions on errors
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Database connection successful!";
} catch (PDOException $exception) {
    // Handle connection failure
    echo "Failed to connect: " . $exception->getMessage();
}
?>

/*
Output:
Database connection successful!
*/

Closing the Connection

By default, the connection closes once the script execution ends. If you wish to close the connection before that, you can explicitly do so with the following code.

<?php
$connection->close(); // Object-Oreinted (MySQLi)
mysqli_close($connection); // Procedural (MySQLi)
$connection = null; // Object-Oriented (PDO)
?>