Please enter at least one search word."; header("Location: {$config->_rootUri}/"); exit; } # Let's implement the PRG (Post->Redirect->Get) method here so that # users can use the back button freely without browser warnings if ( isset($_POST['doSearch']) ) { # build the query string $searchString = trim($_POST['searchString']); $queryString = "searchString=$searchString&searchType={$_POST['searchType']}&wordType={$_POST['wordType']}&foodCat={$_POST['foodCat']}&sortType={$_POST['sortType']}"; # redirect the browser to refetch this page with a GET method header("Location: {$config->_rootUri}/{$config->_thisScript}?$queryString"); exit; } # we should be here with a GET now. make sure that each value is # set, if not then send the user back to the index page. if ( (! isset($_GET['searchString'])) || (! isset($_GET['searchType'])) || (! isset($_GET['wordType'])) || (! isset($_GET['sortType'])) || (! isset($_GET['foodCat'])) ) { $_SESSION['systemMsg'] = "Some required fields were missing in your search."; # header("Location: {$config->_rootUri}/"); exit; } # Sanitize $searchString $searchString = sanitizeUserInput($_GET['searchString']); # assign the vars to the smarty template # these will simply be used to remind the user of how they searched $smarty->assign("searchString", $searchString); $smarty->assign("searchType", $_GET['searchType']); $smarty->assign("wordType", $_GET['wordType']); $smarty->assign("sortType", $_GET['sortType']); # initialize the WHERE and ORDER BY parts variables. # since the structure of the user saved meals and foods table is so diff. from the # main data tables, we need to formulate a special where clause for these tables. $where = ""; $userWhere = ""; $orderby = ""; # what type of search does the user want? if ( $_GET['searchType'] == "Exact Phrase" ) { $where = " WHERE (long_desc = '$searchString' OR comname = '$searchString') "; $userWhere = " WHERE description = '$searchString' "; } else { # if user selected 'Any Word' or 'All Words' then break up the # search string into an array, then count the array so we know # how many search strings we are dealing with then go ahead and # add the first where argument $searchStrings = preg_split("/\s+/",$searchString); $stringsCount = count($searchStrings); $where = " WHERE (long_desc LIKE '%{$searchStrings[0]}%' OR comname LIKE '%{$searchStrings[0]}%') "; $userWhere = " WHERE description LIKE '%{$searchStrings[0]}%' "; if ( $_GET['searchType'] == "All Words" ) { $logicalOp = "AND"; } else { $logicalOp = "OR"; } # append as many additional args to the where clause as there # are items in array joined with our logical operator for ( $idx = 1; $idx < $stringsCount; $idx++ ) { $where .= " $logicalOp (long_desc LIKE '%{$searchStrings[$idx]}%' OR comname LIKE '%{$searchStrings[$idx]}%') "; $userWhere .= " $logicalOp description LIKE '%{$searchStrings[$idx]}%' "; } } # if the user selected a category, add it here if ( $_GET['foodCat'] != "All" ) { $where .= " AND foodCats.fdgrp_cd = '{$_GET['foodCat']}' "; # get food category name $smarty->assign("foodCatName", getFoodCategoryName($_GET['foodCat'])); } else { $smarty->assign("foodCatName", "All"); } # build the ORDER BY part if ( $_GET['sortType'] == "Food Description" ) { $orderby = " ORDER BY foodDesc "; } elseif ( $_GET['sortType'] == "Category" ) { $orderby = " ORDER BY fdgrp_desc, foodDesc "; } elseif ( $_GET['sortType'] == "Popularity" ) { $orderby = " ORDER BY popularity DESC, foodDesc "; } # build the main query $sql = sprintf (" SELECT foodDescs.ndb_no, foodDescs.popularity AS popularity, CONCAT(foodDescs.long_desc, foodDescs.comname) AS foodDesc, foodCats.fdgrp_cd, foodCats.fdgrp_desc FROM foodDescs LEFT JOIN foodCats ON foodDescs.fdgrp_cd = foodCats.fdgrp_cd %s AND foodDescs.usda_status = 'active' ", $where ); # now append another select statement that we will UNION to the previous. this one # is looking for user saved foods and meals, and contains a UNION itself. # most of the fields are just place holders with special values that will allow # a script to later identify these as user foods and meals. ndb_nos 00001 and 00002 # are totally arbitrary and could be anything that doesn't already exist in the # database, but these numbers shouldn't be changed without also changing the # code in food_search.php that is looking for these specific numbers. for the # userFood we will embed the ndb_no, weight and quantity into a suitable query # string so that the smarty template can just plug it into the page instead of # having to parse it $sql .= sprintf (" UNION SELECT CONCAT('food=',userFoods.food,'&weight=',userFoods.weight,'&quantity=', userFoods.quantity,'&userFoodsId=',userFoods.id) AS ndb_no, userFoods.popularity AS popularity, description AS foodDesc, 'userFood' AS fdgrp_cd, 'User saved foods' AS fdgrp_desc FROM userFoods %s UNION SELECT id AS ndb_no, userMeals.popularity AS popularity, description AS foodDesc, 'userMeal' AS fdgrp_cd, 'User saved meals' AS fdgrp_desc FROM userMeals %s ", $userWhere, $userWhere ); # the ORDER BY statement must be appended to the total UNION statement last # because you can't have order by statements in each part of a statement # that includes UNIONs, at least not in MySQL. $sql .= $orderby; # a count-only statement identical to the sql above that will # give the pagination function all it needs without potentially # and uselessly fetching a massive record set only to get the # row count $paginationSql = sprintf (" SELECT (SELECT count(*) FROM foodDescs LEFT JOIN foodCats ON foodDescs.fdgrp_cd = foodCats.fdgrp_cd %s) + (SELECT count(*) FROM userFoods %s) + (SELECT count(*) FROM userFoods %s) AS rowCount ", $where, $userWhere, $userWhere ); # pass the sql to the paginator, the return value should be the # pagination navigation bar. if there is already # a page number submitted, then strip it off first and let # the function add the new one $page = (isset($_GET['page'])) ? "{$_GET['page']}" : "1"; if ( preg_match("/^(.+?)&?page=\d+$/", $_SERVER['QUERY_STRING'], $queryString) ) { $uri = "{$config->_rootUri}/{$config->_thisScript}?{$queryString[1]}"; } else { $uri = "{$config->_rootUri}/" . basename($_SERVER['REQUEST_URI']); } $pageOffset = (($page * $config->_recordsPerPage) - $config->_recordsPerPage); # start with a blank pageNav $pageNav = getPagination($page, $pageOffset, $uri, $paginationSql); # pageNav was passed by reference to the function so should be # suitably modified $smarty->assign("pageNav", $pageNav); # select a limited rowset $db->SelectLimit($sql, $config->_recordsPerPage, $pageOffset); # if there are query results then proceed. the query above already picks # out foods that have at least one partial word match, perhaps more. # therefore below we really only need to weed out records based on whether # the user select "Full Word" or "Partial Word" match. if the user selected # to sort by category then the records will be grouped in the array # $searchResults with an array index which is equivalent to the food's # category id. when displaying the results to the user this will allow us # to break the results out grouped in categories, otherwise we just dump # all the search results into a single array and will not display the # results grouped by category if ( $db->_rowCount > 0 ) { $idx = 0; # an index for each array element in the returned results # act on result set of above query based on whether user selected # 'Full Word' or 'Partial Word' foreach ( $db->_rows as $row ) { # trim any extra commas from the end of foodDesc, as may appear # due to the concatenation of long_desc and comname with a comma # where comname has no value, which it frequently doesnt' $row['foodDesc'] = trim($row['foodDesc'], ", "); # tracks how many, if any, search string words match words from # concatenated query field 'foodDesc' $matchCount = 0; if ( $_GET['wordType'] == "Full Word" ) { # dump each individual word of the field 'foodDesc' into an array $words = preg_split("/\W+/", $row['foodDesc']); # start a foreach loop on individual strings from users search string foreach ( $searchStrings as $searchString ) { # start a foreach loop on individual words found in field # 'foodDesc' from query foreach ($words as $word) { if ( strcasecmp($searchString, $word) == 0 ) { # if there is a match then increment our counter variable by 1 $matchCount++; } } } # if the search type was 'Any Word', then if only 1 of the search # string words matched a word from the field 'foodDesc' we have a # match and should add the record, else if the search type was # 'All Words' and all of the search string words matched then add # the record, else don't add anything. if ( (($_GET['searchType'] == "Any Word") && ($matchCount > 0)) || (($_GET['searchType'] == "All Words") && ($matchCount == count($searchStrings))) ) { if ( $_GET['sortType'] == "Category" ) { $searchResults[$row['fdgrp_cd']]['foodCatName'] = $row['fdgrp_desc']; $searchResults[$row['fdgrp_cd']]['searchResults'][$idx]['food'] = $row['ndb_no']; $searchResults[$row['fdgrp_cd']]['searchResults'][$idx]['foodDesc'] = $row['foodDesc']; } else { $searchResults[$idx]['food'] = $row['ndb_no']; $searchResults[$idx]['category'] = $row['fdgrp_cd']; $searchResults[$idx]['foodDesc'] = $row['foodDesc']; } } } else { # the search type must have been "Partial Word" so just add the row if ( $_GET['sortType'] == "Category" ) { $searchResults[$row['fdgrp_cd']]['foodCatName'] = $row['fdgrp_desc']; $searchResults[$row['fdgrp_cd']]['searchResults'][$idx]['food'] = $row['ndb_no']; $searchResults[$row['fdgrp_cd']]['searchResults'][$idx]['foodDesc'] = $row['foodDesc']; } else { $searchResults[$idx]['food'] = $row['ndb_no']; $searchResults[$idx]['category'] = $row['fdgrp_cd']; $searchResults[$idx]['foodDesc'] = $row['foodDesc']; } } $idx++; } if ( isset($searchResults) ) { $smarty->assign("searchResults", $searchResults); } } # grab the various parts. these sections are not printed to the screen # but rather dumped into smarty variables that will simply be printed # in the template, so the order doesn't matter here at the moment require("header.php"); require("sidebar_left.php"); require("sidebar_right.php"); require("footer.php"); $smarty->display("food_search.tpl"); ?>