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 :
