Results 1 to 14 of 14

Thread: Searching Delimited Fields in MySQL

  1. #1
    Roll Tide! mobilebadboy's Avatar
    Join Date
    Apr 2004
    Location
    Mobile, AL
    Posts
    428

    Searching Delimited Fields in MySQL

    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?
    Shawn Kerr .com

  2. #2
    4x4
    Join Date
    Oct 2004
    Posts
    1,043
    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

  3. #3
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    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.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  4. #4
    Registered
    Join Date
    Aug 2006
    Location
    Sacramento, CA
    Posts
    208
    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/...-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
    Last edited by rpanella; 03-17-2011 at 11:04 AM.

  5. #5
    Roll Tide! mobilebadboy's Avatar
    Join Date
    Apr 2004
    Location
    Mobile, AL
    Posts
    428
    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.
    Shawn Kerr .com

  6. #6
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    Wouldn't your system automate the row creation?
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  7. #7
    Roll Tide! mobilebadboy's Avatar
    Join Date
    Apr 2004
    Location
    Mobile, AL
    Posts
    428
    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.
    Shawn Kerr .com

  8. #8
    4x4
    Join Date
    Oct 2004
    Posts
    1,043
    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

  9. #9
    Registered
    Join Date
    Aug 2006
    Location
    Sacramento, CA
    Posts
    208
    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
    Last edited by rpanella; 03-17-2011 at 11:04 AM.

  10. #10
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    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.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  11. #11
    Roll Tide! mobilebadboy's Avatar
    Join Date
    Apr 2004
    Location
    Mobile, AL
    Posts
    428
    Thanks guys. I believe I've got it all sorted now.
    Shawn Kerr .com

  12. #12
    Registered ZigE's Avatar
    Join Date
    Dec 2006
    Posts
    122
    Quote Originally Posted by ToddW View Post
    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 ?
    Last edited by ZigE; 12-19-2007 at 07:04 PM.

  13. #13
    Registered
    Join Date
    Aug 2006
    Location
    Sacramento, CA
    Posts
    208
    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
    Last edited by rpanella; 03-17-2011 at 11:05 AM.

  14. #14
    Registered ZigE's Avatar
    Join Date
    Dec 2006
    Posts
    122
    Quote Originally Posted by rpanella View Post
    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

Similar Threads

  1. Replies: 0
    Last Post: 12-22-2005, 09:42 AM
  2. Replies: 0
    Last Post: 11-23-2005, 04:49 AM
  3. Clear my misunderstanding: 1 MySQL database for many programs?
    By iKwak in forum Website Programming & Databases
    Replies: 4
    Last Post: 08-21-2005, 10:40 AM
  4. Replies: 0
    Last Post: 07-28-2005, 05:41 AM
  5. Replies: 0
    Last Post: 07-13-2005, 08:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •