I think I figured out most of it.
My new code is:
Code: Select all
mysql_select_db($database_connHP4_php, $connHP4_php);
$query_rs_members = sprintf("SELECT members.memno, members.stage_last, members.stage_first, members.e_color, members.h_color, members.age_L, members.age_H, members.weight, members.height_ft, members.height_in, members.SAG, members.AFTRA, members.AEA, members.union_other, members.phone1, members.ph1_type, members.phone2, members.ph2_type, members.email1, members.website, members.thumb_loc, members.pic_loc, agents.agent_name, agents.agent_phone, managers.manager_name, managers.manager_phone, members.category FROM managers INNER JOIN (agents INNER JOIN members ON agents.agent_ID = members.agent_ID) ON managers.manager_ID = members.manager_ID WHERE (members.age_L >= '%s' AND members.age_H <= '%s') AND (members.weight BETWEEN '%s' and '%s') AND (members.height_ft BETWEEN '%s' and '%s') AND (members.height_in BETWEEN '%s' and '%s') AND (members.stage_last LIKE '%s%%') AND (members.category LIKE '%s')", $ageL_rs_members,$ageH_rs_members,$wL_rs_members,$wH_rs_members,$ftL_rs_members,$ftH_rs_members,$inL_rs_members,$inH_rs_members,$stageL_rs_members,$category1_rs_members);
$query_limit_rs_members = sprintf("%s LIMIT %d, %d", $query_rs_members, $startRow_rs_members, $maxRows_rs_members);
$rs_members = mysql_query($query_limit_rs_members, $connHP4_php) or die(mysql_error());
$row_rs_members = mysql_fetch_assoc($rs_members);
//assume checkboxes are choice1....choice6
//put in an array that maps checkboxes to values to search for
$condition_found = true;
$values["sag1"] = array("column"=>"members.SAG",
"search_value"=>"SAG");
$values["sagEL"] = array("column"=>"members.SAG",
"search_value"=>"SAG Eligible");
$values["aftra1"] = array("column"=>"members.AFTRA",
"search_value"=>"AFTRA");
$values["aftraEL"] = array("column"=>"members.AFTRA",
"search_value"=>"AFTRA Eligible");
$values["aea1"] = array("column"=>"members.AEA",
"search_value"=>"AEA");
$values["aeaEL"] = array("column"=>"members.AEA",
"search_value"=>"AEA Eligible");
$and_clause = " AND ";
foreach($values as $choice=>$properties)
{
if(isset($_GET[$choice]))
{
$and_clause .= "($properties[column] = '$properties[search_value]') OR ";
$condition_found = true;
}
}
$and_clause = rtrim($and_clause," OR ");
//only add "and" clause if there is something to sort on,
//otherwise, no "and" clause = return everything.
if($condition_found)
{
$and_clause = rtrim($and_clause," AND ");
$query_rs_members .= $and_clause;
}
echo $query_rs_members;
The echo seems to show that the SQL is correct no matter what combo of checkboxes I choose. For example, after choosing the checkboxes 'sag1' and 'aeaEL', the echo shows the following SQL statement:
Code: Select all
SELECT members.memno, members.stage_last, members.stage_first, members.e_color, members.h_color, members.age_L, members.age_H, members.weight, members.height_ft, members.height_in, members.SAG, members.AFTRA, members.AEA, members.union_other, members.phone1, members.ph1_type, members.phone2, members.ph2_type, members.email1, members.website, members.thumb_loc, members.pic_loc, agents.agent_name, agents.agent_phone, managers.manager_name, managers.manager_phone, members.category FROM managers INNER JOIN (agents INNER JOIN members ON agents.agent_ID = members.agent_ID) ON managers.manager_ID = members.manager_ID WHERE (members.age_L >= '0' AND members.age_H <= '100') AND (members.weight BETWEEN '0' and '500') AND (members.height_ft BETWEEN '0' and '7') AND (members.height_in BETWEEN '0' and '12') AND (members.stage_last LIKE '%') AND (members.category LIKE '%') AND (members.SAG = 'SAG') OR (members.AEA = 'AEA Eligible')
But, it does NOT seem to be filtering the actual records in the database. I tested the above SQL on a copy of the page that didn't have any of the new code, and sure enough, it filtered the records as expected. Any suggestions??