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 = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "gfg" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
if ( $conn ->connect_error) {
die ( "Connection failed: " . $conn ->connect_error);
}
$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 = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "gfg" ;
$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 = "SELECT * FROM college1 WHERE sid IN (SELECT sid FROM college1)" ;
$result = $conn ->query( $sql );
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 = "SELECT * FROM college1 WHERE sid IN (SELECT sid where sid < 4)" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo " student id: " . $row [ 'sid' ], " - student name: " . $row [ 'sname' ], " - student address: " . $row [ 'saddress' ];
echo "<br>" ;
}
$conn ->close();
?>
</body>
</html>
|
Output :
Example 2
form1.php
PHP
<html>
<body>
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "gfg" ;
$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 = "SELECT sid FROM college1 WHERE sid IN (SELECT sid FROM college1)" ;
$result = $conn ->query( $sql );
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 = "SELECT sname FROM college1 WHERE sid IN (SELECT sid FROM college1)" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo " student name: " . $row [ 'sname' ];
echo "<br>" ;
}
$conn ->close();
?>
</body>
</html>
|
Output :
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...