Is there anyway to print something like this out without nesting queries?
It has the state, then the employee names, followed by their children as an example.
California
Sherry Smith
Diane
Johnny
Carol Cummings
Alex
New York
Zariela Jackson
Kimberly
$query = <<<END
SELECT DISTINCT
state
FROM states
END;
$squery = doquery($query);
while ($state = mysql_fetch_array($squery)) {
echo $state["state"];
$query = <<<END
SELECT DISTINCT
employee, state
FROM employees
WHERE state = $state["state"]
END;
$equery = doquery($query);
while ($employee = mysql_fetch_array($equery)) {
echo $employee["employee"];
$query = <<<END
SELECT DISTINCT
child, employee
FROM children
WHERE employee = $employee["employee"]
END;
$cquery = doquery($query);
while ($child = mysql_fetch_array($cquery)) {
echo $child["child"];
}
}
}
** This is not meant to be a working example
nested queries usually means subqueries, and they’re okay
however, in your case, you are doing queries within nested loops – and that’s horrible for performance
run just one single query –
SELECT s.state
, e.employee
, c.child
FROM children AS c
INNER
JOIN employees AS e
ON e.employee = c.employee
INNER
JOIN states AS s
ON s.state = e.state
and then do the formatting as you loop over the result rows
Do the ordering in the database, then you can just print out the data, ignoring items that were the same as last time around.
So
<?php
// require database connection;
$sql = mysql_query("SELECT s.state
, e.employee
, c.child
FROM children AS c
INNER JOIN employees AS e
ON e.employee = c.employee
INNER JOIN states AS s
ON s.state = e.state
ORDER BY s.state,e.employee,c.child
");
while ($row = mysql_fetch_array($sql)){ // loop through data array
if ($state != $row['state']){ // if state isnt same as last time set it and print it
$state = $row['state'];
echo '<br />' .$state . '<br />';
}
if ($employee != $row['employee']){ // if employee isnt same set and print
$employee = $row['employee'];
echo '----------' . $employee . '<br />';
}
echo '---------------' . $row['child'] . '<br />'; // providing your data isnt duplicated no need to check children
} // end while
?>
I have perused the internet for days and sat here stressed out trying to figure out how to make it print out similiar to that. The data seems to be there and the query working correctly, but it now prints out a notice at the top.
Notice: Undefined variable: state in
Notice: Undefined variable: employee in
What would I do to fix these notifications? By the way. Thank you very much. I still don’t understand how all of this works, but I am trying.