How would I do this? 2

[youradds]

Hi,

Sorry for the non very descriptive title - wasn't sure what to call this thread =)

Basically, I have a table:

IngredientSearch
   recipe_id
   ingredient

Now, what I wanna do - is a search based on a + and -

For example:

+sugar
+cream
-nuts

This would need to query that table (which would have 1 entry per recipe, per ingredient) ... and then once I've got that, I can simply parse it into a template

I've done similar stuff to this before - but not quite the same, so I'm at a bit of a loss as to how to approach this.

Any suggestions are much appreciated

TIA

Andy

 

[youradds]

Eugh good point Now works perfectly - thanks again for your time

also, you need a unique index or primary key on your table -- in the example you just gave, there are duplicate rows, and the same ingredent should never be included more than once in the same recipe

Good point - will add that unique index, and also add some code that checks if the ingredient exists already for that link

Cheers

Andy

 

[r937]

and also add some code that checks if the ingredient exists already for that link

you don't need to do that, the database will do it for you




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Its ok - only took a few seconds (when the script runs, it indexes all the words in the ingredients, minus the filter ones we have - like "and")

Then when adding it, I just did $seen_words->{$tmp} = 1, and added a "if" clause around the code, so it that exists it will skip that one. Then just reset that variable once we move onto the next ingredint

Thans again

Andy

 

[r937]

...it indexes all the words in the ingredients"

that's a unique approach -- how do you tell the difference between a pinch of pepper and a pound of pepper? do "pinch" and "pound" get listed as ingredients? how do you, in fact, pull out the quantities?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

We pull out all the quantities, general words (commads, dots, "and", "or" "pinch" "gram" etc) .. and then when someone searches, all they are doing is look for recipies which do/dont include certain words.

For example, if you can't eat tomato's (or just don't like them), but are looking for a pasta based dish, you would search based on that Its not 100% accurate, but its better than the search system we have currently (which only works based on the recipie title)

Cheers

Andy

 

[youradds]

Sorry, me again

I'm now just trying to give the option to use AND/OR bool

For example, they could do:

includes: tomato AND pasta
doesnt include: water

OR:

includes: tomato OR pasta
doesn't include: water

Is this even possible? I tried a few things, but I either get SQL errors, or just no results

TIA

Andy

 

[r937]

we've done these already, haven't we?

includes: tomato AND pasta
doesnt include: water

SELECT link_id 
  FROM SearchIngredients 
 WHERE ingredient LIKE '%tomato%' 
    OR ingredient LIKE '%pasta%' 
    OR ingredient LIKE '%water%'
GROUP 
    BY link_id 
HAVING COUNT(CASE WHEN ingredient LIKE '%tomato%' 
                    OR ingredient LIKE '%pasta%' 
                  THEN 'ok' ELSE NULL END) = 2    
   AND COUNT(CASE WHEN ingredient LIKE '%water%'
                  THEN 'ok' ELSE NULL END) = 0

includes: tomato OR pasta
doesn't include: water

SELECT link_id 
  FROM SearchIngredients 
 WHERE ingredient LIKE '%tomato%' 
    OR ingredient LIKE '%pasta%' 
    OR ingredient LIKE '%water%'
GROUP 
    BY link_id 
HAVING COUNT(CASE WHEN ingredient LIKE '%tomato%' 
                    OR ingredient LIKE '%pasta%' 
                  THEN 'ok' ELSE NULL END) >= 1    
   AND COUNT(CASE WHEN ingredient LIKE '%water%'
                  THEN 'ok' ELSE NULL END) = 0

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Aaah, it was the >= 1 that was messing me up. Guess that teaches me for doing such a long day

Thanks again - working perfectly now (I would do another star, but got a feeling they may think I'm spamming the system, as thats about 3 I've done to you in this thread lol)

Cheers

Andy