PHP - MySQL : Nested Query
Last Updated :
31 Mar, 2021
In this article, we are going to perform nested query operations on the database in the MySQL server using the Xampp server.
Introduction :
PHP stands for hypertext preprocessor, which is a server-side scripting language and also used to handle database operations. We are a PHP xampp server to communicate with the database. The language used is MySQL. MySQL is a database query language that is used to manage databases. It communicates with PHP and manages the database, by performing some operations.
Nested Query :
It is also known as a subquery or we can say query used with in a query is known as a nested query. We are taking college_details data to perform nested queries.
Syntax :
SELECT column1,column2,column3,...,columnn
FROM table_name
WHERE column_name expression operator
( SELECT COLUMN_NAME ...n from TABLE_NAME WHERE ... );
The (SELECT COLUMN_NAME ...n from TABLE_NAME WHERE ... ); is an inner/subquery.
Example :
Consider the table.
- Nested query to get student all details based on sid,
SELECT * FROM college1 WHERE sid IN (SELECT sid FROM college1);
Result :
student id: 1 - student name: sravan kumar - student address: kakumanu
student id: 2 - student name: bobby - student address: kakumanu
student id: 3 - student name: ojaswi - student address: hyderabad
student id: 4 - student name: rohith - student address: hyderabad
student id: 5 - student name: gnanesh - student address: hyderabad
- Nested query to get student all details based on sid less than 4
SELECT * FROM college1 WHERE sid IN (SELECT sid where sid < 4);
Result :
student id: 1 - student name: sravan kumar - student address: kakumanu
student id: 2 - student name: bobby - student address: kakumanu
student id: 3 - student name: ojaswi - student address: hyderabad
Approach :
- Create a database and create a table in it.
- Write PHP code to insert data into it
- Write PHP code to perform nested queries.
Steps :
- Create a database named gfg and create table college1
college1 columns :
- Insert records into the table using PHP code
PHP
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//gfg is the database name
$dbname = "gfg";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
// Check this connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//insert records into table
$sql = "INSERT INTO college1 VALUES (1,'sravan kumar','kakumanu');";
$sql .= "INSERT INTO college1 VALUES (2,'bobby','kakumanu');";
$sql .= "INSERT INTO college1 VALUES (3,'ojaswi','hyderabad');";
$sql .= "INSERT INTO college1 VALUES (4,'rohith','hyderabad');";
$sql .= "INSERT INTO college1 VALUES (5,'gnanesh','hyderabad');";
if ($conn->multi_query($sql) === TRUE) {
echo "college 1 data inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Output :
Type "localhost/insert.php" in the browser to run the program.
Inserted data,
Write PHP code to perform the nested query
form.php
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//gfg is the database name
$dbname = "gfg";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "nested query Demo "; echo"</h1>";
echo "<br>";
echo "<h2>";echo "nested query to get student all details based on sid";echo "</h2>";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT * FROM college1 WHERE sid IN (SELECT sid FROM college1)";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " student id: ". $row['sid']," - student name: ". $row['sname']," - student address: ". $row['saddress'];
echo "<br>";
}
echo "<br>";
echo "<h2>";echo "nested query to get student all details based on sid less than 4";echo "</h2>";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT * FROM college1 WHERE sid IN (SELECT sid where sid < 4)";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " student id: ". $row['sid']," - student name: ". $row['sname']," - student address: ". $row['saddress'];
echo "<br>";
}
//close the connection
$conn->close();
?>
</body>
</html>
Output :
Example 2
form1.php
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//gfg is the database name
$dbname = "gfg";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "nested query Demo "; echo"</h1>";
echo "<br>";
echo "<h2>";echo "nested query to get student id";echo "</h2>";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT sid FROM college1 WHERE sid IN (SELECT sid FROM college1)";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " student id: ". $row['sid'];
echo "<br>";
}
echo "<br>";
echo "<h2>";echo "nested query to get student name ";echo "</h2>";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT sname FROM college1 WHERE sid IN (SELECT sid FROM college1)";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " student name: ". $row['sname'];
echo "<br>";
}
//close the connection
$conn->close();
?>
</body>
</html>
Output :
