$_POST['sex']='female';
$_POST['eye_colour']='blue';
$_POST['ethic_origin']='caucasian/european';
$_POST['hair_colour']='all';
$_POST['age_start']='1999-01-01';
$_POST['age_finish']='2006-01-13';


The above is being selected and send to mysql via php. The database is for the dynamic generation of photos of models. I do not think i have constructed it in the best way, well it is not working period. All the searches seem to work fine but a search using the above does not return all the models that meet the criteria. Can anyone help with the construction of a working query.



if ($_POST['sex'] != 'all') {
$unique = ' WHERE sex = "'.$_POST['sex'].'"';



}elseif($_POST['sex'] == 'all') {

$unique = ' where (sex = "male" OR sex = "female")';
}


$sql .= $unique;


if ($_POST['eye_colour'] != 'all') {
$unique = ' AND eye_colour = "'.$_POST['eye_colour'].'"';



}elseif($_POST['eye_colour'] == 'all') {

$unique = ' AND( eye_colour = "'.$_POST['blue'].'" OR eye_colour = "'.$_POST['green'].'" OR eye_colour = "'.$_POST['hazel'].'" OR eye_colour = "'.$_POST['brown'].'" )';
}

$sql .= $unique;

if ($_POST['hair_colour'] != 'all') {
$unique = ' AND hair_colour = "'.$_POST['hair_colour'].'"';



}elseif($_POST['hair_colour'] == 'all') {

$unique = ' AND( hair_colour = "black" OR hair_colour = "blond"
OR hair_colour = "brown" OR hair_colour = "red" OR hair_colour = "ginger")';
}

$sql .= $unique;


if ($_POST['ethnic_origin'] == 'caucasian/european') {
$unique = ' AND ethnic_origin = 'caucasian/european';

}elseif($_POST['ethnic_origin'] == 'non caucasian/european') {

$unique = ' AND (ethnic_origin = 'asian' OR ethnic_origin = 'oriental'
OR ethnic_origin = 'black' OR ethnic_origin = 'mixed race/other')';


}elseif($_POST['ethnic_origin'] == 'all') {

$unique = ' AND (ethnic_origin = 'asian' OR ethnic_origin = 'oriental' or ethnic_origin='caucasian/european' OR ethnic_origin = 'black'
OR ethnic_origin = 'mixed race/other')';
}

$sql .= $unique;

$unique = ' AND dob BETWEEN "'.$_POST['age_start'].'" AND "'.$_POST['age_finish'].'"';


$sql .= $unique;

echo your final query instead of executing it

copy/paste it here

run it outside of php and let's see if you get any mysql error messages

Hi Spectre, and welcome to the forum....
I'd agree whole heartedly with Rudy, that its easier to debug a SQL statement by seeing the whole statement rather than a fragment. So I'd suggest you reproduce the SQL here.
You mention it doesn't return all rows you expect. How do you know this is the case?, what are you expeccting as opposed to what did you get. Can you identify which terms in your SQL are not returnign the correct number of rows. Offhand I'd expect the problem is going to be your age / date of birth clause, and the reason is down to how you are phrasing your criteria and how you convert that specification into soemthing that can be matched against a date of birth.

Can I make few style comments

you seem to be including uneccesary ifs in your code
for example
could be expressed as
however in reality if a users hasn't made a selection on that criteria why include it in the first place
The reason behind the removal is that it makes your SQL simplier for both the SQL engine and mere mortals to read. It makes it easier for others to help. There is another knock on advantage in that you are using numeric values for such lookups and that means removing the potetnailly confusing depech marks which seem to cause so much confusion.

I think you may be creating problems for yourself in the future, if not now by using text for your categories, not only are you hardcoding your categories, so a change in category requires a change to the application, rather than just the data. There is also a potential risk that your applicaiton may fail due to mispellings. Generally its better to store such attributes indirectly as a number, referencing another table. Rudy's (R937) site has a useful tome on the basics of database design

HTH