How the deal with a MySQL query like this one...

listenmirndt

$SQL = "SELECT * from $table WHERE RATING >= '$minrating' AND RATING <= '$maxrating' $theorder $bigwhere AND QUED = 'Posted' AND Recipes = '$rowxxx[Recipes]' || Desserts = '$rowxxx[Desserts]' || Baking = '$rowxxx[Baking]' || Barbecue = '$rowxxx[Barbecue]'";

so my question is...

the first criteria, AND RATING, AND RATING, AND QUED are all requirements... they should all match

but the second set of criteria, AND Recipes || Desserts || Baking || Barbecue... these are suppose to be OK to match any of, they don't have to match all.

so is my MYSQL query correct?

will it require the AND RATING, AND RATING, AND QUED, and then || (OR) Recipes || Desserts || Baking...

this doesn't sound right to me...

how would i query something like this,
where certain fields are requred to match, and later fields are checked IF they match, but don't have to match,

the idea is to pull up recipes from a certain category, depending on if that category is selected... but still require that the recipe meets a certain rating critera (etc)

thanks!

Weedpacket

Keep in mind operator precedence.

mandatory AND mandatory AND mandatory AND (optional OR optional OR optional)

to require all the "mandatory" conditions to match and at least one of the "optional" conditions.

listenmirndt

thanks for the reply... but I am sure that this is working.. here is the complete query (searching in my Thai category only here)

SELECT * from WREC_OBJECTS WHERE RATING >= '2' AND RATING <= '5' AND Recipes = 'x' OR Desserts = 'x' OR Baking = 'x' OR Barbecue = 'x' OR Healthy = 'x' OR Gluten = 'x' OR Ethnic = 'x' OR French = 'x' OR Italian = 'x' OR Chinese = 'x' OR Mexican = 'x' OR Indian = 'x' OR Thai = 'Thai' OR Lebanese = 'x' OR Japanese = 'x' OR Vegetarian = 'x'

returns:

White and Dark Chocolate Layered Cake (category: thai, rated 4)

Spicy Tom Yum with Prawns Recipe (category: thai, rated 1)

Spicy Sour Chicken (category: thai, rated 1)

so only the top one, rated 4 should come up here... but all three come up... and ideas?

dalecosp

Use the parentheses, like Weedpacket told you?

bradgrafelman

Heh... "I am sure that this is working.." followed by a description of why it's not working? 😉

dalecosp;11033843 wrote:
Use the parentheses, like Weedpacket told you?

Agreed. Without them, you have this:

RATING >= '2'
AND RATING <= '5'
AND Recipes = 'x'
OR Desserts = 'x'
OR Baking = 'x'
OR Barbecue = 'x'
OR Healthy = 'x'
OR Gluten = 'x'
OR Ethnic = 'x'
OR French = 'x'
OR Italian = 'x'
OR Chinese = 'x'
OR Mexican = 'x'
OR Indian = 'x'
OR Thai = 'Thai'
OR Lebanese = 'x'
OR Japanese = 'x'
OR Vegetarian = 'x'

which is the same as saying this:

( RATING >= '2'
  AND RATING <= '5'
  AND Recipes = 'x'
)
OR Desserts = 'x'
OR Baking = 'x'
OR Barbecue = 'x'
OR Healthy = 'x'
OR Gluten = 'x'
OR Ethnic = 'x'
OR French = 'x'
OR Italian = 'x'
OR Chinese = 'x'
OR Mexican = 'x'
OR Indian = 'x'
OR Thai = 'Thai'
OR Lebanese = 'x'
OR Japanese = 'x'
OR Vegetarian = 'x'

In other words, as long as Thai = 'Thai' is true, it doesn't matter what the Rating is.

On a side note, it would seem as though you're using properties/categories as columns (meaning a new property or category requires an entire schema update) which very strongly suggests that the database isn't normalized.

listenmirndt

thanks for the help guys,
i did try that,

like so:
SELECT * from WREC_OBJECTS WHERE RATING >= '2' AND RATING <= '5' (OR Recipes = 'x' OR Desserts = 'x' OR Baking = 'x' OR Barbecue = 'x' OR Healthy = 'x' OR Gluten = 'x' OR Ethnic = 'x' OR French = 'x' OR Italian = 'x' OR Chinese = 'x' OR Mexican = 'x' OR Indian = 'x' OR Thai = 'Thai' OR Lebanese = 'x' OR Japanese = 'x' OR Vegetarian = 'x') limit 5

but it fetched no results at all...

bradgrafelman

Looks like you're missing an AND before the '('.

Weedpacket

bradgrafelman wrote:

Looks like you're missing an AND before the '('.

And a spurious OR after it...