Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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>";
?>
share|improve this question
add comment (requires an account with 50 reputation)

3 Answers

up vote 3 down vote accepted

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

$bugID = $rowBug['bugId'];

instead of

$bugID = $_POST['bugId'];
share|improve this answer
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 – RyanPitts Jun 2 '11 at 17:33
add comment (requires an account with 50 reputation)
up vote 1 down vote

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.

share|improve this answer
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). – Richard Marskell - Drackir Jun 2 '11 at 16:49
add comment (requires an account with 50 reputation)
up vote 1 down vote

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"
share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.