Welcome to the advanced stage of our journey, where we delve into more sophisticated SQL techniques such as joins, nested queries, and data filtering. These powerful tools allow you to retrieve and manipulate data more effectively, providing deeper insights and better functionality in your web applications.
Setting the Scene
Imagine you’re a data archaeologist, piecing together fragments of information from different tables to uncover hidden stories. Joins, nested queries, and filtering are your essential tools for this task, allowing you to connect related data and extract meaningful insights.
A join allows you to combine rows from two or more tables based on a related column. There are different types of joins, but we’ll focus on the most common ones: INNER JOIN, LEFT JOIN, and RIGHT JOIN.
An INNER JOIN returns only the rows that have matching values in both tables.
Example:
Suppose we have two tables, students and courses, and we want to find out which courses each student is enrolled in.
SQL
Copy code
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_name VARCHAR(50),
FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO students (name) VALUES (‘Alice’), (‘Bob’), (‘Carol’);
INSERT INTO courses (student_id, course_name) VALUES (1, ‘Math’), (2, ‘Science’), (1, ‘History’);
SQL Query:
SQL
Copy code
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;
PHP Code:
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “school”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// SQL query with INNER JOIN
$sql = “SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “Student: ” . $row[“name”]. ” – Course: ” . $row[“course_name”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
A nested query (subquery) is a query within another query. It allows you to perform more complex searches and data manipulations.
Example:
Find the names of students who are enrolled in ‘Math’.
SQL Query:
SQL
Copy code
SELECT name FROM students
WHERE id IN (
SELECT student_id FROM courses WHERE course_name = ‘Math’
);
PHP Code:
php
Copy code
<?php
// SQL query with nested query
$sql = “SELECT name FROM students
WHERE id IN (
SELECT student_id FROM courses WHERE course_name = ‘Math’
)”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “Student: ” . $row[“name”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
Filtering data involves using the WHERE clause to specify conditions that must be met for the records to be included in the result set.
Example:
Retrieve students who are older than 20.
SQL Query:
SQL
Copy code
SELECT * FROM students WHERE age > 20;
PHP Code:
php
Copy code
<?php
// SQL query with WHERE clause
$sql = “SELECT * FROM students WHERE age > 20”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “ID: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
Here’s a complete PHP script that demonstrates joins, nested queries, and data filtering.
Database Setup:
SQL
Copy code
CREATE DATABASE school;
USE school;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_name VARCHAR(50),
FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO students (name, age) VALUES (‘Alice’, 23), (‘Bob’, 25), (‘Carol’, 22);
INSERT INTO courses (student_id, course_name) VALUES (1, ‘Math’), (2, ‘Science’), (1, ‘History’);
PHP Script:
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “school”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Inner Join Query
$sql = “SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id”;
$result = $conn->query($sql);
echo “<h2>Inner Join Results:</h2>”;
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “Student: ” . $row[“name”]. ” – Course: ” . $row[“course_name”]. “<br>”;
}
} else {
echo “0 results”;
}
// Nested Query
$sql = “SELECT name FROM students
WHERE id IN (
SELECT student_id FROM courses WHERE course_name = ‘Math’
)”;
$result = $conn->query($sql);
echo “<h2>Nested Query Results:</h2>”;
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “Student: ” . $row[“name”]. “<br>”;
}
} else {
echo “0 results”;
}
// Filtering Data
$sql = “SELECT * FROM students WHERE age > 20”;
$result = $conn->query($sql);
echo “<h2>Filtering Data Results:</h2>”;
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “ID: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
In this chapter, you’ve learned how to use PHP to execute more advanced SQL queries, including joins, nested queries, and data filtering. These techniques allow you to connect related data from different tables, perform complex searches, and filter results to meet specific criteria. With these skills, you’re well-equipped to handle a wide range of data retrieval tasks in your web applications. Keep practicing, and you’ll master these powerful SQL tools in no time. Happy coding!