help with query and presentation...

am 02.12.2005 23:46:14 von frank

Re: help with query and presentation...

am 03.12.2005 02:42:58 von Tyrone Slothrop

On Fri, 02 Dec 2005 16:46:14 -0600, Frank wrote:

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

{$r['answer1']}

";
//etc.
}

Warning: this code has not been tested for errors. ;-)

Re: help with query and presentation...

am 04.12.2005 02:44:23 von frank

On Fri, 02 Dec 2005 17:42:58 -0800, Tyrone Slothrop
wrote:

>On Fri, 02 Dec 2005 16:46:14 -0600, Frank wrote:
>
>>
>>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 "

{$r['answer1']}

";
> //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.
In my web app there will literally be over 100 main categories, cats,
and sub-cats each with a several answers. So, I am trying to make this
as dynamic as possible. Could you explain a little more for my feeble
mind. Also, could you show me you logic for running multiple queries
on this.

Thanks!!!!!!

Frank

Re: help with query and presentation...

am 05.12.2005 12:16:47 von Hilarion

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

{$r['answer1']}

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

if that category comes first from
the query).

Hilarion

Re: help with query and presentation...

am 06.12.2005 04:27:59 von frank

On Mon, 5 Dec 2005 12:16:47 +0100, "Hilarion"
wrote:

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

{$r['answer1']}

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

if that category comes first from
>the query).
>
>Hilarion

Thank you both very much for the help. It Works like a Charm!!!
Thanks again,

Frank