In this article, we are going to perform a database operation that includes an intersection of two tables using PHP in xampp server. So, we are taking the student database.
Requirements –xampp server
Introduction :
- MySQL –
It is a database query language to manage databases. PHP is a server-side programming language to maintain and manipulate databases.
- INTERSECTION –
It is a useful operator and is used to combine SELECT queries like if you have two SELECT statements then from the first SELECT it will return rows only and that will be identical to a row in the second SELECT statement.
Syntax :
SELECT column1,column2,columnn FROM table1 [WHERE condition] INTERSECT SELECT column1,column2,columnn FROM table1 [, table2 ] [WHERE condition];
Example :
Consider the two tables as follows.
Table-1: Student table –
| id | name | marks | address |
|---|---|---|---|
| 1 | sravan kumar | 98 | kakumanu |
| 2 | bobby | 90 | kakumanu |
| 3 | ojaswi | 89 | hyderabad |
| 4 | rohith | 90 | hyderabad |
| 5 | gnanesh | 87 | hyderabad |
Table-2: Subject table –
| sid | stu_name | sub_name |
|---|---|---|
| 1 | sravan kumar | Maths |
| 7 | ramya | social |
| 2 | bobby | Maths |
| 3 | ojaswi | social |
| 6 | ravi | hindi |
Database Operation :
The intersection of two tables based on student id as follows.
SELECT id FROM student INTERSECT SELECT sid FROM subject
Result –
student id: 1 student id: 2 student id: 3
Approach :
Here, you will see the approach to implement the intersection operation as follows.
- Create database.
- Create tables.
- Write PHP code to insert records into the tables.
- Write PHP code to perform intersection operation.
Steps to implement intersection Operation :
- Start xampp server.
- Create database named test in xampp server and tables named student and subject
- Table structure will look as follows.
Table structure -student :
| Table structure -student | ||
|---|---|---|
| # | Name | Type |
| 1 | id | int(2) |
| 2 | name | varchar(122) |
| 3 | marks | int(2) |
| 4 | address | varchar(211) |
Table structure -subject :
| Table structure -subject | ||
|---|---|---|
| # | Name | Type |
| 1 | sid | int(2) |
| 2 | stu_name | varchar(233) |
| 3 | sub_name | varchar(233) |
PHP’s code to insert records into student table :
File name- student.php
PHP
<?php //servername $servername = "localhost"; //username $username = "root"; //empty password $password = ""; //test is the database name $dbname = "test"; // 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 student VALUES (1,'sravan kumar',98,'kakumanu');"; $sql .= "INSERT INTO student VALUES (2,'bobby',90,'kakumanu');"; $sql .= "INSERT INTO student VALUES (3,'ojaswi',89,'hyderabad');"; $sql .= "INSERT INTO student VALUES (4,'rohith',90,'hyderabad');"; $sql .= "INSERT INTO student VALUES (5,'gnanesh',87,'hyderabad');"; if ($conn->multi_query($sql) === TRUE) { echo "student data inserted successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?> |
Output :
Insert records into the subject table :
File name -subjects.php
PHP
<?php //servername $servername = "localhost"; //username $username = "root"; //empty password $password = ""; //test is the database name $dbname = "test"; // 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 subject VALUES (1,'sravan kumar','Maths');"; $sql .= "INSERT INTO subject VALUES (7,'ramya','social');"; $sql .= "INSERT INTO subject VALUES (2,'bobby','Maths');"; $sql .= "INSERT INTO subject VALUES (3,'ojaswi','social');"; $sql .= "INSERT INTO subject VALUES (6,'ravi','hindi');"; if ($conn->multi_query($sql) === TRUE) { echo "subject data inserted successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?> |
Output :
Now records in two tables are as follows.
Table-1: Subject table –
| sid | stu_name | sub_name |
|---|---|---|
| 1 | sravan kumar | Maths |
| 7 | ramya | social |
| 2 | bobby | Maths |
| 3 | ojaswi | social |
| 6 | ravi | hindi |
Table-2: Student table –
| id | name | marks | address |
|---|---|---|---|
| 1 | sravan kumar | 98 | kakumanu |
| 2 | bobby | 90 | kakumanu |
| 3 | ojaswi | 89 | hyderabad |
| 4 | rohith | 90 | hyderabad |
| 5 | gnanesh | 87 | hyderabad |
PHP code to perform intersection operation on id :
File name -form.php
PHP
<?php // code ?><html> <body> <?php //servername $servername = "localhost"; //username $username = "root"; //empty password $password = ""; //test is the database name $dbname = "test"; // Create connection by passing these connection parameters $conn = new mysqli($servername, $username, $password, $dbname); echo "<h1>"; echo "INTERSECTION Demo "; echo"</h1>"; echo "<br>"; echo "intersection of two tables based on student id"; echo "<br>"; echo "<br>"; //sql query $sql = "SELECT id FROM student INTERSECT SELECT sid FROM subject "; $result = $conn->query($sql); //display data on web page while($row = mysqli_fetch_array($result)){ echo " student id: ". $row['id']; echo "<br>"; } //close the connection $conn->close(); ?> </body> </html> |
Output :
localhost/form.php
