co.ador Posted November 27, 2009 Share Posted November 27, 2009 <?php // WHERE clause filters $arrSQLFilters = array(); // whether or not zip codes table needs to be included $boolIncludeZipCodes = false; // Zipcode filter if(!empty($strZipCode)) { $boolIncludeZipCodes = true; $arrSQLFilters[] = sprintf( "z.zip LIKE '%s'" ,"%$strZipCode%" ); } // State filter if(!empty($strState)) { $boolIncludeZipCodes = true; $arrSQLFilters[] = sprintf( "z.state = '%s'" ,$strState ); } // Restaurants name filter if(!empty($strName)) { $arrSQLFilters[] = sprintf( "r.restaurantname LIKE '%s'" ,"%$strName%" ); }?> Quote Link to comment Share on other sites More sharing options...
akitchin Posted November 27, 2009 Share Posted November 27, 2009 ... what about this script don't you understand? is there a certain line that you can't figure out? Quote Link to comment Share on other sites More sharing options...
co.ador Posted November 27, 2009 Author Share Posted November 27, 2009 Hey guys I will try to get the code and message in sections Thank you akitchin Below is a form which search restaurants by state, zip code, name, type of food and offering, the code is working perfectly. It pulls all the states, zip code, type of food and offering data from the database, as you will see in the queries in the script. There is a query that pull the restaurant list according to the user's selections in the form. When the user get the selection in the form it suppose to display the list of restaurants names and images but I can't get to display it. Help! This is the query that search for the list of restaurants according to the user's selection in the form. Query that search for restaurants Quote Link to comment Share on other sites More sharing options...
akitchin Posted November 28, 2009 Share Posted November 28, 2009 the code you posted is going through each of the variables located in the if() conditionals, and if they are not empty, it adds a filter to the $arrSQL_filters array in the form of a WHERE clause. Quote Link to comment Share on other sites More sharing options...
co.ador Posted November 29, 2009 Author Share Posted November 29, 2009 Thank you Akitchin Below is a form which search restaurants by state, zip code, name, type of food and offering, the code is working perfectly. It pulls all the states, zip code, type of food and offering data from the database, as you will see in the queries in the script. There is a query that pull the restaurant list according to the user's selections in the form. When the user get the selection in the form it suppose to display the list of restaurants names and images but I can't get to display it. Help! This is the query that search for the list of restaurants according to the user's selection in the form. Query that search for restaurants // Build search query and embed filters <?php $strSQL = sprintf( 'SELECT r.restaurants_id ,r.restaurantname ,r.image FROM restaurants r %s %s %s' ,$boolIncludeZipCodes === true?'INNER JOIN zip_codes z ON r.restaurants_id = z.restaurants_id':'' ,empty($arrSQLFilters)?'':' WHERE '.implode(' AND ',$arrSQLFilters) ,$boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':'' ); $arrResult = mysql_query($strSQL); while($arrRow = mysql_fetch_assoc($arrResult)) { $arrRestaurants[] = $arrRow; } } ?> html frame i have set up that will display the restaurants name and image <div id="container4"> <div class="wrap"> <?php // print search query if(!empty($strSQL)) { printf('<p>%s</p>',$strSQL); foreach($arrRestaurants as $arrRestaurant) { { echo "<div class=\"shoeinfo1\"> <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" /> <h2 class=\"infohead\">". $arrRestaurant['restaurantname'] . "</h2> <div class=\"pic\"><img class=\"line\" src= ". $arrRestaurant['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div> </div>"; $i++; } if ($i > 1 && $i % 3 == 0 ) { echo "<div class=\"clearer\"></div>"; } } }?> </div> </div> the whole code <?php $strSQL = ''; $arrRestaurants = array(); // place holder form data variables $strName = ''; $strZipCode = ''; $strState = ''; $arrFoodTypes = array(); $arrOfferings = array(); // food types and offerings drop down build arrays $arrRestaurantsFoodTypes = array('values'=>array(),'output'=>array()); $arrStates = array('','AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY',); $arrRestaurantsOfferings = array(); $arrResult = mysql_query('SELECT restaurant_food_types_id,name FROM RESTAURANT_FOOD_TYPES'); while($arrRow = mysql_fetch_assoc($arrResult)) { $arrRestaurantsFoodTypes['values'][] = $arrRow['restaurant_food_types_id']; $arrRestaurantsFoodTypes['output'][] = $arrRow['name']; } $arrResult = mysql_query('SELECT restaurant_offerings_id,name FROM RESTAURANT_OFFERINGS'); while($arrRow = mysql_fetch_assoc($arrResult)) { $arrRestaurantsOfferings[] = $arrRow; } // reset form $boolReset = isset($_POST['frmSearch']) && isset($_POST['frmSearch']['reset'])?true:false; if($boolReset === false && isset($_POST['frmSearch'])) { // Extract POST variables and escape $strName = isset($_POST['frmSearch']['name'])?/*mysql_real_escape_string(*/$_POST['frmSearch']['name']/*)*/:''; $strZipCode = isset($_POST['frmSearch']['zipcode'])/*mysql_real_escape_string(*/?$_POST['frmSearch']['zipcode']/*)*/:''; $strState = isset($_POST['frmSearch']['state'])/*mysql_real_escape_string(*/?$_POST['frmSearch']['state']/*)*/:''; $arrFoodTypes = isset($_POST['frmSearch']['food_types'])?$_POST['frmSearch']['food_types']:array(); $arrOfferings = isset($_POST['frmSearch']['offerings'])?$_POST['frmSearch']['offerings']:array(); // WHERE clause filters $arrSQLFilters = array(); // whether or not zip codes table needs to be included $boolIncludeZipCodes = false; // Zipcode filter if(!empty($strZipCode)) { $boolIncludeZipCodes = true; $arrSQLFilters[] = sprintf( "z.zip LIKE '%s'" ,"%$strZipCode%" ); } // State filter if(!empty($strState)) { $boolIncludeZipCodes = true; $arrSQLFilters[] = sprintf( "z.state = '%s'" ,$strState ); } // Restaurants name filter if(!empty($strName)) { $arrSQLFilters[] = sprintf( "r.restaurantname LIKE '%s'" ,"%$strName%" ); } // Food types filter if(!empty($arrFoodTypes) && !empty($arrFoodTypes[0])) { $arrSQLFilters[] = sprintf( 'r.restaurants_id IN (SELECT DISTINCT restaurants_id FROM restaurants_restaurant_food_types WHERE restaurants_food_types_id IN (%s) GROUP BY restaurants_id HAVING COUNT(DISTINCT restaurants_id) = %u)' ,/*mysql_real_escape_string(*/ implode(',',$arrFoodTypes) /*)*/ ,count($arrFoodTypes) ); } // Offerings Filter ie. eat-in, lunch, dinner, etc if(!empty($arrOfferings)) { $arrSQLFilters[] = sprintf( 'r.restaurants_id IN (SELECT DISTINCT restaurants_id FROM restaurants_to_restaurant_offerings WHERE restaurant_offerings_id IN (%s) GROUP BY restaurants_id HAVING COUNT(DISTINCT restaurants_id) = %u)' ,/*mysql_real_escape_string(*/ implode(',',$arrOfferings) /*)*/ ,count($arrOfferings) ); } // Build search query and embed filters $strSQL = sprintf( 'SELECT r.restaurants_id ,r.restaurantname ,r.image FROM restaurants r %s %s %s' ,$boolIncludeZipCodes === true?'INNER JOIN zip_codes z ON r.restaurants_id = z.restaurants_id':'' ,empty($arrSQLFilters)?'':' WHERE '.implode(' AND ',$arrSQLFilters) ,$boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':'' ); $arrResult = mysql_query($strSQL); while($arrRow = mysql_fetch_assoc($arrResult)) { $arrRestaurants[] = $arrRow; } } ?> <div id="wrapper"> <form class="abajo" name="frmSearch" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST"> <fieldset class="primero"> <legend class="primerosub">Find A Restuarant</legend> <fieldset class="segundo1" style="border-color:#FFFFFF" > <legend>Location Details</legend> <ol> <li class="restaurants-name"> <label for="restaurants-name">Name</label> <input type="text" name="frmSearch[name]" value="<?php echo $strName; ?>" id="restaurants-name"> </li> <li class="restaurants-zipcode"> <label for="restaurants-zipcode">Zip</label> <input type="text" name="frmSearch[zipcode]" value="<?php echo $strZipCode; ?>" maxlength="5" id="restaurants-name"> </li> <?php if(!empty($arrStates)) { ?> <li class="restaurants-state"> <label for="restaurants-state">State</label> <select name="frmSearch[state]" id="restaurants-state"> <?php foreach($arrStates as $strStateAbb) { printf( '<option value="%s"%s>%s</option>' ,$strStateAbb ,strcmp($strState,$strStateAbb) == 0?' selected="selected"':'' ,strcmp($strStateAbb,'')==0?'--':$strStateAbb ); } ?> </select> </li> <?php } ?> <?php if(!empty($arrRestaurantsFoodTypes)) { ?> <li class="restaurants-food-types"> <label for="restaurants-food-types">Food Type</label> <select name="frmSearch[food_types][]" id="restaurants-food-types"> <?php foreach($arrRestaurantsFoodTypes['values'] as $intIndex=>$intFoodTypesId) { printf( '<option value="%s"%s>%s</option>' ,$intFoodTypesId ,in_array($intFoodTypesId,$arrFoodTypes)?' selected="selected"':'' ,$arrRestaurantsFoodTypes['output'][$intIndex] ); } ?> </select> </li> </ol> <?php } ?> </fieldset> <?php if(!empty($arrRestaurantsOfferings)) { ?> <fieldset class="tercero" style="" > <legend>Services</legend> <?php foreach($arrRestaurantsOfferings as $arrRestaurantsOffering) { printf( '<ol><li class="restaurants-offerings-%u"> <input type="checkbox" name="frmSearch[offerings][]" value="%u" id="restaurants-offerings-%u"%s> <span for="restaurants-offerings-%u" class="checkboxes23">%s</span> </li></ol>' ,$arrRestaurantsOffering['restaurant_offerings_id'] ,$arrRestaurantsOffering['restaurant_offerings_id'] ,$arrRestaurantsOffering['restaurant_offerings_id'] ,in_array($arrRestaurantsOffering['restaurant_offerings_id'],$arrOfferings)?' checked="checked"':'' ,$arrRestaurantsOffering['restaurant_offerings_id'] ,$arrRestaurantsOffering['name'] ); } ?> <?php } ?></fieldset> <li class="submit"> <input type="submit" value="Submit" name="frmSearch[submit]"> </li> <li class="reset"> <input type="submit" value="reset" name="frmSearch[reset]"> </li> </ul> </fieldset> </form> </div> <div id="container4"> <div class="wrap"> <?php // print search query if(!empty($strSQL)) { printf('<p>%s</p>',$strSQL); foreach($arrRestaurants as $arrRestaurant) { { echo "<div class=\"shoeinfo1\"> <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" /> <h2 class=\"infohead\">". $arrRestaurant['restaurantname'] . "</h2> <div class=\"pic\"><img class=\"line\" src= ". $arrRestaurant['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div> </div>"; $i++; } if ($i > 1 && $i % 3 == 0 ) { echo "<div class=\"clearer\"></div>"; } } }?> </div> </div> I have been trying for a while to figure out how to display the data, I can see all the queries and php codes inside the script but seem to lack some organization of my part to get it to display. Any suggestion and help will be greatly appreciated Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.