Skip to content
Related Articles

Related Articles

Improve Article

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 :

  • 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 :




My Personal Notes arrow_drop_up
Recommended Articles
Page :