brem13 Posted May 19, 2010 Share Posted May 19, 2010 ok, i got a problem i am using php statements to determine the outcome of a mysql search, its taking input from a form with stats about yourself and its like a search page i guess. i think the code looks ok but im getting errors, probably because of the query? any help? if($hair == "Any"){ $hairSQL = ""; $and1 = ""; } else{ $hairSQL = "hair='$hair'"; $and1 = "AND"; } if($eyes == "Any"){ $eyeSQL = ""; $and2 = ""; } else{ $eyeSQL = "eye='$eyes'"; $and2 = "AND"; } if($bodyType == "Any"){ $bodyTypeSQL = ""; $and3 = ""; } else{ $bodyTypeSQL = "bodyType='$bodyType'"; $and3 = "AND"; } if($ethnicity == "Any"){ $ethnicitySQL = ""; $and4 = ""; } else{ $ethnicitySQL = "ethnicity='$ethnicity'"; $and4 = "AND"; } if($lookingFor == "Any"){ $lookingForSQL = ""; $and5 = ""; } else{ $lookingForSQL = "lookingFor='$lookingFor'"; $and5 = "AND"; } if($hairSQL || $eyeSQL || $bodyTypeSQL || $ethnicitySQL || $lookingForSQL != "") $where = "WHERE"; else $where = ""; mysql_connect($server, $db_user, $db_pass) or die (mysql_error()); $result = mysql_db_query($database, "select * from $table $where $hairSQL $and2 $eyeSQL $and3 $bodyTypeSQL $and4 $ethnicitySQL $and5 $lookingForSQL") or die (mysql_error()); Link to comment https://forums.phpfreaks.com/topic/202219-search-result-error/ Share on other sites More sharing options...
sharp.mac Posted May 19, 2010 Share Posted May 19, 2010 why not use a switch statement to condense the overwhelming if | else structure and send a example output of your submitted form. $result = mysql_db_query($database, "select * from $table $where $hairSQL $and2 $eyeSQL $and3 $bodyTypeSQL $and4 $ethnicitySQL $and5 $lookingForSQL") or die (mysql_error()); turn the above into $sql = "select * from". $table . $where . $hairSQL . $and2 . $eyeSQL . $and3 . $bodyTypeSQL . $and4 . $ethnicitySQL . $and5 . $lookingForSQL; $result = mysql_db_query($database, $sql) or die (mysql_error()); Then debug it with echo $sql; I'll bet somewhere in that confusion you'll find where your statement is going wrong. Link to comment https://forums.phpfreaks.com/topic/202219-search-result-error/#findComment-1060349 Share on other sites More sharing options...
brem13 Posted May 19, 2010 Author Share Posted May 19, 2010 what does that do exactly? Link to comment https://forums.phpfreaks.com/topic/202219-search-result-error/#findComment-1060350 Share on other sites More sharing options...
Philip Posted May 19, 2010 Share Posted May 19, 2010 Or another way: // Populate the input vars... $hair = 'golden'; $eyes = 'blue'; $table = 'test'; // Your array of mysql column names => variable names $vars = array('hair'=>'hair','eye'=>'eyes','bodyType'=>'bodyType','ethnicity'=>'ethnicity','lookingFor'=>'lookingFor'); // Setup an array to hold whatever is going in the db $sqlArray = array(); // Loop through each variable (using the double $), if not empty... it will be added to the query foreach($vars as $mysql=>$input) { if(!empty($$input) && $$input!='Any') $sqlArray[] = $mysql . ' = "' . $$input . '"'; } // Count the number of elements that will go in the query and properly handle them if(count($sqlArray)==1) $query = "select * from {$table} where {$sqlArray[0]}"; else if(count($sqlArray)>1) $query = "select * from {$table} where ".implode($sqlArray,' and '); else $query = "select * from {$table}"; // Show the query ... you would normally run the mysql_query here echo $query; Anyways. What errors are you getting? Link to comment https://forums.phpfreaks.com/topic/202219-search-result-error/#findComment-1060356 Share on other sites More sharing options...
brem13 Posted May 19, 2010 Author Share Posted May 19, 2010 you rock! thank you for showing me that way to condense the code, it worked too btw. i cant remember what the error was, it was a mysql error, basically telling me that the code was 'select * from table WHERE AND eye='Blue' and so on, but it now works, and i will look over the code to know for future use. again, thank you! Link to comment https://forums.phpfreaks.com/topic/202219-search-result-error/#findComment-1060374 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.