PHP MySQL Query Use LEFT OUTER JOIN
From w3cyberlearnings
Contents |
PHP MySQL LEFT OUTER JOIN
The LEFT OUTER JOIN is the join based on left table, and it displays null for the right table if the join condition is not true.
Insert Two Records to employee table
You need these two records for testing purpose.
INSERT INTO employee VALUES(null,'Jiji','c'); INSERT INTO employee VALUES(null,'Mama','d');
Syntax
SELECT em.id as employee_id, em.name, em.grade, sal.salary, sal.id as salary_id FROM employee em LEFT OUTER JOIN employee_salary sal ON em.id = sal.employee_id
Example 1
<?php
define('HOST', 'localhost');
define('USER', 'root');
define('PASS', 'yeething');
define('DBNAME', 'woowood');
$connection = mysql_connect(HOST, USER, PASS)
or die("can not connect to the server!<br/>");
$rdb = mysql_select_db(DBNAME)
or die("The " . DBNAME . "database could not be selected");
$sel_query = "
SELECT
em.id as employee_id,
em.name,
em.grade,
sal.salary,
sal.id as salary_id
FROM
employee em
LEFT OUTER JOIN employee_salary sal
ON em.id = sal.employee_id";
$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>employee id</th>
<th>Name</th>
<th>Grade</th>
<th>salary</th>
<th>salary id</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
echo '<tr>';
foreach ($row as $value) {
echo '<td>' . $value . '</td>';
}
echo "</tr>";
}
echo '</table>';
mysql_close($connection);
?>
Output
