output mysql sections and categories

[fanding]

Hi All,

 

I have two tables in my mysql DB and I am trying to output the sections as table headers and categories under each related section, this should have about 4 columns and the rest of the sections will loop.

 

I have included an image as a sample.

 

[attachment deleted by admin]

[Barand]

you could try something like this

<?php

$sql = "SELECT s.section_name, GROUP_CONCAT(c.name SEPARATOR '<br/>') as cats
        FROM section s
        INNER JOIN category c ON s.idsection=c.section_id
        GROUP BY s.section_name";
$res = mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");
while (list($section, $cats) = mysql_fetch_row($res))
{
    $sects[$section] = $cats;
}
$k = count ($sects);
/**
* output array 
*/
echo '<table border="1">';
for ($r=0; $r<$k; $r+=4)
{
    $row = array_slice($sects,$r,4);     // get groups of 4
    /**
    * section headings
    */
    echo '<tr>';
    foreach ($row as $head=>$v)
    {
        echo "<th>$head</th>";
    }
    echo '</tr>';
    /**
    * categories
    */
    echo '<tr valign="top">';
    foreach ($row as $cats)
    {
        echo "<td>$cats</td>";
    }
    echo '</tr>';
}

echo '</table>';
?> 

[fanding]

Hi,

 

That was wonder and time saving, I now want to add LIMIT to the number of categories to 4 which I have tried but no success.

 

I am a real newbie

[Barand]

Probably done more easily in the php code than in the SQL

 

change

echo "<td>$cats</td>";

 

to

echo '<td>' . join ('<br/>', array_slice(explode('<br/>', $cats), 0, 4)) . '</td>';

[fanding]

Hi,

 

Just tried that method but it did not seem to work for me.

 

Is it possible implementing the limit in the query itself?

[fenway]

You can't limit a group_concat -- you'd have to pre-select the 4 fours, then join then back.... at which point there's no need to group_concat, since you can do this in code, and use a regular limit.