I’m still trying to get my head round more advanced MySQL but I think I’m slowly getting there. I need some help with a query I’m trying to do but I’m not sure how to go about it and have been scratching my head for the past week on how to do it.
I have 2 tables - content_links
So the idea here is that there is a table full of urls and a table where the urls are put into menus. In my template engine I’ll call a function to build a certain menu - eg “index”, to do this I tried the following query -
SELECT `content_links`.`text`, `content_links`.`url`, `content_links`.`level`, `content_menus`.`links`, `content_menus`.`skeleton`, `content_menus`.`level`
ON `content_links`.`lid` = IN(`content_menus`.`links`)
WHERE `content_links`.`active` = '1'
AND `content_links`.`level` <= '4'
AND `content_menus`.`active` = '1'
AND `content_menus`.`level` <= '4'
AND `content_menus`.`name` = `index`;
The above query resulted in an error. I’m still trying to get to grips with different types of MySQL join, I’m also not sure if I can even use IN() with the join.
learn outer joins later (you’ll be using LEFT OUTER JOIN exclusively, never RIGHT OUTER JOIN)
no, you can’t
what you have is a bad design which will forever give you very poor performance – your best bet is to redesign the tables, removing the links column which has multiple values in it, and adding a relationship table which allows you to associate any link with any menu
in the meantime, try this –
ON [COLOR="Blue"]FIND_IN_SET(content_links.lid,content_menus.links) > 0[/COLOR]
AND content_links.active = 1
AND content_links.level <= 4
WHERE content_menus.active = 1
AND content_menus.level <= 4
AND content_menus.name = 'index';
So the idea of parent is so you can have drop down menus, so if the parent is set to “0” then it’s considered a parent link, if it’s set to “>0” then it’s a child of that id. The thing is no link will always be a parent or always be a child, it depends on the menu. That’s why I thought it needed a relationship id so you could reference it in the parent column.