I do not know if the code following is the best way to write the query. I think most of the query works apart from the bit that searches for ethnic origin. I am trying to get the database to search for caucasian/european (which is composed caucasian/european)or non caucasian/european(which is composed of black, oriental,asian).At the top of the code is a sample post from a form.I am trying to get the database to search for example eye_colour for a specified eye_colour(blue,green,brown,hazel) or all of them,which ever the user selects. Can anyone help rewrite this ?
<?php
$POST['sex']='male';
$POST['eye_colour']='all';
$POST['ethnic_origin']='all';
$POST['hair_colour']='all';
$POST['age_start']='1987-01-01';
$POST['age_finish']='2006-01-01';
$_POST['talent']='none';
// include the Database classes
require_once('database_mysql.php5');
// escape quotes and apostrophes if magic_quotes_gpc off
if (!get_magic_quotes_gpc()) {
foreach($POST as $key=>$value) {
$temp = addslashes($value);
$POST[$key] = $temp;
}
}
$sql = 'select * from kidzdata';
if ($POST['sex'] != 'all') {
$unique = ' WHERE sex = "'.$POST['sex'].'"';
}elseif($_POST['sex'] == 'all') {
$unique = ' where sex LIKE '%'';
}
$sql .= $unique;
if ($POST['eye_colour'] != 'all') {
$unique = ' AND eye_colour = "'.$POST['eye_colour'].'"';
}elseif($_POST['eye_colour'] == 'all') {
$unique = ' AND eye_colour LIKE '%'';
}
$sql .= $unique;
if ($POST['hair_colour'] != 'all') {
$unique = ' AND hair_colour = "'.$POST['hair_colour'].'"';
}elseif($_POST['hair_colour'] == 'all') {
$unique = ' AND hair_colour LIKE '%'';
}
$sql .= $unique;
if ($_POST['ethnic_origin'] != 'caucasian/european') {
$unique = ' AND ethnic_origin NOT LIKE 'cau%'';
}elseif($_POST['ethnic_origin'] == 'caucasian/european') {
$unique = ' AND ethnic_origin LIKE "cau%"';
}elseif($_POST['ethnic_origin'] == 'all') {
$unique = ' AND ethnic_origin LIKE '%'';
}
$sql .= $unique;
if ($_POST['talent'] != 'none') {
$unique = ' AND ( talenta = "'.$POST['talent'].'" OR talentb = "'.$POST['talent'].'"
OR talentc = "'.$POST['talent'].'" OR talentd = "'.$POST['talent'].'"
OR talente = "'.$POST['talent'].'" OR talentf = "'.$POST['talent'].'"
OR talentg = "'.$POST['talent'].'" OR talenth = "'.$POST['talent'].'"
OR talenti = "'.$POST['talent'].'" OR talentj = "'.$POST['talent'].'"
OR talentk = "'.$POST['talent'].'" OR talentl = "'.$POST['talent'].'"
OR talentm = "'.$POST['talent'].'" OR talentn = "'.$POST['talent'].'"
OR talento = "'.$_POST['talent'].'")';
}
$sql .= $unique;
echo getDetails($sql);
function getDetails($sql) {
$db = new Database('xxxxx','xxxx','xxx','xxxxxx');
$result = $db->query($sql);
$numrows =$result->num_rows;
if ($numrows <1) {
$errormessage = 'There is no match for your search at this point in time';
echo 'database return=n&message='.urlencode($errormessage);
}
$details ="total=$numrows";
$counter = 0;
while ($row = $result->fetch_assoc()) {
$details .= '&first_name'.$counter.'='.($row['first_name']);
$details .= '&family_name'.$counter.'='.($row['family_name']);
$details .= '&dob'.$counter.'='.urlencode($row['dob']);
$details .= '&age'.$counter.'='.urlencode($row['age']);
$details .= '&hair_colour'.$counter.'='.urlencode($row['hair_colour']);
$details .= '&eye_colour'.$counter.'='.urlencode($row['eye_colour']);
$details .= 'ðnic_origin'.$counter.'='.urlencode($row['ethnic_origin']);
$details .= '&i_want_to_be'.$counter.'='.urlencode($row['i_want_to_be']);
$details .= '&favourite_food'.$counter.'='.urlencode($row['favourite_food']);
$details .= '&talenta'.$counter.'='.urlencode($row['talenta']);
$details .= '&talentb'.$counter.'='.urlencode($row['talentb']);
$details .= '&talentc'.$counter.'='.urlencode($row['talentc']);
$details .= '&talentd'.$counter.'='.urlencode($row['talentd']);
$details .= '&talente'.$counter.'='.urlencode($row['talente']);
$details .= '&talentf'.$counter.'='.urlencode($row['talentf']);
$details .= '&talentg'.$counter.'='.urlencode($row['talentg']);
$details .= '&talenth'.$counter.'='.urlencode($row['talenth']);
$details .= '&talenti'.$counter.'='.urlencode($row['talenti']);
$details .= '&talentj'.$counter.'='.urlencode($row['talentj']);
$details .= '&talentk'.$counter.'='.urlencode($row['talentk']);
$details .= '&talentl'.$counter.'='.urlencode($row['talentl']);
$details .= '&talentm'.$counter.'='.urlencode($row['talentm']);
$details .= '&talentn'.$counter.'='.urlencode($row['talentn']);
$details .= '&talento'.$counter.'='.urlencode($row['talento']);
$counter++;
}
echo $details;
$db->close();
}
?>
This is the line that the debugger is pointing to, but i do not know how to rewrite it?
$unique = ' AND ethnic_origin NOT LIKE 'cau%'';
