Searching mysql with an array

[jimbeeer]

i have a hierarchical tier structure with folders and items. But I want each item to have the ability to be in multiple folders.

 

E.g.

 

Root

- Cafes

- - Coffee Shops

- - Takeaways

- Restaurants

- - Franchises

- Small Shops

- Stores

 

Then businesses will have multiple categories. (I.E. McDonalds might have restaurants and takeaways)

 

I've done that by creating a field within businesses called 'business_category' where it separates the multiple categories with _.

 

E.g. 12_3

 

So, the point I'm stuck on now is displaying the business within the relevant folder.

 

SELECT * FROM businesses WHERE business_category = folder_id ORDER by business_title

 

I want to explode the 12_3 into an array, then check both of them that they equal the current folderid. But the only way I can think of is to loop through the array running the query each time.

 

I know this is processor intensive so does anyone know a better way? I'm pretty confident there is one.

 

Many thanks in advance.

 

-James

[requinix]

I know this is processor intensive so does anyone know a better way? I'm pretty confident there is one.

Indeed there is. Get rid of that column you have now and replace it with an entire table. Actually, wait to get rid of it, you'll need it to import the data into the new table (once you've done that then you can kill it off). This table has a minimum of two columns: the parent category and the child... thing. One row for each distinct pair:

parent      | child
------------+----------
Restaurants | McDonalds
Takeaways   | McDonalds

(but with ID numbers of course)

[jimbeeer]

Yeah, I was thinking of doing that. Then I can just run a query on the child and parent. I may have to drop another post if I get stuck though.

[jimbeeer]

Ugh, now I'm stuck again.

 

It inserts into 'bus_parents' perfectly.

 

So I've now got:

 

bus_parent_id	| bus_parent_parent| bus_parent_child
 4	                  3	                          10
 3	                  12	                          10
 5	                  7	                          10
 6	                  6	                          10

 

bus_parent_parent = the folder id

bus_parent_child = the business id

 

Now to display it appropriately I need to get 'business_title' from 'businesses' where business_id = (then get bus_parent_child where bus_parent_parent = the current url id)

 

I presume I need to put one SELECT within another, but I'm a bit unclear how to get this and my mind is noodling as I write this.