Recipe Finder
<% '-----Accept the search criteria and set default values searchTerm = Request("searchTerm") & Request("yaql") dish = Request("dish") low_fat = Request("low_fat") organic = Request("organic") spicy = Request("spicy") vegetarian = Request("vegetarian") la_pasta = Request("la_pasta") new_recipe = Request("new_recipe") gluten_free = Request("gluten_free") page_size = 10 page = Request("page") if page = "" Then current_page = 1 else current_page = cint(page) end if 'Now build the form, filling in the form with any values that have previously been entered. %><% '-----Build the SQL statement from the input criteria searchTerm = Replace(searchTerm, "'", "''") 'If the tick (') character was used replace with double-tick ('') category_line = " " low_fat_line = " " spicy_line = " " organic_line = " " vegetarian_line = " " la_pasta_line = " " new_recipe_line = " " gluten_free_line = " " 'Build the string to search through searchString = "([recipe_name] & [Pasta_Type] & [ingredient_1] & [ingredient_2] & [ingredient_3] & [ingredient_4] & [ingredient_5] & [ingredient_6] & [ingredient_7] & [ingredient_8] & [ingredient_9] & [ingredient_10] & [ingredient_11] & [ingredient_12] & [ingredient_13] & [ingredient_14] & [ingredient_15] & [ingredient_16] & [ingredient_17] & [ingredient_18] & [ingredient_19] & [ingredient_20] & [ingredient_21] & [ingredient_22] & [ingredient_23] & [ingredient_24] & [ingredient_25] & [ingredient_26] & [ingredient_27] & [ingredient_28] & [ingredient_29] & [ingredient_30])" 'If any of the check boxes or drop-down boxes were selected, include them in the SQL statement if searchTerm <> "" then searchTerm_line = "AND " & searchString & " LIKE '%" & Replace(searchTerm, " ","%' AND " & searchString & " LIKE '%") & "%' " if dish <> "" Then category_line = "AND meal_category_id1=" & dish & " " if low_fat = "true" Then low_fat_line = "AND low_fat=True " if organic = "true" Then organic_line = "AND Pasta_Type LIKE 'Organic%' " if spicy = "true" Then spicy_line = "AND spicy=True " if vegetarian = "true" Then vegetarian_line = "AND vegetarian=True " if gluten_free = "true" Then gluten_free_line = "AND Pasta_Type LIKE 'Gluten Free%' " if la_pasta = "true" Then la_pasta_line = "AND Pasta_Type LIKE 'La Pasta%' " if new_recipe = "true" Then new_recipe_line = "AND date_live > now()-90 " 'The SQL statement ... actually the mother of all SQL statements sqlTemp = "SELECT * " & _ "FROM pasta_types RIGHT JOIN recipes ON pasta_types.pasta_type_id = recipes.pasta_type_id " & _ "WHERE " & _ "((recipes.brand_id)=1) AND recipes.status_id=1 " & _ searchTerm_line & _ category_line & _ low_fat_line & _ organic_line & _ spicy_line & _ vegetarian_line & _ gluten_free_line & _ la_pasta_line & _ new_recipe_line & _ "ORDER BY recipe_name;" 'response.write "
" & sqlTemp & "
" 'Just used for testing 'searchTerm = "" 'Just used for testing runit = false if searchTerm <> "" Then runit = True if dish <> "" Then runit = True if low_fat <> "" Then runit = True if organic <> "" Then runit = True if spicy <> "" Then runit = True if vegetarian <> "" Then runit = True if la_pasta <> "" Then runit = True if new_recipe <> "" Then runit = True if gluten_free <> "" Then runit = True If runit = True Then 'Don't run the search unless a search term has been entered. set searchResultSet = server.createobject("ADODB.Recordset") searchResultSet.PageSize = page_size searchResultSet.CacheSize = page_size searchResultSet.open sqlTemp, RecipeDB, adOpenStatic, adLockReadOnly result_count = searchResultSet.RecordCount page_count = searchResultSet.PageCount if result_count = 0 Then response.write "Sorry, no recipes were found.
" else searchResultSet.AbsolutePage = current_page response.write "" & result_count & " recipes found.
" & vbnewline
response.write "Page " & current_page & " of " & page_count & "
"
For recipe_num = 1 to searchResultSet.PageSize
'For each recipe in result set, create a link that opens in a new window
newtext = ""
if searchResultSet("date_live") > now()-90 then newtext = " - New!"
response.write "" & searchResultSet("recipe_name") & "" & newtext & "
" & vbnewline
searchResultSet.MoveNext
if searchResultSet.EOF Then Exit For
Next 'recipe_num
'Navigation section
searchTerm = replace(searchTerm,"%","%25")
response.write "
" if current_page > 1 then response.write "[<< Prev] " & vbnewline if page_count > 1 then for I = 1 to page_count if I = current_page Then response.write I & " " & vbnewline else response.write "" & I & " " & vbnewline end if next 'I end if if current_page < page_count then response.write "[Next >>]" & vbnewline response.write "
" end if 'result_count = 0 searchResultSet.Close set searchResultSet = nothing end if 'searchTerm <> "" ----No event if searchTerm = "" RecipeDB.close set RecipeDB = nothing %>