PHP – MySQL : Nested Query

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 :

Start Xampp server

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 :

Article Tags :

PHP

SQL

Web Technologies

Web Templates