dats dats - 7 months ago 11
SQL Question

How do i implement nested foreach with data from mysql?

I have a process table which lists all processes. it has a primary key of processid. it has a column for cateforyid which contains the categoryid of the process. I have a separate category table which has a categoryid and category name.

process table category table enter image description here

From my controller, I get all processes from my model and a separate function to get all categories.

Then in my view, i have this code:

<?php
foreach ($category as $c) {
echo "<li>" . $c->CategoryName . "</li>";
foreach ($process as $r) {
if ($r->CategoryID == $c->CategoryID) {
echo "<ul>";
echo "<li>" . anchor("process/id/$r->ProcessID", $r->ProcessName, 'target="_blank"') . "</li>";
echo "</ul>";
}
}
}
?>


It works fine but it lists the categories even if they dont have any processes under them. How can i implement in such a way that it only lists the categories if there is a process under them?

Answer

You should always use explicit JOINs for your SELECT statements. Never just list tables in the FROM clause. You're also missing JOIN criteria for your Category table. Instead, try:

SELECT
    P.ProcessID,
    P.ProcessName,
    P.CategoryID,
    CAT.CategoryName
FROM
    Process P
INNER JOIN Category CAT ON CAT.CategoryID = P.CategoryID
INNER JOIN Process_Country PC ON
    PC.ProcessID = P.ProcessID AND
    PC.CountryCode = 'AM'