In addition to basic CRUD (Create, Read, Update, Delete) operations, advanced queries play a crucial role in leveraging the full potential of PHP and MySQL integration. These queries involve more complex operations, such as joining multiple tables, aggregating data, and handling transactions.
2.4.1 JOIN Operations
Introduction:
JOIN operations are essential for retrieving data from multiple tables based on specified relationships. This is particularly useful when information is distributed across different tables, and you need to combine it for comprehensive analysis or display.
Types of JOINs:
INNER JOIN:
An INNER JOIN retrieves records that have matching values in both tables. It filters out unmatched records.
<?php
// Example of INNER JOIN
$sqlInnerJoin = "SELECT users.username, orders.order_id FROM users INNER JOIN orders ON users.user_id = orders.user_id";
$resultInnerJoin = mysqli_query($connection, $sqlInnerJoin);
?>
LEFT JOIN (or LEFT OUTER JOIN):
A LEFT JOIN retrieves all records from the left table and the matched records from the right table. Unmatched records on the right side will contain NULL values.
<?php
// Example of LEFT JOIN
$sqlLeftJoin = "SELECT users.username, orders.order_id FROM users LEFT JOIN orders ON users.user_id = orders.user_id";
$resultLeftJoin = mysqli_query($connection, $sqlLeftJoin);
?>
RIGHT JOIN (or RIGHT OUTER JOIN):
A RIGHT JOIN retrieves all records from the right table and the matched records from the left table. Unmatched records on the left side will contain NULL values.
<?php
// Example of RIGHT JOIN
$sqlRightJoin = "SELECT users.username, orders.order_id FROM users RIGHT JOIN orders ON users.user_id = orders.user_id";
$resultRightJoin = mysqli_query($connection, $sqlRightJoin);
?>
FULL JOIN (or FULL OUTER JOIN):
A FULL JOIN retrieves all records when there is a match in either the left or right table. Unmatched records in both tables will contain NULL values.
<?php
// Example of FULL JOIN
$sqlFullJoin = "SELECT users.username, orders.order_id FROM users FULL JOIN orders ON users.user_id = orders.user_id";
$resultFullJoin = mysqli_query($connection, $sqlFullJoin);
?>
2.4.2 Aggregation Functions
Aggregation functions are used for performing calculations on groups of rows, such as calculating the average, sum, minimum, or maximum of a set of values.
Examples:
<?php
// Example of aggregation functions
$sqlAvgPrice = "SELECT AVG(price) as average_price FROM products";
$resultAvgPrice = mysqli_query($connection, $sqlAvgPrice);
$sqlTotalOrders = "SELECT COUNT(order_id) as total_orders FROM orders";
$resultTotalOrders = mysqli_query($connection, $sqlTotalOrders);
?>
2.4.3 Subqueries
Subqueries are queries embedded within other queries. They are useful for complex conditions and operations.
Example:
<?php
// Example of a subquery
$sqlSubquery = "SELECT username FROM users WHERE user_id IN (SELECT user_id FROM orders)";
$resultSubquery = mysqli_query($connection, $sqlSubquery);
?>
2.4.4 Transactions
Transactions ensure the atomicity of database operations, meaning that either all changes are applied, or none are applied.
Example:
<?php
// Example of a transaction
mysqli_autocommit($connection, false);
$sql1 = "INSERT INTO orders (customer_id, total_amount) VALUES (1, 50)";
$sql2 = "UPDATE customers SET balance = balance - 50 WHERE customer_id = 1";
mysqli_query($connection, $sql1);
mysqli_query($connection, $sql2);
mysqli_commit($connection);
mysqli_autocommit($connection, true);
?>
hese queries are particularly valuable in scenarios where data is distributed across multiple tables, and sophisticated analysis or reporting is required.