In this article, we are going to join two tables using PHP and display them on the web page.
Introduction :
PHP is a server-side scripting language, which is used to connect with databases. Using this, we can get data from the database using PHP scripts. The database language that can be used to communicate with PHP is MySQL. MySQL is a database query language that is used to manage databases.
Requirements :
Xampp server – xampp server is used to store our database locally. We are going to access the data from xampp server using PHP.
In this article, we are taking the student details database that contains two tables. They are student_address and student_marks.
Structure of tables :
table1=student_address
table2=student_marks.
We are going to perform INNER JOIN, LEFT JOIN, RIGHT JOIN on these two tables.
1. INNER JOIN :
The INNER JOIN is a keyword that selects records that have matching values in both tables.
Syntax :
SELECT column 1,column 2,...column n
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Example :
Let student_address contains these details
And student_marks table includes
By using sid, we can join these two tables using an Inner join, since, sid is common in two tables.
- Query to display student_address details based on inner join –
SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;
Result :
STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu
STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu
STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad
STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad
- Query to display student_marks details based on inner join.
SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid
Result :
STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99
STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89
STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98
STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98
2. LEFT JOIN :
The LEFT JOIN keyword is used to return all records from the left table (table1), and the matching records from the right table (table2).
Syntax :
SELECT column1,column2,...columnn
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
- Query to display all student_address table based on student id using left join
SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid
Result :
STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu
STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu
STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad
STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad
STUDENT-ID : ----- NAME : gnanesh ----- ADDRESS : hyderabad
- Query to display all student_marks table based on student id using left join
SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid
Result :
STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99
STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89
STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98
STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98
STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 79
3. RIGHT JOIN :
The RIGHT JOIN keyword is used to return all records from the right table (table2), and the matching records from the left table (table1).
Syntax :
SELECT column1,column2,...columnn
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
- Query to display all student_address table based on student id using right join
SELECT * from student_address RIGHT JOIN student_marks on student_address.sid=student_marks.sid
Result :
STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu
STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu
STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad
STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad
STUDENT-ID : 7 ----- NAME : ----- ADDRESS :
- Query to display all student_marks table based on student id using right join
SELECT * from student_marks RIGHT JOIN student_address on student_address.sid=student_marks.sid
Result :
STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99
STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89
STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98
STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98
STUDENT-ID : 5 ----- SUBJECT 1 : ----- SUBJECT 2 :
Approach :
- Create a database named database and create tables(student_address and student_marks)
- Insert records into two tables using PHP
- Write SQL query to perform all joins using PHP
- Observe the results.
Steps:
- Type “localhost/phpmyadmin” in your browser and create a database named “database” then create two tables named student_address and student_marks
Student_address table structure :
Student_marks table structure :
- Insert the records into the student_address table using PHP (data1.php) Run code by typing “localhost/data1.php”
PHP
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO student_address VALUES (1,'sravan kumar','kakumanu');";
$sql .= "INSERT INTO student_address VALUES (2,'bobby','kakumanu');";
$sql .= "INSERT INTO student_address VALUES (3,'ojaswi','hyderabad');";
$sql .= "INSERT INTO student_address VALUES (4,'rohith','hyderabad');";
$sql .= "INSERT INTO student_address VALUES (5,'gnanesh','hyderabad');";
if ($conn->multi_query($sql) === TRUE) {
echo "data stored successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
|
Output :
Write PHP code to insert details in the student_marks table. (data2.PHP)
PHP
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO student_marks VALUES (1,98,99);";
$sql .= "INSERT INTO student_marks VALUES (2,78,89);";
$sql .= "INSERT INTO student_marks VALUES (3,78,98);";
$sql .= "INSERT INTO student_marks VALUES (4,89,98);";
$sql .= "INSERT INTO student_marks VALUES (7,89,79);";
if ($conn->multi_query($sql) === TRUE) {
echo "data stored successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
|
Output :
Type “localhost/data2.php” to see the output
Write PHP code to perform inner join (form.php)
PHP
<html>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);
echo "inner join on student_address: ";
echo "<br>";
echo "<br>";
$sql = "SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid";
$result = $conn->query($sql);
while($row = mysqli_fetch_array($result)){
echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
echo "<br>";
}
echo "<br>";
echo "inner join on student_marks: ";
echo "<br>";
echo "<br>";
$sql1 = "SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid";
$result1 = $conn->query($sql1);
while($row = mysqli_fetch_array($result1)){
echo " STUDENT-ID : ". $row['sid'], " ----- SUBJECT 1 : ". $row['subject1'] ," ----- SUBJECT 2 : ". $row['subject2'] ;
echo "<br>";
}
$conn->close();
?>
</body>
</html>
|
Output :
Type “localhost/form.php” in your browser.
Write code to perform right join (form1.php)
PHP
<html>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);
echo "right join on student_address: ";
echo "<br>";
echo "<br>";
$sql = "SELECT * from student_address RIGHT JOIN student_marks on student_address.sid=student_marks.sid";
$result = $conn->query($sql);
while($row = mysqli_fetch_array($result)){
echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
echo "<br>";
}
echo "<br>";
echo "right join on student_marks: ";
echo "<br>";
echo "<br>";
$sql1 = "SELECT * from student_marks RIGHT JOIN student_address on student_address.sid=student_marks.sid";
$result1 = $conn->query($sql1);
while($row = mysqli_fetch_array($result1)){
echo " STUDENT-ID : ". $row['sid'], " ----- SUBJECT 1 : ". $row['subject1'] ," ----- SUBJECT 2 : ". $row['subject2'] ;
echo "<br>";
}
$conn->close();
?>
</body>
</html>
|
Output :
Type “localhost/form1.php” in your browser.
Write PHP code to perform left join (form2.php)
PHP
<html>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);
echo "left join on student_address: ";
echo "<br>";
echo "<br>";
$sql = "SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid";
$result = $conn->query($sql);
while($row = mysqli_fetch_array($result)){
echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
echo "<br>";
}
echo "<br>";
echo "left join on student_marks: ";
echo "<br>";
echo "<br>";
$sql1 = "SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid";
$result1 = $conn->query($sql1);
while($row = mysqli_fetch_array($result1)){
echo " STUDENT-ID : ". $row['sid'], " ----- SUBJECT 1 : ". $row['subject1'] ," ----- SUBJECT 2 : ". $row['subject2'] ;
echo "<br>";
}
$conn->close();
?>
</body>
</html>
|
Output :
type localhost/form2.php in browser
Similar Reads
PHP | mysqli_close() Function
MySQLi Procedural procedure: To close the connection in mysql database we use php function mysqli_close() which disconnect from database. It require a parameter which is a connection returned by the mysql_connect function. Syntax: mysqli_close(conn); If the parameter is not specified in mysqli_close
2 min read
PHP | mysqli_error() Function
The mysqli_error() function is used to return the error in the most recent MySQL function call that failed. If there are multiple MySQL function calls, the error in the last statement is the one that is pointed out by the function. Syntax: mysqli_error("database_name") Parameters: This function acce
1 min read
SQL Self Join
A Self Join in SQL is a powerful technique that allows one to join a table with itself. This operation is helpful when you need to compare rows within the same table based on specific conditions. A Self Join is often used in scenarios where there is hierarchical or relational data within the same ta
4 min read
PHP | MySQL ( Creating Table )
What is a table? In relational databases, and flat file databases, a table is a set of data elements using a model of vertical columns and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. Creating a
3 min read
PHP | MySQL ( Creating Database )
What is a database? Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty,
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 | MySQL WHERE Clause
The WHERE Clause is used to filter only those records that are fulfilled by a specific condition given by the user. in other words, the SQL WHERE clause is used to restrict the number of rows affected by a SELECT, UPDATE or DELETE query. Syntax : The basic syntax of the where clause is - SELECT Colu
3 min read
PHP | MySQL LIMIT Clause
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count.The value of both the parameters can be zero or positive integers. Offset:It is used to specify the offset of the fir
3 min read
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
SQL LEFT JOIN
In SQL, LEFT JOIN retrieves all records from the left table and only the matching records from the right table. When there is no matching record found, NULL values are returned for columns from the right table. This makes LEFT JOIN extremely useful for queries where you need to retain all records fr
5 min read