php get menu from SQL query

Question

Hello everyone I got a database that looks like that

Category.name,category.id
Sub_category.name,sub_category.id,sub_category.belong_to_id

Basically when I pull the menu I need to make something like this:

$query = mysql_query("SELECT * FROM category");
while($category = mysql_fetch_assoc($query) {
  echo '<li>'.$category[name].'<li>';
}

and then make a sql query for each sub category inside the while loop it looks useless to me, and I can't figure a way how to do this with inner joins, because i need to seperate the menu.

I don't want nested loops

You should not be using that code: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1 — miken32, Nov 19 '15 at 23:51

John Doe · Answer 1 · 2015-11-20T00:06:29.200

0

Here is how to do an INNER JOIN on those 2 tables if you don"t want nested loops:

SELECT *
FROM Category c
INNER JOIN Sub_category s ON s.name = c.name

edited Nov 20 '15 at 00:06

answered Nov 19 '15 at 23:55

John Doe

905
8
9

2

While this code snippet may solve the question, including an explanation out of the code really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. http://meta.stackexchange.com/q/114762/308249 – davejal Nov 19 '15 at 23:56

score 0 · Answer 2 · answered Nov 20 '15 at 00:00

The following query would return a list of the categories and their appropriate sub-categories:

SELECT
  c.name as cname, 
  sc.name as scname 
FROM Category c 
INNER JOIN Sub_category sc ON sc.belong_to_id = c.id

You could then rearrange this data to fit the schema you need, without having to run multiple sub-queries (which is kind of inefficient).

For example:

$cats = array();
foreach($data as $item) {
  $cats[$item['cname']][] = $item['scname'];
}

Which would leave you with something like this:

array(
  'Services' => array(
     0 => 'Something We Do',
     1 => 'Another Thing We Do'
  ),
  'About Us' => array(
     0 => 'Contact Us',
     1 => 'More Information'
  )
)

Which you could then use to more easily build your menu. Obviously specifics of how you want to display the menu and etc play a factor, but that is a general idea.

php get menu from SQL query

2 Answers2