richiejones24 Posted December 14, 2011 Share Posted December 14, 2011 Can anyone tell me the best way to handle empty search field on a mysql Query? The query below if $name is left blank but the other fields are filled it shows no results, currently i use if.... else.... but that means i have a lot of code replicated, is there a better way? $sql = "SELECT private.username, se.age, se.ro, se.suc, se.bu, se.fu, com.in, com.ol, se.di, se.bo, se.uin FROM Reg_Profile_public AS se INNER JOIN Reg_Profile_Private AS private USING (uin) INNER JOIN Reg_Profile_public_Com AS com USING (uin) WHERE se.age BETWEEN '$low' AND '$high' AND se.ro=''$name"; Quote Link to comment https://forums.phpfreaks.com/topic/253126-mysql-empty-search-field/ Share on other sites More sharing options...
Psycho Posted December 14, 2011 Share Posted December 14, 2011 If I have multiple parameters for my search clause I typically include logic for each clause and them implode them at the end. I usually use checks on the submitted values and not the variables that are set from those submitted values. Since you don't show where those variable are set I'll just use the variables. $whereParts = array(); if(!empty($low) && !empty($high)) { //Both low and high have values $whereParts[] = "se.age BETWEEN '$low' AND '$high'"; } elseif(!empty($low)) { //Only low has a value $whereParts[] = "se.age >= '$low'"; } elseif(!empty($high)) { //Only highhas a value $whereParts[] = "se.age <= '$high'"; } if(!empty($name)) { $whereParts[] = "se.ro='$name'"; } //Create the base query $sql = "SELECT private.username, se.age, se.ro, se.suc, se.bu, se.fu, com.in, com.ol, se.di, se.bo, se.uin FROM Reg_Profile_public AS se INNER JOIN Reg_Profile_Private AS private USING (uin) INNER JOIN Reg_Profile_public_Com AS com USING (uin)"; //Add the WHERE conditions if(count($whereParts)>0) { $sql .= "WHERE " . implode(' AND ', $whereParts); } Quote Link to comment https://forums.phpfreaks.com/topic/253126-mysql-empty-search-field/#findComment-1297686 Share on other sites More sharing options...
QuickOldCar Posted December 14, 2011 Share Posted December 14, 2011 I like mjdamato's method as that will only query the selected or populated items from the form. I usually like to insert default values if none were inserted, if I want all the results to be displayed. But take this simple example checking if all the fields of the form have a value. <html> <body> <form action="" method="post"> Name: <input type="text" name="name" /> Age: <input type="text" name="age" /> City: <input type="text" name="city" /> <input type="submit" /> </form> <?php if(isset($_POST)){ foreach($_POST as $post_data){ if(empty($post_data)){ echo "You have not completely filled out the form"; EXIT; } } print_r($_POST); } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/253126-mysql-empty-search-field/#findComment-1297688 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.