Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Unanswered: search does not return all rows that meet the criteria (was "newbie in need of help")

    $_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;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    Code:
    if ($_POST['sex'] != 'all')
    { $unique = ' WHERE sex = "'.$_POST['sex'].'"';
    } elseif($_POST['sex'] == 'all')
    { $unique = ' where (sex = "male" OR sex = "female")';
    }
    could be expressed as
    Code:
    if ($_POST['sex'] != 'all')
    { $unique = ' WHERE sex = "'.$_POST['sex'].'"';
    } else
    { $unique = ' where (sex = "male" OR sex = "female")';
    }
    however in reality if a users hasn't made a selection on that criteria why include it in the first place
    Code:
    if ($_POST['sex'] != 'all')
    { $unique = ' WHERE sex = "'.$_POST['sex'].'"';
    }
    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
    Last edited by healdem; 02-16-06 at 04:59.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •