SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast drisley's Avatar
    Join Date
    Jun 2001
    Location
    Florida
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Challenging Search Problem

    Or at least it is for me right now.

    I have a situation for a client where I am posting businesses into a database. Each database has the capibility to be posted into multiple categories. What I've done is that on the "Add Business" page, they use a multiple select box to choose which categories they want to put the business in. In the form processor, I "implode" the category array and turn it into a comma-delineated list, such as "2,3,4,5", and I store that string into the categories field.

    First, I'm not exactly sure if this is the right way to go about it. I have some pages that specifially query based on "WHERE category=x" and with a comma-delineated list, this won't work. Ideas?

    Second, and what really complicates it, is that on the "search" pages, I'm trying to allow people to search based on multiple categories. This forms a category array to search with. I have a problem searching a comma-delineated list in the database using multiple elements in an array. Complicated (and its late).

    Something tells me I'm approaching this wrong. I want to allow businesses to be posted in multiple categories, have category pages that display all businesses in certain categories, as well as search based on multiple selections.

    Can you guys shed any light on this?
    David Risley
    Web Developer, Web Publisher, Blogger, Tech Dude
    PC Mechanic | WebbyOnline

  2. #2
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I would do would be this.

    Create another table, in this have 2 columns, and you may as well index them both at the same time. Put in the ID from where business is stored in that table. And put the category ID in next to it.

    And your category table, will have the ID and its name, ie: web design.

    So you end up with a many to many relationship I think it is called.
    So a schema of the layout would be:

    category
    categoryID || categoryname

    business
    businessID || thestuffyouhavenow

    catbus
    categoryID || businessID


    I hope I have kinda explained it in some form of english.


    You can then reference from the categories and find the relevant businesses, and when you add businesses in more than one category, you are simply inserting into the catbus table multiple times for each categoryID

  3. #3
    SitePoint Enthusiast drisley's Avatar
    Join Date
    Jun 2001
    Location
    Florida
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I should have remembered that from database design studies I've done. After some sleep, the world seems clearer.

    Thanks...I'll see what I can do with it.
    David Risley
    Web Developer, Web Publisher, Blogger, Tech Dude
    PC Mechanic | WebbyOnline


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
  •