waitingtoderail Posted July 25, 2009 Share Posted July 25, 2009 Please excuse me if this is a completely obvious answer, I'm a complete php newbie in a database management class. I've been searching message boards and going through Peachpit Press manuals all day and can't make heads or tails of how to do it. Here's my situation: I'm trying to create a search form with four variables that will return results no matter how many of the fields are filled in. For example, a state field and a city field. If the searcher only fills in state, I want it to come back with all the cities listed in the database for that state. So the search form and php form I created work, but only if I fill in all fields. Here's the code for the php form, can anyone help me out? Baby steps... $sql="select farm_name, farm_address, farm_city, farm_state, farm_zip, farm_description from farm_farm, farm_type, farm_product, farm_farmproduct where farm_product.product_id=farm_farmproduct.product_id and farm_farm.farm_id=farm_farmproduct.farm_id and farm_farm.type_id=farm_type.type_id and (farm_city like \"$_POST[city]\") and (farm_state like \"$_POST[state]\") and (farm_farm.type_id like \"$_POST[farmtype]\") and (farm_product.product_name like \"$_POST[product]\")" ; $result=mysql_query($sql); if($result==0) echo ("<p>Error".mysql_errno()." is ".mysql_error()."</p>"); else { echo("<table border=\"5\">"); for($i=0;$i<mysql_num_rows($result);$i++) { echo("<tr>"); $row_array=mysql_fetch_row($result); for($j=0;$j<mysql_num_fields($result);$j++) { echo("<td>".$row_array[$j]."</td>"); } echo("</tr>"); } echo("</table>"); } ?> Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/ Share on other sites More sharing options...
.josh Posted July 25, 2009 Share Posted July 25, 2009 ... (column like 'somethin') OR (column like 'something') OR etc... Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882906 Share on other sites More sharing options...
waitingtoderail Posted July 25, 2009 Author Share Posted July 25, 2009 So just change all the ands to ors? Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882907 Share on other sites More sharing options...
.josh Posted July 25, 2009 Share Posted July 25, 2009 not all of them...only the ones in-between your like statements. Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882908 Share on other sites More sharing options...
waitingtoderail Posted July 25, 2009 Author Share Posted July 25, 2009 not all of them...only the ones in-between your like statements. Well, that worked, but it is returning each result into the table about 75 times - I get a huge table with repeating records. Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882910 Share on other sites More sharing options...
.josh Posted July 25, 2009 Share Posted July 25, 2009 $sql="select farm_name, farm_address, farm_city, farm_state, farm_zip, farm_description from farm_farm, farm_type, farm_product, farm_farmproduct where farm_product.product_id=farm_farmproduct.product_id and farm_farm.farm_id=farm_farmproduct.farm_id and farm_farm.type_id=farm_type.type_id and ((farm_city like \"$_POST[city]\") or (farm_state like \"$_POST[state]\") or (farm_farm.type_id like \"$_POST[farmtype]\") or (farm_product.product_name like \"$_POST[product]\"))" ; Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882912 Share on other sites More sharing options...
lynxus Posted July 25, 2009 Share Posted July 25, 2009 I may be missunderstanding the problem, But i would do this: I would build the SQL select on the fly based on the submitted info. Ie, only select for stuff that has been submitted. So if i would search for age sex location, but only filled in age location. id only do a select * from foo where age = bar and location = bar; if i filled all in , then my statement would be select * from foo where age = bar and sex = bar and location = bar; if that made sense? Probably not the best way to do it, but thats how i would Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882914 Share on other sites More sharing options...
waitingtoderail Posted July 25, 2009 Author Share Posted July 25, 2009 $sql="select farm_name, farm_address, farm_city, farm_state, farm_zip, farm_description from farm_farm, farm_type, farm_product, farm_farmproduct where farm_product.product_id=farm_farmproduct.product_id and farm_farm.farm_id=farm_farmproduct.farm_id and farm_farm.type_id=farm_type.type_id and ((farm_city like \"$_POST[city]\") or (farm_state like \"$_POST[state]\") or (farm_farm.type_id like \"$_POST[farmtype]\") or (farm_product.product_name like \"$_POST[product]\"))" ; This worked, but if I search on more than one field, it gives me ALL the results for each field search. What I need is, if I search for "RI" in state, and "Goats" in product, it will only return farms that sell goats in Rhode Island. As it stands it returns all farms in RI and all farms that sell goats. Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882915 Share on other sites More sharing options...
waitingtoderail Posted July 25, 2009 Author Share Posted July 25, 2009 I may be missunderstanding the problem, But i would do this: I would build the SQL select on the fly based on the submitted info. Ie, only select for stuff that has been submitted. So if i would search for age sex location, but only filled in age location. id only do a select * from foo where age = bar and location = bar; if i filled all in , then my statement would be select * from foo where age = bar and sex = bar and location = bar; if that made sense? Probably not the best way to do it, but thats how i would This sounds like what I need but is so far over my newbie head that I don't get it. What does the * represent? Foo is the database name? Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882916 Share on other sites More sharing options...
.josh Posted July 26, 2009 Share Posted July 26, 2009 okay then you are going to have to go back to using AND instead of OR but only querying for the ones selected (what lynxus said) for example: $sql="select farm_name, farm_address, farm_city, farm_state, farm_zip, farm_description from farm_farm, farm_type, farm_product, farm_farmproduct where farm_product.product_id=farm_farmproduct.product_id and farm_farm.farm_id=farm_farmproduct.farm_id and farm_farm.type_id=farm_type.type_id"; $sql .= (trim($_POST['city']) != "")? " and (farm_city like \"$_POST[city]\")" : ""; $sql .= (trim($_POST['state']) != "")? " and (farm_state like \"$_POST[state]\")" : ""; $sql .= (trim($_POST['farmtype']) != "")? " and (farm_farm.type_id like \"$_POST[farmtype]\")" : ""; $sql .= (trim($_POST['product']) != "")? " and (farm_product.product_name like \"$_POST[product]\")" : ""; Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882917 Share on other sites More sharing options...
waitingtoderail Posted July 26, 2009 Author Share Posted July 26, 2009 okay then you are going to have to go back to using AND instead of OR but only querying for the ones selected (what lynxus said) for example: $sql="select farm_name, farm_address, farm_city, farm_state, farm_zip, farm_description from farm_farm, farm_type, farm_product, farm_farmproduct where farm_product.product_id=farm_farmproduct.product_id and farm_farm.farm_id=farm_farmproduct.farm_id and farm_farm.type_id=farm_type.type_id"; $sql .= (trim($_POST['city']) != "")? " and (farm_city like \"$_POST[city]\")" : ""; $sql .= (trim($_POST['state']) != "")? " and (farm_state like \"$_POST[state]\")" : ""; $sql .= (trim($_POST['farmtype']) != "")? " and (farm_farm.type_id like \"$_POST[farmtype]\")" : ""; $sql .= (trim($_POST['product']) != "")? " and (farm_product.product_name like \"$_POST[product]\")" : ""; Thanks so much for your help here, this worked, but is again giving me the same results multiple times. It's strange though because it's not giving me nearly as many repetitions and not all of them repeat. Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882923 Share on other sites More sharing options...
.josh Posted July 26, 2009 Share Posted July 26, 2009 maybe you have multiple rows with the same data? You are pulling stuff from a lot of diff tables...same column/value criteria match up for more than 1 table? Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882925 Share on other sites More sharing options...
waitingtoderail Posted July 26, 2009 Author Share Posted July 26, 2009 maybe you have multiple rows with the same data? You are pulling stuff from a lot of diff tables...same column/value criteria match up for more than 1 table? No, the data is clean. Could it be due to a linking table? The farm_farmproduct is one. Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882928 Share on other sites More sharing options...
waitingtoderail Posted July 26, 2009 Author Share Posted July 26, 2009 OK, it's definitely the linking table. The results are showing multiple times because each time the linking table comes up with a product on a particular farm, it lists the whole thing again. Is there a way to stop this? I'll start poring over the books again.... Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882943 Share on other sites More sharing options...
.josh Posted July 26, 2009 Share Posted July 26, 2009 hmm i think you might want to look into mysql's distinct() Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-882952 Share on other sites More sharing options...
waitingtoderail Posted July 26, 2009 Author Share Posted July 26, 2009 hmm i think you might want to look into mysql's distinct() You, my friend, have saved my life. Thanks so much! Quote Link to comment https://forums.phpfreaks.com/topic/167438-solved-search-form-with-blank-fields-how-do-i-get-it-to-return-results/#findComment-883193 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.