CODEWITHSUNDEEP
phpmysqljoinwhile-loop
RyanPitts
RyanPitts

Reputation: 601

php while loop inside of while loop - need joins?

Ok, so i am working on some software where users can submit tickets for bugs found on a website. I am using multiple checkboxes so users can check all browsers that are affected by the bug. The bug bugId, title, type, etc are stored in one table called bugs and the affected browsers are stored in another table called affectedbrowsers. The commonality between the two tables is the bugId. I have the form submitting everything correctly.

My problem is returning the data. I have an html table that has one row for each bug in the bugs database table. There is one column called "Affected Browsers" that i would like to populate with the data from the affectedbrowsers table. I tried using a while loop to loop through the bugs and echo out the rows in the html table and using a second while loop within that first while loop that would query the affectedbrowsers table and find all the records that have the same bugId. This isn't returning any data in the second while loop. I would like to use JOINS if possible but i am not really familiar with them. What are your thoughts?

My Code:

<?php
    echo "<table>";
    $resultBug = mysql_query("SELECT * FROM bugs WHERE projectId = '$projectId' ORDER BY bugId ASC");
    echo "<tr> <th>Case Title</th> <th>Affected Browsers</th> </tr>";
    while($rowBug = mysql_fetch_array( $resultBug )){
        $bugId = $_POST['bugId'];
        echo "<tr><td>";
        $rowBugTitle = htmlspecialchars($rowBug['title']);
        echo $rowBugTitle;
        echo "</td><td>";
        $resultAffectedBrowsers = mysql_query("SELECT * FROM affectedbrowsers WHERE bugId = '$bugId' ORDER BY id ASC");
        while($rowAffectedBrowsers = mysql_fetch_array( $resultAffectedBrowsers )){
            $affectedBrowsers = $rowAffectedBrowsers['label'];
            echo $affectedBrowsers . " - ";
        }
        echo "</td></tr>"; 
    }
    echo "</table></div>";
?>

Upvotes: 1

Views: 2146

Answers (3)

mkilmanas
mkilmanas

Reputation: 3485

On top of @AlexAtNet suggestion, I would add grouping so that each bug is returned only once (even when multiple browsers are affected)

SELECT b.bugId, b.title, 
    GROUP_CONCAT(ab.label ORDER BY ab.label ASC SEPARATOR ' - ') AS browsers 
FROM bugs b
INNER JOIN affectedbrowsers ab ON ab.bugId = b.bugId
WHERE b.projectId = '$projectId'
GROUP BY b.bugId
ORDER BY b.bugId ASC

The result would look like

bugId | title       | browsers
------+-------------+-----------------------
1     | "Bug-one"   | "Chrome - IE"
2     | "Bug-2"     | "Firefox"
3     | "Bug-three" | "Chrome - Safari - IE"

Upvotes: 1

Alex Netkachov
Alex Netkachov

Reputation: 13532

Do not execute query inside the loop - use joins instead.

 SELECT b.bugId, b.title, ab.label FROM bugs b
 INNER JOIN affectedbrowsers ab
 ON ab.bugId = b.bugId
 WHERE b.projectId = '$projectId' ORDER BY b.bugId, ab.id ASC

Also do not mix the HTML and PHP - at least move the query to separate function and rendering to another function.

And sanitize the input - do not put the _POST variable directly into query.

Upvotes: 1

A. M.
A. M.

Reputation: 565

You want to get the browsers for the current bug, so you should have 

$bugID = $rowBug['bugId'];

instead of

$bugID = $_POST['bugId'];

Upvotes: 3

Related Questions