PHP MySQL - inner join

Let’s discuss how to perform INNER JOIN on MySQL databases through PHP in the XAMPP Server.

Consider the tables - hospital1, hospital2 present in the database - hospital snapshot with some records.

1. hospital1: 4 rows and 2 columns

2. hospital2: 6 rows and 3 columns

INNER JOIN

INNER JOIN is performed on two or more tables. It will return the rows that are similar in two or more tables.

SQL INNER - Syntax

SELECT columns
FROM FIRST_TABLE
INNER JOIN SECOND_TABLE
ON FIRST_TABLE.COLUMN = SECOND_TABLE.COLUMN;

FIRST_TABLE is the table1 and SECOND_TABLE is the table2.

COLUMN may or may not have similar values in two tables. Based on this column, Tables are joined.

Example 1-

Let's perform INNER JOIN on two tables - hospital1 and hospital2 based on ID Column and display ID, name from hospital1 and city and type from hospital2.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Perform Inner Join on hospital1 and hospital2 based on ID Column
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
INNER JOIN hospital2
ON hospital1.ID = hospital2.ID;";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "ID: " . $record_data["ID"]. " , Name: " . $record_data["name"]. " , City: " . $record_data["city"]. " , Type: " . $record_data["type"]. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

So we can see that only rows with ID 1,2 and 3 are present in both the tables. Hence they were returned.

It can be possible to use WHERE clause along with INNER JOIN.

Example 2:

Now we will perform INNER JOIN on hospital1 and hospital2 where name is 'General Hospital'.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Perform Inner Join on hospital1 and hospital2 based on ID Column WHERE name='General Hospital'
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
INNER JOIN hospital2
ON hospital1.ID = hospital2.ID
WHERE hospital1.name='General Hospital' ;";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "ID: " . $record_data["ID"]. " , Name: " . $record_data["name"]. " , City: " . $record_data["city"]. " , Type: " . $record_data["type"]. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

So the records with name - 'General Hospital' were returned.

Example 3:

Now we will perform INNER JOIN on hospital1 and hospital2 where ID > 2.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Perform Inner Join on hospital1 and hospital2 based on ID Column WHERE ID > 2
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
INNER JOIN hospital2
ON hospital1.ID = hospital2.ID
WHERE hospital1.ID > 2 ;";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "ID: " . $record_data["ID"]. " , Name: " . $record_data["name"]. " , City: " . $record_data["city"]. " , Type: " . $record_data["type"]. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

We can see similar rows with ID greater than 3 were returned.

Conclusion

So by the end of this article, we saw how to perform PHP-MySQL query with INNER JOIN in XAMPP Server by running PHP Script. It can be possible to use WHERE Clause with INNER JOIN.

PHP MySql

Would you like to see your article here on tutorialsinhand. Join Write4Us program by tutorialsinhand.com

About the Author

Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University. Published 1400+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning

Page Views : Published Date : Jun 14,2024

Please Share this page

PHP MySQL - inner join

PHP MySQL - inner join

Related Articles

You might be interested in