Hitherto, we have just been getting information from each table in turn. This is fine for basic takes, however in most certifiable MySQL use, you will regularly have to get information from numerous tables in a solitary inquiry.
You can utilize different tables in your single SQL inquiry. The demonstration of participating in MySQLi alludes to crushing at least two tables into a solitary table.
You can utilize JOINS in SELECT, UPDATE and DELETE articulations to join MySQLi tables. We will see an illustration of LEFT JOIN additionally which is not quite the same as basic MySQLi JOIN.
Using Joins at Command Prompt
Assume we have two tables tutorials_bks and tutorials_inf, in TUTORIALS. A total posting is given beneath −
Example
Evaluate the accompanying models −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * FROM tcount_bks;
+----+---------+
| id | book |
+----+---------+
| 1 | java |
| 2 | java |
| 3 | html |
| 4 | c++ |
| 5 | Android |
+----+---------+
5 rows in set (0.00 sec)
mysql> SELECT * from tutorials_inf;
+----+-------+
| id | name |
+----+-------+
| 1 | sai |
| 2 | johar |
| 3 | raghu |
| 4 | ram |
+----+-------+
4 rows in set (0.00 sec)
mysql>
Presently we can compose a SQL inquiry to join these two tables. This question will choose all the names from table tutorials_inf and will pickup comparing number of instructional exercises fromtutorials_bks.
mysql> SELECT a.id, a.name,b.id FROM tutorials_inf a,tutorials_bks b WHERE a.id = b.id;
+----+-------+----+
| id | name | id |
+----+-------+----+
| 1 | sai | 1 |
| 2 | johar | 2 |
| 3 | raghu | 3 |
| 4 | ram | 4 |
+----+-------+----+
4 rows in set (0.00 sec)
mysql>
In tutorials_bks table, we have 5 records however in the above model it channels and gives just 4 id records according to inquiry
Using Joins in PHP Script
You can utilize any of the previously mentioned SQL question in PHP content. You just need to pass SQL inquiry into PHP work mysqli_query() and afterward you will get brings about normal way.
Example
Evaluate the accompanying model −
<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '';
$dbname = 'TUTORIALS';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
if(! $conn ) {
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully</br>';
$sql = 'SELECT a.id, a.name,b.id FROM tutorials_inf a,tutorials_bks b WHERE a.id = b.id';
if($result = mysqli_query($conn, $sql)) {
if(mysqli_num_rows($result) > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>name</th>";
echo "<th>id</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['id'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_free_result($result);
} else {
echo "No records matching your query were found.";
}
} else {
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
mysqli_close($conn);
?>
The example yield ought to be this way −
Connected successfully
id name id
1 sai 1
2 johar 2
3 raghu 3
4 ram 4
MySQL LEFT JOIN
A MySQLi left join is unique in relation to a basic join. A MySQLi LEFT JOIN gives additional thought to the table that is on the left.
On the off chance that I do a LEFT JOIN, I get all the records that coordinate similarly and IN ADDITION I get an additional record for each unrivaled record in the left table of the join - subsequently guaranteeing (in my model) that each name gets a notice −
Example
Evaluate the accompanying guide to see LEFT JOIN −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql>SELECT a.id, a.name,b.id FROM tutorials_inf a LEFT JOIN tutorials_bks b ON a.id = b.id;
You would have to accomplish more practice to get comfortable with JOINS. This is somewhat mind boggling idea in MySQL/SQL and will turn out to be all the more clear while doing genuine models.