Searching Delimited Fields in MySQL

[mobilebadboy]

I'm trying to categorize some restaurants in a table. Some of the restaurants may fall into more than one category so I can't just set a category for each restaurant and pull them distinctly from that table. So I'm going to create a separate table for the categories and assign each an ID.

Then for each restaurant listing I may have something like (1,4,7 representing the cats):

Restaurant|cats|Address|CityState|etc|etc
McDonalds|1,4,7|123 Pine St.|Mobile, AL|etc|etc

Let's say 4 represents "Fast Food", so how would I, when I pull the Fast Food category ID of 4, search the 'cats' field in the restaurants table and pull each one with 4 in the cats field?

Or is there a better way of doing this?

[Todd W]

There's a better way.

You need these tables.
Categories
id | category_name | category_description |

Then create a table called rest_cats like:
cat_id | rest_id
Then make a new row for each category a restaurant is in.

Then when you do lookups you do joins.

Your way requires more overhead when doing queries and really slows it down, and is basically the wrong way to do it. Google database normalization, there may be even more data you can split into another table(s) to make it faster, easier to work with, and easier to edit in the future.

-Todd

[Chris]

Ya, you have a textbook many::many relationship and the proper way to do it is as Todd laid out with a relationship based table.

[rpanella]

If there are only a few categories which will almost never change, you could probably get away with using a SET (http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html) for the category field, which would simplify it a little.

If you will have lots of categories that can change the best (and proper) way would be to use what Todd and Chris said.
________
Yamaha phazer history (http://www.yamaha-tech.com/wiki/Yamaha_Phazer)

[mobilebadboy]

That's what I was trying to avoid. Guess this project goes on the back burner for now.

There's going to be a few hundred restaurants to begin with that I'm going to have to create manually, not interested in an additional few thousand rows to compensate for every category.

[Chris]

Wouldn't your system automate the row creation?

[mobilebadboy]

I have no initial data to go from. This project I'm just now starting from scratch. I'm creating the listings manually in a spreadsheet which I'll then import into my database. That in itself is going to take days. Then I'm going to have to create the categories as I go, based on the restaurants I add to the list.

Basically I'm fixing to catalog all of the restaurants in my city (as part of a larger project). 514 by the rough count I just took from the latest phone book, and I know of several that aren't listed.

It's going to be more than just restaurant name, it's going to be full address, phone number, broad food types, and other pieces of info. That's why I was trying to lighten the already heavy load.

I think I'm just going to pick one cat for each, and add that to each listing. Then I can pull distinctly from there. Won't be as grand, but will work nearly the same.

[Todd W]

Why not mass assign categories once you get all the companies added to the database?

You can also most likely purchase the data you are going to manually enter too and even save time/money.

-Todd

[rpanella]

Instead of inputing the data to a spreadsheet, it would be pretty easy to set up a simple form with fields for the name, address, etc, and then checkboxes for the possible categories, and then u click submit and it fills the tables with all the info and category relations as talked about above. Rinse and repeat.

It would take just a few mins to setup the input script and would make the data entry much faster than using a spreadsheet.
________
Hemp marijuana (http://marijuanahemp.com)

[Chris]

Ya, I'm with Russell. You're making things overly hard on yourself by putting it into a spreadsheet.

First, come up with basic categories, you know them...

Fast Food
Mexican
Italian
Seafood
Casual Dining
Steakhouse
Chinese
Japanese
BBQ
Middle Eastern
Indian
Greek
Bar & Grill
Pub
Bakery
Deli


Then, make your input form for all the singular details. At the bottom include a printout of all categories with checkboxes next to them, check which categories it belongs too. Beneath that have a blank for Add New Category with a textbox where you can optionally add it to a new category.

Hit submit... goes to your PHP script.

Step 1, insert all singular data to the database. then immediately pull it's ID using mysql_insert_id

Step 2, check for existence of new category, if exists add it, then immediately pull it's ID using mysql_insert_id.

Step 3, for each checkbox checked, (and for new category if applicable), insert into the relationship table 1 row listing the restaurant id and the category id.

Voila, done. Not a complex script.

[mobilebadboy]

Thanks guys. I believe I've got it all sorted now.

[ZigE]

There's a better way.

You need these tables.
Categories
id | category_name | category_description |

Then create a table called rest_cats like:
cat_id | rest_id
Then make a new row for each category a restaurant is in.

Then when you do lookups you do joins.

Your way requires more overhead when doing queries and really slows it down, and is basically the wrong way to do it. Google database normalization, there may be even more data you can split into another table(s) to make it faster, easier to work with, and easier to edit in the future.

-Todd

When your doing joins for this, like

select * FROM info INNER JOIN rest_cats ON info.rest_id = rest_cats.rest_id

What WHERE clause do you need to select cat_id = x ?

[rpanella]

You would do:



SELECT * FROM info INNER JOIN rest_cats ON info.rest_id = rest_cats.rest_id WHERE rest.cats.cat_id = x


This query won't give you the category names though, just their IDs. You would have to do a join with the categories table as well to have it return category names.
________
400 (http://www.dodge-wiki.com/wiki/Dodge_400)

[ZigE]

You would do:



This query won't give you the category names though, just their IDs. You would have to do a join with the categories table as well to have it return category names.

Ah right cheers. I did try that, but had another WHERE clause, and forgot to put AND after it, doh :p