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

makman24Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.