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 :
- 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 :
- 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
Similar Reads
PHP - Mysql LIKE Operator
Problem Statement : In this article, we are going to display data using LIKE operator with SQL in Xampp server. Here we are going to consider the student address database as an example. Requirements: Xampp Introduction: PHP stands for hypertext preprocessor. It is used as a server-side scripting lan
6 min read
PHP - MySQL min(),max() aggregate operations
In this article, we are going to find minimum and maximum details from the table column using PHP from MySQL Xampp server. We are taking an example from employee database to find the minimum and maximum salary of the employee. Requirements -Xampp server Introduction : PHP - It stands for Hyper Text
4 min read
PHP - MYSQL : sum() operation
Problem Statement: In this article, we are going to perform sum() aggregate operation on our database using PHP with xampp server. So we are considering the food_order database and perform database sum() operation. Requirements: xampp Introduction: PHP stands for hypertext preprocessor which is a se
3 min read
PHP | mysqli_ping() Function
The mysqli_ping() function is used to ping a server connection. That is it is used to check if a host is reachable on an IP network or not. This function also tries to reconnect if an existing server connection is lost. To use this function, it is mandatory to first set up the connection with the My
2 min read
PHP mysqli_connect() Function
The mysqli_connect() function in PHP is a fundamental tool for establishing a connection to a MySQL database. This function is crucial for PHP applications that need to interact with MySQL databases, enabling them to execute queries, retrieve data, and perform various database operations.In this art
3 min read
PHP | Bitwise Operators
The Bitwise operators is used to perform bit-level operations on the operands. The operators are first converted to bit-level and then calculation is performed on the operands. The mathematical operations such as addition , subtraction , multiplication etc. can be performed at bit-level for faster p
5 min read
PHP | MySQL Database Introduction
What is MySQL? MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.MySQL
4 min read
PHP | mysqli_num_rows() Function
The mysqli_num_rows() function is an inbuilt function in PHP which is used to return the number of rows present in the result set. It is generally used to check if data is present in the database or not. To use this function, it is mandatory to first set up the connection with the MySQL database. Sy
2 min read
PHP | gmp_or() Function
The gmp_or() is an inbuilt function in PHP which is used to calculate the bitwise OR of two GMP numbers(GNU Multiple Precision : For large numbers). Syntax: gmp_or($num1, $num2) Parameters: This function accepts two GMP numbers, $num1, $num2 as mandatory parameters as shown in the above syntax. Thes
2 min read
POWER() Function in MySQL
POWER() function in MySQL is used to find the value of a number raised to the power of another number. It Returns the value of X raised to the power of Y. Syntax : POWER(X, Y) Parameter : This method accepts two parameter which are described below : X : It specifies the base number. Y : It specifies
3 min read