search result error

[brem13]

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());

[sharp.mac]

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.

 

 

[brem13]

what does that do exactly?

 

[Philip]

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?

[brem13]

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!