MySQL LEFT JOIN with Comma Delimited Field Search Criteria

try this

(Menus.ID LIKE Content.Menu + ',%') OR
(Menus.ID LIKE '%,' + Content.Menu) OR
(Menus.ID = Content.Menu)

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'"
			);

Select allOpen in new window

sorry made a mistake, should have been

(Content.Menu LIKE Menus.ID + ',%') OR
(Content.Menu LIKE '%,' + Menus.ID) OR
(Menus.ID = Content.Menu)

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 ;

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.

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.

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'"
			);

Select allOpen in new window

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!

Ok. can y ou explain how your approach works? or provide some literature? thanks!!!

-MAK

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

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..
 
 

Select allOpen in new window

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