public static function is_watch_list_eligible($pet_wk, $watch_list_wk) { global $session; global $database; $pet = Pet::find_by_id($pet_wk); $watch_list_detail = Watch_List_Detail::find_by_sql("SELECT * FROM `watch_list_detail` WHERE `watch_list_wk` = " . $watch_list_wk . ";"); //echo '<pre>' . var_export($watch_list_detail, true) . '</pre>'; //debug //generate the dynamic MySQL statement $sql = "SELECT `p`.* FROM `pet` AS `p` "; $sql .= "INNER JOIN `breed` AS `b` ON `b`.`breed_wk` = `p`.`breed_wk` "; $sql .= "INNER JOIN `pet_type` AS `pt` ON `pt`.`pet_type_wk` = `b`.`pet_type_wk` "; $sql .= "WHERE `p`.`is_deleted` = 0 AND `p`.`pet_wk` = " . $pet_wk . " "; //loop through each criteria, determine if it's eligible foreach ($watch_list_detail as $item) { //if one of the min or max fields if (in_array($item->column_name, array('age_min', 'age_max', 'weight_min', 'weight_max'))) { if ($item->column_name == 'age_min') { $sql .= "AND `age` >= " . $item->value . " "; } else { if ($item->column_name == 'age_max') { $sql .= "AND `age` <= " . $item->value . " "; } else { if ($item->column_name == 'weight_min') { $sql .= "AND `weight` >= " . $item->value . " "; } else { if ($item->column_name == 'weight_max') { $sql .= "AND `weight` <= " . $item->value . " "; } } } } } else { //not one of the min or max fields $sql .= "AND " . ($item->column_name == 'pet_type' ? '`pt`' : '`p`') . ".`" . $item->column_name . "_wk` = " . $item->value . " "; } } $sql .= ";"; $result = Pet::find_by_sql($sql); //if the count of animals returned is 1, return true //else, return false if (count($result) == 1) { return true; } else { return false; } }