php while loop inside of while loop - need joins?

Question

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>";
?>

A. M. · Accepted Answer · 2011-06-02 16:43:36Z

3

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

$bugID = $rowBug['bugId'];

instead of

$bugID = $_POST['bugId'];

answered Jun 2, 2011 at 16:43

A. M.

5651 gold badge8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RyanPitts Over a year ago

wow...i feel a little dumb for that one....guess i've just been starring at it for too long. That worked. Thanks! Glad it was an easy fix

Alex Netkachov · Accepted Answer · 2011-06-02 16:43:47Z

1

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.

answered Jun 2, 2011 at 16:43

Alex Netkachov

13.6k7 gold badges56 silver badges90 bronze badges

1 Comment

Richard Marskell - Drackir Over a year ago

If the OP changes to loops, it will affect all of his looping logic. You should identify how it will change since the OP clearly stated they're not familiar with JOINs. For example, the new recordset will have multiple rows for each bug (assuming multiple browsers) and will have no records for bugs where there were no affected browsers (assuming that's possible).

mkilmanas · Accepted Answer · 2011-06-02 17:00:06Z

1

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"

answered Jun 2, 2011 at 17:00

mkilmanas

3,48519 silver badges28 bronze badges

Collectives™ on Stack Overflow

php while loop inside of while loop - need joins?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related