MySQL Database

PHP MySQL Database Introduction

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) widely used in conjunction with PHP. It is currently one of the most popular database systems and is developed, distributed, and maintained by Oracle Corporation.

Data within a MySQL database is organized into tables consisting of rows and columns.
MySQL operates as a server-based database system.
It is well-suited for both small and large-scale applications.
Known for its speed, reliability, and ease of use, MySQL uses standard SQL (Structured Query Language).
MySQL can run on various platforms.

Downloading MySQL Database

You can download MySQL for free from the official MySQL website.

How to Connect PHP to a MySQL Database?

In PHP version 5 and above, MySQL can be accessed using two primary methods:

MySQLi extension.
PDO (PHP Data Objects).

Difference Between MySQLi and PDO

PDO supports 12 different types of databases, while MySQLi is exclusively for MySQL databases.
Both PDO and MySQLi offer object-oriented interfaces, but MySQLi also provides a procedural interface.
If you need to switch your project from MySQL to another database system, PDO allows this with minimal changes to the connection string and a few queries. With MySQLi, you would have to rewrite the entire codebase, including all queries.

Ways to Work with MySQL in PHP

There are three primary methods to connect and interact with a MySQL database in PHP:

1. MySQLi (Object-Oriented)
2. MySQLi (Procedural)
3. PDO (PHP Data Objects)

Connecting to a MySQL Database using PHP

Below are the three different approaches to connect to a MySQL database from a PHP script.

1. MySQLi Object-Oriented Approach: You can use the object-oriented approach with MySQLi to create a connection to the MySQL database.

Syntax:

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

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

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

Output:

				
					Connected successfully

2. MySQLi Procedural Approach: You can also establish a connection using MySQLi’s procedural interface.

Syntax:

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

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

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

Output:

				
					Connected successfully

3. PDO Approach: PDO offers a unified interface for connecting to a MySQL database and can be easily adapted for other database systems.

Syntax:

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

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

Output:

				
					Connected successfully

PHP MySQL ( Creating 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

PHP Database connection

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

Connect PHP to MySQL

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!

PHP MySQL ( Creating Table )

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

PHP Inserting into MySQL database

Inserting Data into a MySQL Database Using PHP

Inserting data into a MySQL database using PHP is a key task in many web applications. This allows developers to dynamically manage and store data, such as user inputs, for a wide variety of tasks like content management, e-commerce, and more.

In this section, we’ll explore how to insert records into a MySQL database using PHP. The process involves creating a connection to the database, writing an SQL INSERT statement, executing it, and handling any potential errors.

Inserting Data Using MySQLi (Object-Oriented Approach)

Let’s assume we are inserting a new record into a MySQL table named people. The objective is to add the name, surname, and age of an individual.

				
					<?php
$mysqli = new mysqli("localhost", "admin", "password123", "mydatabase");

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

// Insert data
$sql = "INSERT INTO people (name, surname, age) 
        VALUES ('John', 'Doe', '30')";

if ($mysqli->query($sql) === true) {
    echo "Record inserted successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

Output:

				
					Record inserted successfully.

Inserting Data Using MySQLi (Procedural Approach)

In this case, we are inserting data into the people table using the MySQLi procedural method. The values to be added are Anna, Smith, and 24 for name, surname, and age, respectively.

				
					<?php
$link = mysqli_connect("localhost", "admin", "password123", "mydatabase");

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

// Insert data
$sql = "INSERT INTO people (name, surname, age) 
        VALUES ('Anna', 'Smith', '24')";

if (mysqli_query($link, $sql)) {
    echo "Record added successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

Output:

				
					Record added successfully.

Inserting Data Using PDO

In this example, we are inserting data into the people table using the PDO method. The script will handle any errors that might occur during the connection or execution phases.

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

try {
    // Insert data
    $sql = "INSERT INTO people (name, surname, age) 
            VALUES ('David', 'Lee', '28')";
    $pdo->exec($sql);
    echo "Record inserted successfully.";
} catch (PDOException $e) {
    die("ERROR: Could not execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

Output:

				
					Record inserted successfully.

Inserting Multiple Rows into a Table

You can also insert multiple rows into a table with a single INSERT query. To do this, add multiple sets of values within the query, where each set is enclosed in parentheses and separated by commas.

Inserting Multiple Rows Using MySQLi (Object-Oriented Approach)

				
					<?php
$mysqli = new mysqli("localhost", "admin", "password123", "mydatabase");

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

// Insert multiple rows
$sql = "INSERT INTO people (name, surname, age) 
        VALUES ('Alice', 'Brown', '22'), 
               ('Tom', 'Harris', '35'), 
               ('Emily', 'Clark', '27'), 
               ('Luke', 'Morris', '31')";

if ($mysqli->query($sql) === true) {
    echo "Records inserted successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

Output:

				
					Records inserted successfully.

Inserting Multiple Rows Using MySQLi (Procedural Approach)

				
					<?php
$link = mysqli_connect("localhost", "admin", "password123", "mydatabase");

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

// Insert multiple rows
$sql = "INSERT INTO people (name, surname, age) 
        VALUES ('Michael', 'Smith', '40'), 
               ('Nina', 'Johnson', '29'), 
               ('Chris', 'Evans', '33'), 
               ('Sophia', 'Martinez', '24')";

if (mysqli_query($link, $sql)) {
    echo "Records added successfully.";
} else {
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

Output:

				
					Records added successfully.

Inserting Multiple Rows Using PDO

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

try {
    // Insert multiple rows
    $sql = "INSERT INTO people (name, surname, age) 
            VALUES ('Olivia', 'Taylor', '21'), 
                   ('James', 'Wilson', '37'), 
                   ('Isabella', 'Garcia', '30'), 
                   ('Liam', 'White', '45')";
    $pdo->exec($sql);
    echo "Records inserted successfully.";
} catch (PDOException $e) {
    die("ERROR: Could not execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

Output:

				
					Records inserted successfully.

PHP MySQL Delete Query

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.”

PHP MySQL WHERE Clause

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.

PHP MySQL UPDATE Query

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.

PHP MySQL LIMIT Clause

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.

Contents

MySQL Database

PHP MySQL Database Introduction

What is MySQL?

Downloading MySQL Database

How to Connect PHP to a MySQL Database?

Difference Between MySQLi and PDO

Ways to Work with MySQL in PHP

Connecting to a MySQL Database using PHP

PHP MySQL ( Creating Database )

What is a Database?

PHP Database connection

The collection of related data is called a database.

Connect PHP to MySQL

How to Connect PHP with MySQL?

Should You Use MySQLi or PDO?

Connecting PHP to MySQL

PHP MySQL ( Creating Table )

What is a Table?

Creating a MySQL Table Using MySQLi and PDO

PHP Inserting into MySQL database

Inserting Data into a MySQL Database Using PHP

Inserting Data Using MySQLi (Object-Oriented Approach)

Inserting Data Using MySQLi (Procedural Approach)

Inserting Data Using PDO

Inserting Multiple Rows into a Table

PHP MySQL Delete Query

PHP MySQL WHERE Clause

PHP MySQL UPDATE Query

PHP MySQL LIMIT Clause

Related Chapters

CATEGORY

ABOUT US

GET IN TOUCH

FOLLOW US