You probably want to restructure your tables (if that's an option) if it's a heirachal menu system (one parent only), then you'll need to put a parent_id column, and then map each child to the parent.
then once you have your parent menu (158), you select * from menus where parent_id = 158 If it's a cross-menu system (multiple parents per item), you'll need another table, say menu_relations (source_id, item_id) and put a row in there for each relationship. then you select * from menus, menu_relations where id = item_id and source_id = 158) Dunno if this helps.... > -----Original Message----- > From: Craig Harding [mailto:[EMAIL PROTECTED] > Sent: Friday, October 24, 2003 12:36 PM > To: [EMAIL PROTECTED] > Subject: question. > > > I have a table (menus) with the following fields: > > TABLE: MENUS > ------------ > id int(11) > url varchar(100) > items varchar(35) > > An example row: > > ID URL ITEMS > 158 programs/graduate 21,22,23,24,25,26,27,28,160 > > I want to select the ITEMS and then do another subselect that > can uses > each ITEM as the ID in the subselect. > > 'select items from menus where id = 158' returns: > > 21,22,23,24,25,26,27,28,160 > > Each of these ITEMS is an id in the table also. I want to get > each row > that corresponds to id = 21, 22, 23, 24, 25 ... > > I know how to do this in php, but I need to do this on the > command line. > Remember that ITEMS is a varchar type, so I'm not sure if > there's a way > to do some kind of loop within this to select the items or create the > long query string with OR id = 21 OR id = 22 OR id = 23 ... which I > started to do: > > select CONCAT('id = ', substring(items,1,LOCATE(',', items, > 1)-1), ' OR > id = ', substring(items, 4,locate(',', items,2)-1)) AS STR from menus > where items != '' and id = 158; > > but this can be pretty long since it only grabs the first two digits > from ITEMS! > > The ITEMS always has NO spaces between commas and numbers and some > numbers may be single digit, two digits or even three in the row. > > thanks in advance, > > craig. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
