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!!!

On May 5, 6:33 am, Justin Voelker <justin.voel...@gmail.com> wrote:
> 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!!!

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

On May 5, 10:03 am, strawberry <zac.ca...@gmail.com> wrote:
> On May 5, 6:33 am, Justin Voelker <justin.voel...@gmail.com> wrote:
>
> > 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!!!
>
> Normalize your data. Don't use 5 fields. Use a separate table instead
> e.g. store_flavors(store_id*,flavor_id*)

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 Voelker wrote:
> On May 5, 10:03 am, strawberry <zac.ca...@gmail.com> wrote:
>> On May 5, 6:33 am, Justin Voelker <justin.voel...@gmail.com> wrote:
>>
>>> 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!!!
>>
>> Normalize your data. Don't use 5 fields. Use a separate table instead
>> e.g. store_flavors(store_id*,flavor_id*)
>
> 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?

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

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" <paul.laut...@btinternet.com>
wrote:
> Justin Voelker wrote:
> > On May 5, 10:03 am, strawberry <zac.ca...@gmail.com> wrote:
> >> On May 5, 6:33 am, Justin Voelker <justin.voel...@gmail.com> wrote:
>
> >>> 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!!!
>
> >> Normalize your data. Don't use 5 fields. Use a separate table instead
> >> e.g. store_flavors(store_id*,flavor_id*)
>
> > 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?
>
> By using a JOIN as you do with all of your other normalised data of course.