• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2525
  • Last Modified:

MySQL LEFT JOIN with Comma Delimited Field Search Criteria

I have a LEFT JOIN for listing menu items. I have one table called "Content" and another Called "Menus". The relationship is the "Content.Menu" field and the "Menu.ID" field.

The issue is I need to LEFT JOIN Menus ON Menus.ID = Content.Menu. The problem is that Content.Menu is a comma delimited list of menu IDs. This is because a content page can be assigned to different menus. I need to replace the (=) with (LIKE) or something that will say "JOIN these two tables where the Menus.ID exists in the comma delimted values of Content.Menu.  make sense??

Here is my CODE...


$db = new DbConnection();
			$db->query("
				SELECT Content.Page_Name, Content.ID as cID, Menus.ID 
				FROM Content 
				LEFT JOIN Menus 
				ON (Menus.ID IN Content.Menu)
				WHERE Menus.Title = '$parentID'"
			);
			while ($item = $db->fetchObject()) {
				echo "<li><a href='index.php?target={$item->cID}'>{$item->Page_Name}</a></li>\n";
			}

Open in new window

0
makman24
Asked:
makman24
  • 5
  • 4
  • 3
  • +2
2 Solutions
 
ee_rleeCommented:
try this

(Menus.ID LIKE Content.Menu + ',%') OR
(Menus.ID LIKE '%,' + Content.Menu) OR
(Menus.ID = Content.Menu)
0
 
makman24Author Commented:
Nope. That pulls out everything.  Why doesn't this work?
			$db->query("
				SELECT Content.Page_Name, Content.ID as cID, Menus.ID 
				FROM Content 
				LEFT JOIN Menus 
				ON (Menus.ID LIKE Content.Menu)
				WHERE Menus.Title = '$parentID'"
			);

Open in new window

0
 
ee_rleeCommented:
sorry made a mistake, should have been

(Content.Menu LIKE Menus.ID + ',%') OR
(Content.Menu LIKE '%,' + Menus.ID) OR
(Menus.ID = Content.Menu)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Steve BinkCommented:
Because Menus.ID is not a properly normalized field.  You need to step back and examine your structure.  Menus.ID should be removed from table Menus, and a new table mapping the many-to-many relationship should be created.

CREATE TABLE `Menu_Content_Map` (
  `MapID` int(11) unsigned NOT NULL auto_increment,
  `MenuID` int(11) unsigned NOT NULL,
  `ContentID` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`MapID`)
)

0
 
makman24Author Commented:
P.S.  Here is the table structure...

--
-- Table structure for table `Content`
--

CREATE TABLE `Content` (
  `ID` int(10) NOT NULL auto_increment,
  `Menu` varchar(10) NOT NULL default '0',
  `Parent_Page` varchar(255) NOT NULL default '',
  `Page_Name` varchar(255) NOT NULL default '',
  `Content` text NOT NULL,
  `Position` int(10) NOT NULL default '0',
  `Status` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;


The menu field above stores Menu IDs like (2,5,7,8).


--
-- Table structure for table `Menus`
--

CREATE TABLE `Menus` (
  `ID` int(10) NOT NULL auto_increment,
  `Title` varchar(255) NOT NULL default '',
  `Status` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
0
 
ee_rleeCommented:
when using like, you have to use wildcards like %, or it will be just like using =.
http://www.techonthenet.com/sql/like.php

i put 3 conditions so that if your id is 1, it will not be pulled out when your menu is 10.
0
 
Steve BinkCommented:
But it will match '3,10' when you ask for '1'.

Normalization is a necessary part of database design.  Ignoring it has consequences.  You're seeing one of them now.  This only gets worse over time, and patching a design error such as this will eventually back you into a corner.
0
 
makman24Author Commented:
ee_rlee:

Ok, I am not using this in the WHERE clause however. Does that matter? I am using it as an expression between tables to JOIN ON. The code below still pulls everything.

routinet:

I understand your approach, however, this is a quicky app that needs to be done very soon and no time for database restructuring... I need to work with what I have. Thanks though! If you can maybe help me work with what I got, that would be great. =o)
			$db->query("
				SELECT Content.Page_Name, Content.ID as cID, Menus.ID 
				FROM Content 
				LEFT JOIN Menus 
				ON (Content.Menu LIKE Menus.ID + ',%') 
				OR (Content.Menu LIKE '%,' + Menus.ID) 
				OR (Menus.ID = Content.Menu)
				WHERE Menus.Title = '$parentID'"
			);

Open in new window

0
 
Steve BinkCommented:
Understood, but keep this in mind as you expand.  The time to fix this issue is now, before you build any further.  I once spent 9 months writing a website before identifying a design flaw like this.  It took me another 9 months to rip it down, fix the flaw, and get back to where I was.

Good luck!
0
 
makman24Author Commented:
Ok. can y ou explain how your approach works? or provide some literature? thanks!!!

-MAK
0
 
Steve BinkCommented:
Well, the basic concept of normalization that applies here is that if you are storing more than one value in a single field, you need to create a lookup table.  Your content and menu tables inherently have a many-to-many relationship.  That is generally expressed in relational databases with a 'middle man' table, such as the one I described earlier.

Content 1->m Menu_Content_Map m<-1 Menu

Ideally, Menu_Content_Map would also have a unique index built on MenuID and ContentID.  A query to pull all of the maps as they should appear:

SELECT * FROM Menu a INNER JOIN Menu_Content_Map b ON a.MenuID=b.MenuID INNER JOIN Content c ON b.ContentID=c.ContentID

From there, you can use a WHERE clause to isolate just one menu item, or just one content item, or the one record that matches both:

SELECT * FROM Menu a INNER JOIN Menu_Content_Map b ON a.MenuID=b.MenuID INNER JOIN Content c ON b.ContentID=c.ContentID WHERE a.MenuID=1

SELECT * FROM Menu a INNER JOIN Menu_Content_Map b ON a.MenuID=b.MenuID INNER JOIN Content c ON b.ContentID=c.ContentID WHERE c.ContentID=2

SELECT * FROM Menu a INNER JOIN Menu_Content_Map b ON a.MenuID=b.MenuID INNER JOIN Content c ON b.ContentID=c.ContentID WHERE a.MenuID=1 AND c.ContentID=2

Here's a link to a not-so-basic tutorial on normalization concept.  If you google for "database normalization", you'll find much more information.

http://databases.about.com/od/specificproducts/a/normalization.htm


0
 
LowfatspreadCommented:
i agree you shouldn't structure your table like this

however the current solution would be ...

 
SELECT C.Page_Name, C.ID as cID, M.ID 
  FROM Content as C
  LEFT OUTER JOIN Menus as M
    ON ','+ C.Menu +',' LIKE '%,'+Menus.ID + ',%' 
 WHERE M.Title = '$parentID'"
 
ie ensure the list is delimited with a , then search for the menu_id surrounded by commas
so that 1 isn't confused with 10 or 31 or 2001  etc..
 
 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me drop in with suggesting the FIND_IN_SET function:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

however, I agree with above comments to consider normalizing the table structure...
SELECT C.Page_Name, C.ID as cID, M.ID 
  FROM Content as C
  LEFT OUTER JOIN Menus as M
    ON FIND_IN_SET(Menus.ID , C.Menu ) > 0
 WHERE M.Title = '$parentID'"

Open in new window

0
 
makman24Author Commented:
angellll, your solution was EXACTLY what I needed. routinet, you are absolutely right though. I gave you points because even though you didnt offer the solution I need, you offered intelect I can use in the future! Thanks a lot!!!

-MAK
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now