|
|
Posted by Hilarion on 12/05/05 13:16
>>>I have a mySql db table with the following structure:
>>>
>>>main_category| category| sub_category| answer|date
>>>
>>>Basically, the data will be along these lines:
>>>Neuro | LOC | Status | answer1|date
>>>Neuro | LOC | Status | answer2|date
>>>Neuro | LOC | Status | answer3|date
>>>Senso| Visi | Clarity | answer1|date
>>>Senso| Visi | Clarity | answer2|date
>>>etc...
>>>I am trying to query the db and create labels in order to present the
>>>user with the data in the following structure:
>>>
>>>Main Category
>>> Category
>>> Sub Category
>>> answer1
>>> answer2
>>> answer3
>>> ...
>>>
>>>Main Category
>>> Category
>>> Etc...
>>>
>>>I could really use some help on a query to group the data in this way!
>>>
>>>Thanks in advance!!!
>>>
>>>
>>>Frank
>>
>>You could run multiple queries to do group by's or you can run one
>>query and be creative.
>>
>>$ctgy = '';
>>$sctgy = '';
>>$qy = "SELECT * FROM table order by main_category, category";
>>$rs = mysql_query ($qy) or die (mysql_error());
>>while ($r = mysql_fetch_array ($rs)) {
>> if ($ctgy != $r['main_category']) {
>> $ctgy = $r['main_category'];
>> echo "<div style='padding-left:10px;">$ctgy</div>";
>> }
>> // do the same thing for the sctgy with padding-left:20px;
>> // then display your answers
>> echo "<div style='padding-left:30px;'>{$r['answer1']}</div>";
>> //etc.
>> }
>>
>>Warning: this code has not been tested for errors. ;-)
>
> Tyrone,
>
> Thanks very much for the help, but I am not quite sure that I
> understand. in the if ($ctgy!=..) statement, what I am testing
> against? I don't see where $ctgy is ever set to anything but an emptly
> string.
It's set to empty string at first, but then it's set to $r['main_category'].
The solution given works like this:
If the current "main_category" differs from the previous one ($ctgy),
then output it's name and store it as $ctgy for future comparisons.
The algorithm will fail if there are categories with empty names
(it will not output it's <div> if that category comes first from
the query).
Hilarion
Navigation:
[Reply to this message]
|