PHP MySQL Query Subquery
From w3cyberlearnings
Contents |
PHP MySQL SubQuery
Scalar subquery is a subquery within query and it returns single value only. In the WHERE clause subquery can return multiple values. Check the example for detail.
Syntax
// scalar subquery
SELECT
sal.id as salary_id ,
( SELECT
em.name
FROM
employee em
WHERE em.id=sal.employee_id
) as name,
sal.salary as 'hourly salary'
FROM
employee_salary sal
// Where clause subquery, and it can return multiple values
SELECT em.id as employee_id,
em.name,
em.grade
FROM
employee em
WHERE em.id IN(SELECT sel.employee_id FROM employee_salary sel);
Example 1
- Subquery returns only single value.
<?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
sal.id as salary_id ,
( SELECT
em.name
FROM
employee em
WHERE em.id=sal.employee_id
) as name,
sal.salary as 'hourly salary'
FROM
employee_salary sal";
$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Salary id</th>
<th>Name</th>
<th>salary</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
Example 2
- Where clause subuquery, and this query can return multiple records.
<?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
FROM
employee em
WHERE em.id IN(SELECT sel.employee_id FROM employee_salary sel)";
$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>
</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
