0

I have two tables in the database, parts, and products. I have a column in the products table with strings of ids (comma separated). Those ids match ids of the parts table.

**parts**

ID  |  description (I'm searching this part)
-------------------------------
1   |  some text here
2   |  some different text here
3   |  ect...

**products**

ID  |  parts-list
--------------------------------
1   |  1,2,3
2   |  2,3
3   |  1,2

I'm really struggling with the SQL query on this one. I've done the 1st part, got the id's from the parts table

SELECT * FROM parts WHERE description LIKE '%{$search}%'

The biggest problem is the comma separated structure of the the description column.

Obviously, I could do it in PHP, create an array of the the results from the parts table, use that to search the products table for id's, and then use those results to grab the row data from the parts table (again). Not very efficient.

I also tried this, but I'm obviously trying to compare two arrays here, not sure how this should be done.

SELECT * FROM `products` WHERE 
CONCAT(',', description, ',') 
IN (SELECT `id` FROM `parts` WHERE `description` LIKE '%{$search}%')

Can anybody help?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
PlasmaDan
  • 475
  • 8
  • 14
  • 1
    you should refactor your database introduce a table called product_part and have 2 columns product_id, part_id. and get rid of the comma separated field its inefficient. – DevZer0 Jun 23 '13 at 06:22
  • 1
    To add on to what DevZer0 is saying, the term you're looking for is "many to many" or "junction" table. [Look it up](http://en.wikipedia.org/wiki/Junction_table) -- this is definitely what you want to be doing here. – mpen Jun 23 '13 at 06:29
  • Assume that isn't an option. Each product has multiple parts, and each part is used with multiple products, with varying numbers of each. The only way I can keep this relationship is using the method above. – PlasmaDan Jun 23 '13 at 18:36

1 Answers1

0

I would perhaps try a combination of LOCATE() and SUBSTR(). I work mainly in MSSQL which has CHARINDEX() that I think works like MySQL's LOCATE(). It is bound to be messy. Are there a variable number of elements in the parts-list field?

Tony
  • 801
  • 1
  • 7
  • 22
  • Yes there are a varying number of each. Basically the structure is set-up so that the parts table holds all the information, and the products table is just used to collate the data when displaying a page. What I need is a search feature that will search the parts table for matched results, refer to the products that have those parts in the parts-list column, and return the row data for each of those products. – PlasmaDan Jun 23 '13 at 18:39
  • Maybe give this a look. http://stackoverflow.com/questions/7737970/procedure-to-loop-through-comma-separated-string-is-not-working – Tony Jun 23 '13 at 19:06