Embedding a query within a query

[Fluffy654]

I'm using the following code to produce a resortable, nested menu from a database that consists of three fields: id, code, category.

 

<?php

$sql = "SELECT id, code, GROUP_CONCAT(category ORDER BY category SEPARATOR '</a><a>') as category FROM menu GROUP BY code ORDER BY code";

$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {

echo "<h4 class=\"toggler\">" . $row['code'] . "</h4><div class=\"element\"><a>" . $row['category'] . "</a></div>";

}

?>

 

This echos item x in "code" and then every item of "category" that has code "x". It then moves on to item y in "code" and so on. As you can see, I have given everything appropriate classes so that the resulting list will produce a mootools accordion.

 

What I need to do now is give each "category" item a unique identifier that will allow me to produce an ajax request from it. Normally, I would do this by giving each anchor tag an id but using this query, I can't do that as I am implanting the anchor tags using the SEPERATOR command in the initial query.

 

Does anybody have any ideas about how I can assign an id to each "category" item? I am initially thinking that it should be possible to do a second query and nest the original query inside of it. I should then be able to call the id from the database into the SEPERATOR but I have been unable to do this. Is there an easier way or am I stuck with this method and, if so, how can I go about doing it?

[aschk]

Please post the layout of your database, sample data, and what you want out of it (in english, no code).

[Fluffy654]

Heya aschk,

 

My database consists of 3 fields: id, code and category with id as the primary index.

 

Some sample data:

ID CODE CATEGORY

1 XHTML menu

2 CSS lightbox

3 Mootools lightbox

4 Mootools accordion

 

The code I am using produces a series of headers and divs arranged as needed for a mootools accordion. The headers need the class "togglers" and the divs need the class "elements". Based on the sample data, this code will first print XHTML into h4 tags and then every category that has the code "XHTML" in anchor tags wrapped in a div. In this case, it will just take menu. It then moves on to CSS and then Mootools and so on.

 

I see the code above did not get posted correctly. Where it says "url" it should say "a".