MySQL Database

What is a Database?

A database is a structured collection of related data that allows efficient storage, retrieval, insertion, and deletion of information. It organizes data into tables, views, schemas, reports, etc., facilitating streamlined data management. For instance, a university database may organize data about students, faculty, and administration, which enables effective data operations such as retrieval and insertion.

To create a database in MySQL, a SQL query is needed. Here are the steps to create a MySQL database using PHP:

1. Establish a connection to the MySQL server from the PHP script.
2. If the connection is successful, write a SQL query to create a database and store it in a string variable.
3. Execute the query.

We already know how to establish a connection and create variables in PHP. The query can be executed in three different ways, as shown below:

1. Using MySQLi Object-Oriented Approach: If the MySQL connection is made using the object-oriented approach, the query() method of the mysqli class is used to run the query.

Syntax:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

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

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

// SQL query to create a database named newDB
$sql = "CREATE DATABASE newDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully with the name newDB";
} else {
    echo "Error creating database: " . $conn->error;
}

// Closing connection
$conn->close();
?>

Output:

Database created successfully with the name newDB

2. Using MySQLi Procedural Approach: If the MySQL connection is established using the procedural approach, the mysqli_query() function is used to execute the query.

Syntax:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

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

// Checking connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL query to create a database named newDB
$sql = "CREATE DATABASE newDB";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully with the name newDB";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}

// Closing connection
mysqli_close($conn);
?>

Output:

Database created successfully with the name newDB

3. Using PDO Approach: If the connection is made using PDO, the query can be executed as shown below.

Syntax:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
    $conn = new PDO("mysql:host=$servername;dbname=newDB", $username, $password);
    // Setting PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query to create a database named newDB
    $sql = "CREATE DATABASE newDB";

    // Executing the query using exec() as no results are returned
    $conn->exec($sql);
    echo "Database created successfully with the name newDB";
}
catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

// Closing the connection
$conn = null;
?>

Output:

Database created successfully with the name newDB

The collection of related data is called a database.

XAMPP is an abbreviation for Cross-Platform, Apache, MySQL, PHP, and Perl. It is a lightweight and straightforward local server used for website development.

Requirements:

Procedure to use the XAMPP web server:

1. Start the XAMPP server by running Apache and MySQL.
2. Write a PHP script for connecting to the XAMPP server.
3. Run the script in your local browser.
4. A database is created based on the PHP code.

In PHP, we can connect to a database using the XAMPP server by navigating to the following path:

“localhost/phpmyadmin”

Steps in Detail:

1. Open XAMPP and start the Apache, MySQL, and FileZilla services.
2. Now open your PHP file and write the PHP code to create a database and a table.

PHP Code to Create a Database:

<?php

// Server name is typically localhost
$servername = "localhost";

// Username for XAMPP is generally root
$username = "root";

// No password by default in XAMPP
$password = "";

// Establishing a connection
$conn = new mysqli($servername, $username, $password);

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

// SQL to create a database named myDB
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "Database 'myDB' created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

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

Steps to Follow:

• Save the file as “create_database.php” in the htdocs folder located in your XAMPP installation directory.
• Open your web browser and navigate to localhost/create_database.php.

Final Step:

• The database will be created and connected to PHP.
• To view your newly created database, type localhost/phpmyadmin in your web browser, and the database will appear.

Output:

Database 'myDB' created successfully

MySQL can run on a server and is well-suited for both small and large applications. It is known for being fast, reliable, and easy to use. Additionally, it supports SQL standards and is available on multiple platforms.

How to Connect PHP with MySQL?

Starting with PHP 5, there are two main ways to interact with MySQL databases:

• MySQLi extension (where “i” stands for “improved”)
• PDO (PHP Data Objects)

Should You Use MySQLi or PDO?

Both MySQLi and PDO have their advantages:

• PDO works with 12 different types of databases, whereas MySQLi is specifically designed to work only with MySQL databases.
• If you foresee switching to another database system, PDO simplifies the transition by only requiring changes to the connection string and some queries. With MySQLi, you would need to rewrite the entire codebase, including queries.
• Both provide object-oriented interfaces, though MySQLi also offers a procedural API.

In short, if you are only using MySQL, both are good options, but if you may switch databases, PDO is more flexible.

Connecting PHP to MySQL

Using MySQLi (Object-Oriented): Here is how you can establish a connection to a MySQL database using MySQLi in object-oriented style:

<?php
$server = "localhost";
$user = "admin";
$pass = "admin123";

// Establishing the connection
$connection = new mysqli($server, $user, $pass);

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

echo "Connection successful!";
?>

Output:

Connection successful!

Using MySQLi (Procedural): The procedural approach to connect PHP with MySQL using MySQLi looks like this:

<?php
$server = "localhost";
$user = "admin";
$pass = "admin123";

// Establishing the connection
$connection = mysqli_connect($server, $user, $pass);

// Checking the connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connection successful!";
?>

Output:

Connection successful!

What is a Table?

In relational and flat file databases, a table refers to an organized structure where data elements are stored in the form of vertical columns and horizontal rows. Each row represents a data record, and each column represents a specific field. A table has a fixed number of columns but can have an unlimited number of rows. The point where a row and column meet is known as a cell.

Creating a MySQL Table Using MySQLi and PDO

The process of creating a table is similar to creating a database. However, instead of creating a new database, you will connect to an existing database and define a table within it. To connect to an existing database, you need to specify the database name during the connection to MySQL.

The CREATE TABLE statement is used to define a new table.

In the following example, we will create a table named “customers” with four columns: customer_id, first_name, last_name, and email.

The data types used are:

• VARCHAR: Holds a string of variable length, containing letters, numbers, and special characters. The maximum size is defined in parentheses.
• INT: Holds integer values, ranging between -2147483648 to 2147483647.

Attributes used:

• NOT NULL: Ensures that every row must have a value in the column.
• PRIMARY KEY: Uniquely identifies each row in the table. Often applied to an ID column.

Creating a Table Using MySQLi Object-Oriented Procedure

<?php
$server = "localhost";
$user = "root";
$pass = "";
$dbname = "shopDB";

// Establishing connection
$conn = new mysqli($server, $user, $pass, $dbname);

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

// SQL code to create the table
$sql = "CREATE TABLE customers (
        customer_id INT(3) PRIMARY KEY,
        first_name VARCHAR(40) NOT NULL,
        last_name VARCHAR(40) NOT NULL,
        email VARCHAR(60)
        )";

if ($conn->query($sql) === TRUE) {
    echo "Table customers created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

// Closing connection
$conn->close();
?>

Creating a Table Using MySQLi Procedural Procedure

<?php
$server = "localhost";
$user = "root";
$pass = "";
$dbname = "shopDB";

// Establishing connection
$conn = mysqli_connect($server, $user, $pass, $dbname);

// Checking connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL code to create the table
$sql = "CREATE TABLE customers (
        customer_id INT(3) PRIMARY KEY,
        first_name VARCHAR(40) NOT NULL,
        last_name VARCHAR(40) NOT NULL,
        email VARCHAR(60)
        )";

if (mysqli_query($conn, $sql)) {
    echo "Table customers created successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}

// Closing connection
mysqli_close($conn);
?>

Output:

Table customers created successfully

Creating a Table Using PDO

<?php
$server = "localhost";
$user = "root";
$pass = "";
$dbname = "shopDB";

try {
    // Establishing connection
    $conn = new PDO("mysql:host=$server;dbname=$dbname", $user, $pass);

    // Setting PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL code to create the table
    $sql = "CREATE TABLE customers (
            customer_id INT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(40) NOT NULL,
            last_name VARCHAR(40) NOT NULL,
            email VARCHAR(60)
            )";

    // Using exec() as no results are returned
    $conn->exec($sql);
    echo "Table customers created successfully";
}
catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

// Closing connection
$conn = null;
?>

Output:

Table customers created successfully

The DELETE query is used to remove records from a table in a database. It is often used with a SELECT statement to delete only those rows that meet a certain condition.

Syntax:

The basic syntax for a DELETE query is as follows:

Example Implementation:

Let us consider a table called Employees with four columns: EmployeeID, FirstName, LastName, and Position. Below are examples of how to delete the record of an employee whose EmployeeID is 101 from the Employees table using different MySQLi and PDO methods.

1. DELETE Query Using MySQLi Procedural Method:

<?php
$link = mysqli_connect("localhost", "root", "", "CompanyDB");

if ($link === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "DELETE FROM Employees WHERE EmployeeID = 101";
if (mysqli_query($link, $sql)) {
    echo "Record deleted successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

mysqli_close($link);
?>

Output:

This code will delete the record where the EmployeeID is 101 from the Employees table. After execution, a confirmation message will be displayed: “Record deleted successfully.”

2. SELECT Query Using MySQLi Object-Oriented Method:

<?php
$mysqli = new mysqli("localhost", "root", "", "SampleDB");

if ($mysqli === false) {
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}

$sql = "SELECT * FROM Users";
if ($res = $mysqli->query($sql)) {
    if ($res->num_rows > 0) {
        echo "<table>";
        echo "<tr>";
        echo "<th>FirstName</th>";
        echo "<th>LastName</th>";
        echo "<th>Age</th>";
        echo "</tr>";
        while ($row = $res->fetch_array()) {
            echo "<tr>";
            echo "<td>".$row['FirstName']."</td>";
            echo "<td>".$row['LastName']."</td>";
            echo "<td>".$row['Age']."</td>";
            echo "</tr>";
        }
        echo "</table>";
        $res->free();
    } else {
        echo "No records found.";
    }
} else {
    echo "ERROR: Could not execute $sql. " . $mysqli->error;
}
$mysqli->close();
?>

Output:

Similar to the previous example, this code will output the data in an HTML table format. If there are no matching records, it will show “No records found.”

The WHERE clause is used to filter records based on a specific condition provided by the user. Essentially, it helps restrict the number of rows returned or affected by a SELECT, UPDATE, or DELETE query.

Syntax:

The basic syntax for using the WHERE clause is as follows:

SELECT Column1, Column2, ...
FROM Table_Name
WHERE Condition;

Example of WHERE Clause:

Let’s consider a table called Students with three columns: FirstName, LastName, and Age.

To retrieve all the rows where the FirstName is “John”, we will use the following code examples:

1. WHERE Clause Using MySQLi Procedural Method:

<?php
$link = mysqli_connect("localhost", "root", "", "SchoolDB");

if ($link === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "SELECT * FROM Students WHERE FirstName='John'";
if ($res = mysqli_query($link, $sql)) {
    if (mysqli_num_rows($res) > 0) {
        echo "<table>";
        echo "<tr>";
        echo "<th>FirstName</th>";
        echo "<th>LastName</th>";
        echo "<th>Age</th>";
        echo "</tr>";
        while ($row = mysqli_fetch_array($res)) {
            echo "<tr>";
            echo "<td>" . $row['FirstName'] . "</td>";
            echo "<td>" . $row['LastName'] . "</td>";
            echo "<td>" . $row['Age'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        mysqli_free_result($res);
    } else {
        echo "No matching records found.";
    }
} else {
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

mysqli_close($link);
?>

Output:

This code fetches all records from the Students table where the FirstName is “John.” The matching rows are displayed in a table format.

2. WHERE Clause Using MySQLi Object-Oriented Method:

<?php
$mysqli = new mysqli("localhost", "root", "", "SchoolDB");

if ($mysqli === false) {
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}

$sql = "SELECT * FROM Students WHERE FirstName='John'";
if ($res = $mysqli->query($sql)) {
    if ($res->num_rows > 0) {
        echo "<table>";
        echo "<tr>";
        echo "<th>FirstName</th>";
        echo "<th>LastName</th>";
        echo "<th>Age</th>";
        echo "</tr>";
        while ($row = $res->fetch_array()) {
            echo "<tr>";
            echo "<td>" . $row['FirstName'] . "</td>";
            echo "<td>" . $row['LastName'] . "</td>";
            echo "<td>" . $row['Age'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        $res->free();
    } else {
        echo "No matching records found.";
    }
} else {
    echo "ERROR: Could not execute $sql. " . $mysqli->error;
}

$mysqli->close();
?>

Output:

In this example, the object-oriented method retrieves all the rows from the Students table where the FirstName is “John” and displays them in a table format.

3. WHERE Clause Using PDO Method:

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=SchoolDB", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    $sql = "SELECT * FROM Students WHERE FirstName='John'";
    $res = $pdo->query($sql);
    if ($res->rowCount() > 0) {
        echo "<table>";
        echo "<tr>";
        echo "<th>FirstName</th>";
        echo "<th>LastName</th>";
        echo "<th>Age</th>";
        echo "</tr>";
        while ($row = $res->fetch()) {
            echo "<tr>";
            echo "<td>" . $row['FirstName'] . "</td>";
            echo "<td>" . $row['LastName'] . "</td>";
            echo "<td>" . $row['Age'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        unset($res);
    } else {
        echo "No matching records found.";
    }
} catch (PDOException $e) {
    die("ERROR: Could not execute $sql. " . $e->getMessage());
}

unset($pdo);
?>

Output:

The PDO example runs a query to select all rows from the Students table where the FirstName is “John.” The results are displayed in a table format.

The MySQL UPDATE query is utilized to modify existing records in a table within a MySQL database.

It allows updating one or more fields simultaneously and can be used in conjunction with the WHERE clause to specify conditions for the update.

Syntax:

The basic syntax for the UPDATE query is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example of WHERE Update Query:

Let’s consider a table called Employees with columns ID, FirstName, LastName, and Age.

To update the Age of an employee with ID 105 in the Employees table, we can use the following code:

1. Update Query Using MySQLi Procedural Method:

<?php
$link = mysqli_connect("localhost", "root", "", "CompanyDB");

if ($link === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "UPDATE Employees SET Age='32' WHERE ID=105";
if (mysqli_query($link, $sql)) {
    echo "Record was updated successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

mysqli_close($link);
?>

Output:

This code updates the Age of the employee with ID 105 to 32. If successful, a message is displayed indicating the update.

2. Update Query Using MySQLi Object-Oriented Method:

<?php
$mysqli = new mysqli("localhost", "root", "", "CompanyDB");

if ($mysqli === false) {
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}

$sql = "UPDATE Employees SET Age='32' WHERE ID=105";
if ($mysqli->query($sql) === true) {
    echo "Record was updated successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . $mysqli->error;
}

$mysqli->close();
?>

Output:

In this example, the Age of the employee with ID 105 is updated to 32. A success message is shown if the query is executed without errors.

3. Update Query Using PDO Method:

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=CompanyDB", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    $sql = "UPDATE Employees SET Age='32' WHERE ID=105";
    $pdo->exec($sql);
    echo "Record was updated successfully.";
} catch (PDOException $e) {
    die("ERROR: Could not execute $sql. " . $e->getMessage());
}

unset($pdo);
?>

Output:

This PDO method updates the Age of the employee with ID 105 to 32, and a confirmation message is displayed upon successful execution.

In MySQL, the LIMIT clause is used in conjunction with the SELECT statement to control the number of rows returned in the result set. The LIMIT clause accepts either one or two arguments: offset and count. Both values must be non-negative integers.

• Offset: This specifies the position of the first row to return.
• Count: This defines the maximum number of rows to return.

When two parameters are used, the first one represents the offset and the second indicates the count. If only one parameter is provided, it represents the number of rows to return from the start of the result set.

Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

Example:

Let’s consider the following table named Employees, which contains three columns: FirstName, LastName, and Age.

To retrieve the first two rows from the Employees table, use the following query:

SELECT * FROM Employees LIMIT 2;

To fetch rows starting from the second row and retrieve two rows (rows 2 and 3 inclusive), use the following query:

SELECT * FROM Employees LIMIT 1, 2;

Below is the PHP implementation of the query to display the first two rows from the Employees table using the LIMIT clause in both procedural and object-oriented methods:

1. Limit Clause Using MySQLi Procedural Method:

<?php
$link = mysqli_connect("localhost", "root", "", "CompanyDB");

if ($link === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "SELECT * FROM Employees LIMIT 2";
if ($res = mysqli_query($link, $sql)) {
    if (mysqli_num_rows($res) > 0) {
        echo "<table>";
        echo "<tr>";
        echo "<th>FirstName</th>";
        echo "<th>LastName</th>";
        echo "<th>Age</th>";
        echo "</tr>";
        while ($row = mysqli_fetch_array($res)) {
            echo "<tr>";
            echo "<td>" . $row['FirstName'] . "</td>";
            echo "<td>" . $row['LastName'] . "</td>";
            echo "<td>" . $row['Age'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        mysqli_free_result($res);
    } else {
        echo "No matching records were found.";
    }
} else {
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

mysqli_close($link);
?>

Output:

This code retrieves and displays the first two rows from the Employees table, showing FirstName, LastName, and Age fields.