PHP – MySQL : Nested Query

0 0 3 minutes read

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>