Skip navigation
Currently Being Moderated

Advance search not working with gender check

Apr 1, 2012 6:44 AM

Hi all,

I am not sure if this is something you can help me with or if I am missing something totally obvious.

 

I have a "advanced search form" which has a few options to check through such as age, city, nationality etc and I have two recordsets, one for the logged in user who's gender is stored in $SameGenderCheck

And I have an SQL statement for the search form's search criteria.

 

Below is the SQL statement:

$query_search_res = "SELECT * FROM profiles LEFT JOIN profile_details ON profiles.profile_id = profile_details.profile_id WHERE (city = '$location') OR (ethnicity = '$ethnicity' OR nationality = '$nationality' OR marital_history = '$marital_history' OR height = '$height') AND (Age BETWEEN '$age_from' AND '$age_to') AND (gender != '$SameGenderCheck') AND (approved = 'Yes')";

 

Thanks.

 
Replies
  • Currently Being Moderated
    Apr 2, 2012 9:47 AM   in reply to The_FedEx_Guy

    You are not grouping things in parenthesis correctly in your WHERE clause. You should always use parenthesis to group AND with OR operators. But we need more details about how you want your conditions to work. Please provide example data base data, example inputs, and expected outputs.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 2, 2012 2:55 PM   in reply to The_FedEx_Guy

    OK, I guess I didn't explain this well. I don't need to see the code,  I need to know what your expected results will be for a given search criteria. You have both AND and OR keywords in your WHERE clause. So you need to group these properly to get your intended results. For example, one criteria is Location. Do you want all results from that location, regardless of the other conditions. Or just results from that location that also satisfy the other conditions?

     

    >I have two tables "profile" and "profile_details" (there is a left join here)

     

    Do you need a LEFT join here. Does every profile row have exactly 1 related profile_details row?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 2, 2012 4:11 PM   in reply to bregent

    >Currently it is loading everyone that is Male and the city is the selected city.

     

    Are you are saying here is that it is returning all rows with a matching city, regardless of other criteria? That's would be expected, as you are using and OR keyword. If you want to only return rows for the city where the other conditions are also true, you would use AND.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 3, 2012 12:04 AM   in reply to The_FedEx_Guy

    >The results I need back should be ALL records based on

    >the selected but Gender should always be checked against

     

    Sorry, that answer is too vague to be useful - I can interpret that in many different ways. See if you can provide a little more detail. I'm sure you know what you want, but you are not conveying it to me. For example, you say that Gender should always be checked against...what does that mean? How is that different from the other conditions, like 'approved' or 'age' ?

     

    >I need the join because the rest of the

    >personal details are in the related table.

     

    Yes, of course you need a join, but why a LEFT join? Outer joins are expensive and should only be used when necessary. And why are you putting the details in a separate table? From what I see, the attribute in both the profile and profile_detail table can be in the same table.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 3, 2012 11:59 AM   in reply to The_FedEx_Guy

    >the other search options are optional so if they are entered they will further narrow

    >down the search results if they are not entered then just use age and city.

     

    OK, I believe this is actually the part that is causing problems for you. You want all of the criteria to NARROW your results.  You want ethnicity, nationality, marital_history and height to be optional criteria, so you've combined them with the rest of the statement with an OR. But an OR condition will always widen the results, not narrow. So you must change the first OR to an AND. The OR's used inside the parens for those criterial are OK.

     

    $query_search_res = "SELECT * FROM profiles LEFT JOIN profile_details ON profiles.profile_id = profile_details.profile_id WHERE (city = '$location') AND (ethnicity = '$ethnicity' OR nationality = '$nationality' OR marital_history = '$marital_history' OR height = '$height') AND (Age BETWEEN '$age_from' AND '$age_to') AND (gender != '$SameGenderCheck') AND (approved = 'Yes')";

     

     

    But now comes the problem; if the user has no entries for the ethnicity, nationality, marital_history and height criteria, no results will be returned. Plain SQL will not solve your problem. You can do a workaround by changing the equality operator to the LIKE predicate and putting a wildcard for users that don't have this criteria in their profile. But that only works for text fields, not numeric, and I don't like it.

    A better solution is to dynamically create the WHERE clause, removing the ethnicity, nationality, marital_history and height criteria from the WHERE clause if the user doesn't have these fields completed.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points