Advance search not working with gender check

[The_FedEx_Guy]

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.

[The_FedEx_Guy]

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.

[bregent]

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.

[The_FedEx_Guy]

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

[bregent]

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?

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

[The_FedEx_Guy]

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.

[bregent]

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

[The_FedEx_Guy]

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

[bregent · Accepted solution]

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

[The_FedEx_Guy]

Hi,

Thank you for clearing this up for me. I'm really sorry it took more than one post to get what I needed.