Skip to main content
T
The_FedEx_Guy
Inspiring
April 1, 2012
Answered

Advance search not working with gender check

  • April 1, 2012
  • 2 replies
  • 1322 views

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.

This topic has been closed for replies.
Correct answer bregent

I'm sorry I am not being clear.  I will try again...

The search results should bring back all profiles with the matching criteria. Only approved, and gender must be as below:

approved = Yes

gender = Male/Female (based on $SameGenderCheck;, males cannot search for males, and females cannot search for females)

age should be a number between 16 and 65 selected via two dropdowns ($age_from and $age_to)

I want the user to select the age and the city as required fields, 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. The gender will always be the opposite of the user. And I only want the user to see the people who have been approved so "approved" has to be set to Yes

There is nothing else.

Thank you in advance


>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.

2 replies

T
The_FedEx_GuyAuthor
Inspiring
April 3, 2012

The results I need back should be ALL records based on the selected but Gender should always be checked against

I need the join because the rest of the personal details are in the related table.

B
bregent
Participating Frequently
April 3, 2012

>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.

T
The_FedEx_GuyAuthor
Inspiring
April 3, 2012

I'm sorry I am not being clear.  I will try again...

The search results should bring back all profiles with the matching criteria. Only approved, and gender must be as below:

approved = Yes

gender = Male/Female (based on $SameGenderCheck;, males cannot search for males, and females cannot search for females)

age should be a number between 16 and 65 selected via two dropdowns ($age_from and $age_to)

I want the user to select the age and the city as required fields, 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. The gender will always be the opposite of the user. And I only want the user to see the people who have been approved so "approved" has to be set to Yes

There is nothing else.

Thank you in advance

T
The_FedEx_GuyAuthor
Inspiring
April 1, 2012

Sorry, I forgot to say that if the logged in user is Male, only Females should come up on the results for this user.

And only if any other option is selected if not it is ignored.

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

B
bregent
Participating Frequently
April 2, 2012

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.

T
The_FedEx_GuyAuthor
Inspiring
April 2, 2012

I see.

I will do my best.

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

The name and contact details are in the table called "profile" and personal details such as height, nationality,marital status etc

All fields are filled in.

"Profile" also stores the gender of the user.

So the idea of the form is to allow the visitor to type/select any and all of the search criteria and adv_search_results.php should display the matched criteria.

The user must not be the same gender as the people in the results. So if user is male the person they are searching for should only be female. And vise versa.

Here are my code snippets.

Logged in visitor:

mysql_select_db($database_db, $db);

$query_loadProfile = "SELECT * FROM profiles LEFT JOIN profile_details ON profiles.profile_id = profile_details.profile_id WHERE email_address ='".$_SESSION['MM_Username']."' AND approved = 'Yes'";

$loadProfile = mysql_query($query_loadProfile, $db) or die(mysql_error());

$row_loadProfile = mysql_fetch_assoc($loadProfile);

$totalRows_loadProfile = mysql_num_rows($loadProfile);

$SameGenderCheck = $row_loadProfile['gender']; <--- THIS TAKES THE CURRENT LOGGED IN USER's GENDER

This is the recordset for the search criteria:

mysql_select_db($database_db, $db);

$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')";

This is the results page output:

<table width="100%" border="0" cellspacing="2" cellpadding="2">

                            <tr>

                              <td valign="top"><div align="left">User ID</div></td>

                              <td valign="top"><div align="left">100<?php echo $row_search_res['profile_id']; ?></div></td>

                            </tr>

                            <tr>

                              <td valign="top"><div align="left">City</div></td>

                              <td valign="top"><div align="left"><?php echo $row_search_res['city']; ?></div></td>

                            </tr>

                          <tr>

                            <td valign="top">Ethnicity</td>

                              <td valign="top"><?php echo $row_search_res['ethnicity']; ?></td>

                            </tr>

                          <tr>

                            <td valign="top">Nationality</td>

                              <td valign="top"><?php echo $row_search_res['nationality']; ?></td>

                            </tr>

                          <tr>

                            <td valign="top"> </td>

                              <td valign="top"><div align="right"><a href="profile.php?id=<?php echo $row_search_res['profile_id']; ?>" target="_blank">View Profile</a></div></td>

                            </tr>

                          </table>

Please note I have not put the whole code of the results page as it loads other non critical things.

I hope this is enough

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices