MySQL and PHP - For Each?

Question

Hi all,

I have following code:

SELECT q21, q21coding  AS Description FROM `tresults_acme` WHERE q21 IS NOT NULL AND q21 <> '' ORDER BY q21coding

It brings back the following (excerpt):

Text                                                     Description
Lack of up to date equal pay cases&legislation - t... Content needs updating
The intranet could contain more "up to date traini... Content needs updating
Poorly set out. It is hard to find things.            Difficulty in navigating/finding content
Only use the intranet as a necessity. Will ask my ... Difficulty in navigating/finding content

Now, I'd like to display this in a table on a PHP page but am having some problems because of the way I'd like it displayed, it needs to be as follows:

Content needs updating
----------------------
[List all the comments relating to this description]

Difficulty in navigating/finding content
----------------------------------------
[List all the comments relating to this description]

and so on.

Now I think it is a For Each loop in PHP but I am having terrible difficulty getting my head around this - any ideas and suggestions very very welcome!

Thanks,

Answer 1

Simple approach

1. Set prev_desc to NULL
2. For each row print text
3. If description is not equal to prev_desc prepend with the description for the new "section" and set prev_desc <- description

E.g.¹ (untested!),

$prev_desc = null;
while ($row = mysql_fetch_assoc(...)) {
    if ($prev_desc != $row['description']) {
        print '<h1>' . $row['description'] . '</h1>';
        $prev_desc = $row['description'];
    }
    print $row['text'] . '<br />'; // Formatting needed
}

Note: You must keep the ORDER BY <description-column> in order to have rows "grouped". Otherwise this simple approach will not work.

Less presentation-specific approach

I could be considered more "clean" to create some kind of 2D container to "categorize" the extracted data, e.g.,

$items = array(
    'Content needs updating' => array(
        'Lack of ...',
        'The intra...'
    ),
    ...
);

You could then loop over these items like so¹:

foreach ($items as $desc => $texts) {
    print '<h1>' . $desc . '</h1>';
    foreach ($texts as $text) {
        print $text . '<br />';
    }
}

---

¹ As @bobince has noted, make sure that content going directly into the final HTML is properly escaped, see e.g. htmlspecialchars().

Answer 2

You just need to keep track of which heading you last displayed. I don't know which library you're using for database access, so the details of how you access columns/rows will be slightly different, but here it is in kind-of pseudocode:

$lastHeading = '';
foreach($rows as $row)
{
    if ($lastHeading != $row['Description'])
    {
        if ($lastHeading != '')
            echo '</ul>';

        $lastHeading = $row['Description'];
        echo "<h1>$lastHeading</h1>";
        echo '<ul>';
    }

    echo '<li>'.$row['Text'].'</li>';
}
if ($lastHeading != '')
    echo '</ul>';

This has the added feature of putting comments in a <ul>, not sure if that's required for you or not.

This works because you've sorted by the "description" column. That means you know that all of the rows with the same "description" will come together.

Answer 3

you can either create multiple queries for each of the sections or loop over the data multiple times and filter based on the type of description using php.

$descriptions = array('Content needs updating','Difficulty in navigating/finding content');
$rows = <fetch all rows from the query>;
foreach($descriptions as $description)
{
    echo '<h1>',$description,'</h1>';
    foreach($rows as $row)
    {
        if ($row['description'] == $description)
        {
            echo $row['text'],'<br />';
        }
    }
}