Skip to content
Related Articles

Related Articles

Improve Article

PHP – MySQL : INTERSECTION operation

  • Last Updated : 25 Mar, 2021

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 :

  1. MySQL – 
    It is a database query language to manage databases. PHP is a server-side programming language to maintain and manipulate databases.
     
  2. 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 namemarksaddress
1sravan kumar98kakumanu
2bobby90kakumanu
3ojaswi89hyderabad
4rohith90hyderabad
5gnanesh87hyderabad

Table-2: Subject table –

sidstu_namesub_name
1sravan kumarMaths
7ramyasocial
2bobbyMaths
3ojaswisocial
6ravihindi

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
#NameType
1idint(2)
2namevarchar(122)
3marksint(2)
4addressvarchar(211)

Table structure -subject :

Table structure -subject
#NameType
1sidint(2)
2stu_namevarchar(233)
3sub_namevarchar(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 –

sidstu_namesub_name
1sravan kumarMaths
7ramyasocial
2bobbyMaths
3ojaswisocial
6ravihindi

Table-2: Student table –

id namemarksaddress
1sravan kumar98kakumanu
2bobby90kakumanu
3ojaswi89hyderabad
4rohith90hyderabad
5gnanesh87hyderabad

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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.




My Personal Notes arrow_drop_up
Recommended Articles
Page :