php complex search form to search mysql database

[nade93]

keith sent you a pm with more details

 

thanks

[kickstart]

Hi

 

Think you want to AND rather than OR the different checks of fields.

 

However if doing that you need to only check against fields which do have a selected value.

 

<?php
$query = "SELECT DISTINCT a.id
FROM jos_users a
LEFT JOIN jos_community_users b ON a.id = b.userid
LEFT JOIN jos_community_fields_values c ON b.userid = c.user_id
";
$where = "";
$where .= (empty($gender))?"":sprintf(" (c.field_id=2 and c.`value`='%s') AND",mysql_real_escape_string($gender));
$where .= (empty($cities))?"":sprintf(" (c.field_id=10 and c.`value`='%s') AND",mysql_real_escape_string($cities));
$where .= (empty($height))?"":sprintf(" (c.field_id=18 and c.`value`='%s') AND",mysql_real_escape_string($height));
$where .= (empty($build))?"":sprintf(" (c.field_id=19 and c.`value`='%s') AND",mysql_real_escape_string($build));
$where .= (empty($sexuality))?"":sprintf(" (c.field_id=20 and c.`value`='%s') AND",mysql_real_escape_string($sexuality));
$where .= (empty($lookingfor))?"":sprintf(" (c.field_id=21 and c.`value`='%s') AND",mysql_real_escape_string($lookingfor));
$where .= (empty($membertype))?"":sprintf(" (c.field_id=22 and c.`value`='%s') AND",mysql_real_escape_string($membertype));
$where .= (empty($activities))?"":sprintf(" (c.`value`='%s') AND",mysql_real_escape_string($activities));
$query .= (!empty($where))?"WHERE ".substr($where,0,-3):"";
$result = mysql_query($query) or die($query.mysql_error());
?>

 

All the best

 

Keith