Professional Web Applications Themes

Multiple fields with multiple search items - MySQL

Hello everyone: I am having a bit of a problem trying to wrap my head around a particular select statement... I have a table that has numerous stores. Beyond the normal address, city, state, zip fields, each store record has 5 fields, 1 for each for ice cream flavor carried at that particular store. I have a search box that lets you search for ice cream flavors. Right now searching for one flavor is hard enough since my select statement select all records "where flavor1 = "x" or flavor2 = 'x' or...", but I would like the ability to search ...

  1. #1

    Default Multiple fields with multiple search items

    Hello everyone: I am having a bit of a problem trying to wrap my head
    around a particular select statement... I have a table that has
    numerous stores. Beyond the normal address, city, state, zip fields,
    each store record has 5 fields, 1 for each for ice cream flavor
    carried at that particular store. I have a search box that lets you
    search for ice cream flavors. Right now searching for one flavor is
    hard enough since my select statement select all records "where
    flavor1 = "x" or flavor2 = 'x' or...", but I would like the ability to
    search for multiple flavors, and have the returned results start with
    those stores that match the most flavors searched upon. Does anyone
    have any way of doing this? Also, the page is written in PHP so if
    you have any php related way to handle this issue, I am more than
    happy to try them as well. Thank you!!!

    Justin Guest

  2. #2

    Default Re: Multiple fields with multiple search items

    On May 5, 6:33 am, Justin Voelker <com> wrote: 

    Normalize your data. Don't use 5 fields. Use a separate table instead
    e.g. store_flavors(store_id*,flavor_id*)

    strawberry Guest

  3. #3

    Default Re: Multiple fields with multiple search items

    On May 5, 10:03 am, strawberry <com> wrote: 
    >
    > Normalize your data. Don't use 5 fields. Use a separate table instead
    > e.g. store_flavors(store_id*,flavor_id*)[/ref]

    The rest of my data is normalized, this is the only piece that isn't
    because I thought it would be easier to update the fields when I know
    I am only looking at an exact set of 8 rather than every flavor for
    every store (already nearing 50 after 1 day of work). If I put them
    into a separate table how would I still perform that search?

    Justin Guest

  4. #4

    Default Re: Multiple fields with multiple search items

    Justin Voelker wrote: 
    >>
    >> Normalize your data. Don't use 5 fields. Use a separate table instead
    >> e.g. store_flavors(store_id*,flavor_id*)[/ref]
    >
    > The rest of my data is normalized, this is the only piece that isn't
    > because I thought it would be easier to update the fields when I know
    > I am only looking at an exact set of 8 rather than every flavor for
    > every store (already nearing 50 after 1 day of work). If I put them
    > into a separate table how would I still perform that search?[/ref]

    By using a JOIN as you do with all of your other normalised data of course.


    Paul Guest

  5. #5

    Default Re: Multiple fields with multiple search items

    If I understand what you are trying to do, this is how I would do it.
    I would create two tables, one with two fields, flavor_id and
    flavor_name (holding a list of all flovors offered at eny store-I
    would call this a flavors table) and another with two fields,
    flavor_id and store_id (which will tell you what flaovrs are offered
    at any given store-I would call this a linking table). With this
    configuration, you can do a search on the second table for any store
    and find out what flavors it carries. You can also do a search to see
    which stores cary a given compnation of flavors. If you want to
    identify a store that crries the most flavors of a given set, you can
    do searches for several stores and use use mysql_num_rows() to see how
    many items there are in the result for each store. By comparing
    results you can see which store has the most occurences.

    You can use a php script in combination with SQL queries to accomplish
    any of these things.

    --Kenoli

    On May 6, 12:11 pm, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]

    >
    > By using a JOIN as you do with all of your other normalised data of course.[/ref]


    Kenoli Guest

Similar Threads

  1. One Search String/Multiple Fields
    By KYMatt in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 20th, 07:16 PM
  2. PHP MySQL search same name in multiple dbase fields
    By mduke in forum Dreamweaver AppDev
    Replies: 0
    Last Post: February 28th, 09:14 PM
  3. Search multiple keywords across multiple fields
    By Darren Heinrich in forum ASP Database
    Replies: 3
    Last Post: February 6th, 05:29 AM
  4. SQL Search Multiple Fields ??
    By Ulf Wendel in forum PHP Development
    Replies: 5
    Last Post: July 5th, 02:22 PM
  5. Access Combo Box search allowing seacr on multiple fields
    By L Balogh in forum Microsoft Access
    Replies: 2
    Last Post: June 30th, 11:15 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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139