Head First PHP book INNER JOIN SQL example

Here is a good example for the chapter where you need to use “INNER JOIN” to join two sql tables.

Create two tables for your database.

Table 1: “countries”, with 2 elements: id_country (auto increment), country.
Table 2: “states”, with 3 elements: id_state (auto increment), id_country, state.

So we could join the tables in our SQL query so if we want to display an element from “states”, it will be without having to match the “id_country” values with two requests, just with 1.

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

<? require_once('engine.php'); ?>
 
<?
 
$requestSQL = "SELECT * FROM countries INNER JOIN states ON countries.id_country = states.id_country";
// we first select as normal, our countries table, then use INNER JOIN and declare the second table, "states", use "ON", then match the table elements using a prefix with the table names.
$query = mysqli_query($dbc, $requestSQL);
while ($row = mysqli_fetch_array($query))
	{
		$country = $row['country'];
		$state = $row['state'];
		echo ''.$state.'';	
	}
 
?>

When echo-ing “state”, it will really echo the state.
You can see more regarding INNER JOIN here: http://www.w3schools.com/sql/sql_join_inner.asp