By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,636 Members | 2,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,636 IT Pros & Developers. It's quick & easy.

Simple menu, MySQL query question

P: n/a
Hello, I'm creating simple menu.

MySQL:

+----+-----+----------------------+
| id | sid | Title |
+----+-----+----------------------+
| 1 | 0 | Main Menu 1
| 2 | 0 | Main Menu 2
| 3 | 1 | SubMenu 1 of menu 1
| 4 | 1 | SubMenu 2 of menu 1
| 5 | 1 | SubMenu 3 of menu 1
| 6 | 2 | SubMenu 1 of menu 2
+----+-----+----------------------

What should be my query? I'm trying to select all titles, and display them:

Main Menu 1
- SubMenu 1 of menu 1
- SubMenu 2 of menu 1
- SubMenu 3 of menu 1
Main Menu 2
- SubMenu 1 of menu 2

TNX

Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hey,
First, I dont know how you structuring SID, but if you just have it setup so
you can only have 1 sub-menu, then according to normal forms, then that
d-base design is wrong. I would retitle "sid" to "parent", which refers to
the "id" of the parent menu. ("0" would be the main parent of course)

As far as displaying them, unless you want just 1 parent and its immediate
underlings, then you are going to want to retrive the entire table and sort
it in PHP, so the SQL you would use is "SELECT * FROM menu_table;" Like I
said, you would have to use PHP to sort everything and format it.
-Eric Kincl

dr. zoidberg wrote:
Hello, I'm creating simple menu.

MySQL:

+----+-----+----------------------+
| id | sid | Title |
+----+-----+----------------------+
| 1 | 0 | Main Menu 1
| 2 | 0 | Main Menu 2
| 3 | 1 | SubMenu 1 of menu 1
| 4 | 1 | SubMenu 2 of menu 1
| 5 | 1 | SubMenu 3 of menu 1
| 6 | 2 | SubMenu 1 of menu 2
+----+-----+----------------------

What should be my query? I'm trying to select all titles, and display
them:

Main Menu 1
- SubMenu 1 of menu 1
- SubMenu 2 of menu 1
- SubMenu 3 of menu 1
Main Menu 2
- SubMenu 1 of menu 2

TNX


Jul 17 '05 #2

P: n/a
On Mon, 10 Nov 2003 22:57:02 +0100, "dr. zoidberg" <so*****@example.wrong>
wrote:
Hello, I'm creating simple menu.

MySQL:

+----+-----+----------------------+
| id | sid | Title |
+----+-----+----------------------+
| 1 | 0 | Main Menu 1
| 2 | 0 | Main Menu 2
| 3 | 1 | SubMenu 1 of menu 1
| 4 | 1 | SubMenu 2 of menu 1
| 5 | 1 | SubMenu 3 of menu 1
| 6 | 2 | SubMenu 1 of menu 2
+----+-----+----------------------

What should be my query? I'm trying to select all titles, and display them:

Main Menu 1
- SubMenu 1 of menu 1
- SubMenu 2 of menu 1
- SubMenu 3 of menu 1
Main Menu 2
- SubMenu 1 of menu 2


MySQL doesn't have any hierarchical query extensions to SQL, so you can't
really get that in a single query. The closest that comes to mind, assuming
only one level of submenus, might be:

mysql> select main.id main_id, main.title main_title,
-> sub.id sub_id, sub.title sub_title
-> from menu main
-> inner join menu sub on (main.id = sub.sid)
-> where main.sid = 0
-> order by main.id, sub.id;
+---------+-------------+--------+---------------------+
| main_id | main_title | sub_id | sub_title |
+---------+-------------+--------+---------------------+
| 1 | Main Menu 1 | 3 | SubMenu 1 of menu 1 |
| 1 | Main Menu 1 | 4 | SubMenu 2 of menu 1 |
| 1 | Main Menu 1 | 5 | SubMenu 3 of menu 1 |
| 2 | Main Menu 2 | 6 | SubMenu 1 of menu 2 |
+---------+-------------+--------+---------------------+
4 rows in set (0.01 sec)

For a more general solution you might want to denormalise some fields, making
updating the menu more expensive, but reading it trivial. See this post for
some ideas:

http://groups.google.com/groups?hl=e...4ax.com&rnum=3

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 17 '05 #3

P: n/a
Eric Kincl wrote:
Hey,
First, I dont know how you structuring SID, but if you just have it setup so
you can only have 1 sub-menu, then according to normal forms, then that
d-base design is wrong. I would retitle "sid" to "parent", which refers to
the "id" of the parent menu. ("0" would be the main parent of course)

As far as displaying them, unless you want just 1 parent and its immediate
underlings, then you are going to want to retrive the entire table and sort
it in PHP, so the SQL you would use is "SELECT * FROM menu_table;" Like I
said, you would have to use PHP to sort everything and format it.


This is probably a good solution. Getting to the "use PHP to sort
everything and format it" issue, I recommend a recursive function.

hmmm.. I seem to have written the whole dang thang :/
well, that was fun :)

It's an XML implementation, If you want me to show you how to make it
output HTML, just ask.

<?php
class MenuMaker {

var $objDom;
var $arrResult;

function MenuMaker($arrResult) {
$this->arrResult = $arrResult;
$this->objDom = domxml_new_doc("1.0");
$ndRoot =
$this->objDom->append_child($this->objDom->create_element("menu"));
foreach($this->arrResult AS $arrTopRow) {
if($arrTopRow["parent_id"] == 0) {
$this->handleMenuItem($arrTopRow,$ndRoot);
}
}
}

function handleMenuItem($arrRow,$nd) {

// display the thing...
$ndItem = $this->printMenuItem($arrRow,$nd);

//{{{ create a temporrary stack of immediate children
$arrTmp = array();
foreach($this->arrResult AS $arrSubRow) {
if($arrRow["id"] == $arrSubRow["parent_id"]) {
$arrTmp[] = $arrSubRow;
}
}
//}}}

// execute the immdiate children in a row (ordered)
foreach($arrTmp AS $arrSubRow){
$this->handleMenuItem($arrSubRow,$ndItem);
}
}

function printMenuItem($arrRow,$nd) {
$ndItem = $nd->append_child($this->objDom->create_element("item"));
foreach($arrRow AS $key=>$val) {
$ndTitle =
$ndItem->append_child($this->objDom->create_element($key));
$ndTitle->set_content($val);
}
return $ndItem;
}

function getXml() {
return $this->objDom->dump_mem(true);
}
}

$arrFakeResult = array();
$arrFakeResult[] =
array("id"=>1,"parent_id"=>0,"title"=>"project","h ref"=>"project/");
$arrFakeResult[] =
array("id"=>2,"parent_id"=>0,"title"=>"faq","href" =>"faq/");
$arrFakeResult[] =
array("id"=>3,"parent_id"=>0,"title"=>"contact","h ref"=>"contact.php");
$arrFakeResult[] =
array("id"=>4,"parent_id"=>1,"title"=>"aims","href "=>"project/aims/");
$arrFakeResult[] =
array("id"=>5,"parent_id"=>1,"title"=>"objectives" ,"href"=>"project/obj/");
$arrFakeResult[] =
array("id"=>6,"parent_id"=>4,"title"=>"easy","href "=>"project/aims/easy.php");
$arrFakeResult[] =
array("id"=>7,"parent_id"=>4,"title"=>"scalable"," href"=>"project/aims/sclable.php");
$arrFakeResult[] =
array("id"=>8,"parent_id"=>4,"title"=>"robust","hr ef"=>"project/aims/robust.php");
$arrFakeResult[] = array("id"=>9,"parent_id"=>5,"title"=>"object
oriented","href"=>"project/obj/oo.php");
$arrFakeResult[] =
array("id"=>10,"parent_id"=>5,"title"=>"xml","href "=>"project/obj/xml.php");

$objMM = new MenuMaker($arrFakeResult);
header("Content-Type: text/xml");
echo $objMM->getXml();

?>

Jul 17 '05 #4

P: n/a
Actually, I was originally gonna make it output it in HTML, but HTML is
so limited. Anyway, this is the reason for using a temporary stack. A
dead-end format (HTML) would require me to group things level by level.
XML does not require this. Now that it is XML, you can remove the extra
code for the temporary stack.

The handleMenuItem is now smaller/simpler.

function handleMenuItem($arrRow,$nd) {

// display the thing...
$ndItem = $this->printMenuItem($arrRow,$nd);

foreach($this->arrResult AS $arrSubRow) {
if($arrRow["id"] == $arrSubRow["parent_id"]) {
$this->handleMenuItem($arrSubRow,$ndItem);
}
}
}

PS. did I mention that the number of sub-levels is essentially unlimited :)

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.