I am having a lot of trouble with a grouped output for a dynamic navigation query. Please note, this is not an SQL-related grouping for aggregation.
I have four values I would like to group, each of the first two grouped by the name, and the last two being a pair for the hyperlink.
GRAPHIC:
Category1
--Subcategory1
-----Link1
-----Link2
-----Link3
Category2
--Subcategory2
-----Link4
The code I have written is an embarrassment -- and falls apart with the second "Category". I don't know if it is a nested while() loop or something else. Any help is GREATLY appreciated.
Thanks.
Grouped output
Moderator: General Moderators
Re: Grouped output
First you'll need to explain what you are talking about. Is it how to write a PHP script that displays grouped data from a query? If so, show us your SQL so we can figure out what you're trying to do. I hesitate to say this, but if the script you wrote is such an embarrassment to you, chances are not good that your knowledge level is sufficient for us to help you without writing your entire script, and that's not likely to happen here. I'd suggest that you get over your embarrassment and post your code. If you do so, please enclose your PHP code in [syntax=php]...[/syntax] tags here, to make it more readable.
Re: Grouped output
Jack of Zircons, thanks for the shot of reality. You're right; I have no room for embarrassment. I am not looking to have anyone do the write the script. I get the data I want; I am simply having trouble displaying the code. I am close, but I think a nested while loop(s) would be more elegant and precise.
SQL SNIPPET:
SELECT
l.subcategory,
l.link_id,
l.link_name,
c.category_name
FROM
categories c,
links l
...
SAMPLE RESULT:
Category_1 / Subcategory_1 / Link_1 / Link_Id_1
Category_1 / Subcategory_1 / Link_2 / Link_Id_2
Category_1 / Subcategory_1 / Link_3 / Link_Id_3
Category_2 / Subcategory_2 / Link_4 / Link_Id_4
Category_2 / Subcategory_2 / Link_5 / Link_Id_5
Category_3 / Subcategory_3 / Link_6 / Link_Id_6
Category_4 / Subcategory_4 / Link_7 / Link_Id_7
DESIRED OUTPUT:
The dashes used solely for clarity; actual code will be nested HTML lists. I removed the HTML from my code snippet. Likewise, the trailing number indicates the incremental rows.
Category_1
--Subcategory_1
----Link_1 / Link_Id_1
----Link_2 / Link_Id_2
----Link_3 / Link_Id_3
Category_2
--Subcategory_2
----Link_4 / Link_Id_4
----Link_5 / Link_Id_5
Category_3
--Subcategory_3
----Link_6 / Link_Id_6
Category_4
--Subcategory_4
----Link_7 / Link_Id_7
SQL SNIPPET:
SELECT
l.subcategory,
l.link_id,
l.link_name,
c.category_name
FROM
categories c,
links l
...
SAMPLE RESULT:
Category_1 / Subcategory_1 / Link_1 / Link_Id_1
Category_1 / Subcategory_1 / Link_2 / Link_Id_2
Category_1 / Subcategory_1 / Link_3 / Link_Id_3
Category_2 / Subcategory_2 / Link_4 / Link_Id_4
Category_2 / Subcategory_2 / Link_5 / Link_Id_5
Category_3 / Subcategory_3 / Link_6 / Link_Id_6
Category_4 / Subcategory_4 / Link_7 / Link_Id_7
DESIRED OUTPUT:
The dashes used solely for clarity; actual code will be nested HTML lists. I removed the HTML from my code snippet. Likewise, the trailing number indicates the incremental rows.
Category_1
--Subcategory_1
----Link_1 / Link_Id_1
----Link_2 / Link_Id_2
----Link_3 / Link_Id_3
Category_2
--Subcategory_2
----Link_4 / Link_Id_4
----Link_5 / Link_Id_5
Category_3
--Subcategory_3
----Link_6 / Link_Id_6
Category_4
--Subcategory_4
----Link_7 / Link_Id_7
Code: Select all
/*Create Array*/
$x = 0;
while ($record = mysql_fetch_object($qry)) {
$ary[$x][0] = $record->category_name;
$ary[$x][1] = $record->subcatgory_name;
$ary[$x][2] = $record->link_name;
$ary[$x][3] = $record->link_id;
$x++;
}
...
/* Output menu */
$count = mysql_num_rows($qry);
for($a = 0; $a < $count; $a++) {
$category = $ary[$a][0];
$subcategory = $ary[$a][1];
$link_name = $ary[$a][2];
$link_id = $ary[$a][3];
if($category != $last_category) {
$last_category = $category;
echo "$category";
}
if($subcategory != $last_subcategory) {
$last_subcategory = $subcategory;
echo "$subcategory";
}
$b = $a+ 1;
$peek = $subcategory = $ary[$b][1];
echo "$link_id $link_name";
}
Re: Grouped output
You did fine except for the SQL, you left off the most important part, the GROUP BY. That's what we need to see in order to suggest how to display it.
Re: Grouped output
It is my understanding that group by is used for aggregate functions such as sum() and count(). Or am I misinformed?
Re: Grouped output
I didn't read your first post closely enough. So are you saying that the SQL you showed was the entire SQL statement??? No ORDER BY clause??? No WHERE clause???darkelf wrote:It is my understanding that group by is used for aggregate functions such as sum() and count(). Or am I misinformed?
I don't know of any way to "group" data without sorting it. If all you want is really just what you showed, all you need to do is use an ORDER BY c.category_name. Usually the question is "how do I show the Category as a heading", for which you have to fetch each row in a while loop and test whether each one is in the same category as the previous row, and if not, output the heading.
Re: Grouped output
There is an ORDER BY clause. I omitted it before for brevity. Here is the SQL. Thanks again for you indulgence.
SELECT
l.subcategory,
l.link_id,
l.link_name,
c.category_name
FROM
categories c,
links l
WHERE
l.link_cat_id = c.category_id
ORDER BY
c.category_name,
l.subcategory,
l.link_name
SELECT
l.subcategory,
l.link_id,
l.link_name,
c.category_name
FROM
categories c,
links l
WHERE
l.link_cat_id = c.category_id
ORDER BY
c.category_name,
l.subcategory,
l.link_name
Re: Grouped output
Ahh, now it's possible to see what you're doing. Then, as I said in my previous post, you need to test whether each successive row of the result has changed since the previous one, within a while loop. Since you appear to have two levels of grouping, you will need a slightly more complicated while loop. Something like the following:
I don't see a need to use arrays here, unless there's some other reason that's not apparent to me.
Code: Select all
...
$lastcat="";
$lastsub="";
while($row=mysql_fetch_assoc($result)){
extract($row);
if($lastcat<>$category_name) {
// echo the category header
// echo the subcategory header
$lastsub=$subcategory;
}
if($lastsub<>$subcategory) {
// echo the subcategory header
}
// echo the link data
$lastcat=$category_name;
$lastsub=$subcategory;
}