[SOLVED] Help with multiple conditions query

[tekrscom]

I'm sorry for posting about the same issue I had before, but I honestly thought it was working, but it in fact is not...

 

PHP version 5.2.5

MySQL version 5.0.81-community-log

 

Ok, so basically what I'm trying to accomplish here is to exclude result rows that do not have either Users.Yahoo or Users.MSN field ... I only want result rows where at least one of those have a value... Here's my code...

 

// Prior code to give example of what $query, $TempGender and $TempPrivacySetting variables are...

$query="";
if ($_SESSION['SearchParameter'][0]==1){$query=$query."Roleplay=1, ";}
if ($_SESSION['SearchParameter'][4]==1){$query=$query."Old=1, ";}
if ($_SESSION['SearchParameter'][5]==1){$query=$query."Fat=1, ";}
if ($_SESSION['SearchParameter'][8]==1){$query=$query."ImageExchange=1, ";}
if ($_SESSION['SearchParameter'][11]==1){$query=$query."Bukkake=1, ";}
if ($_SESSION['SearchParameter'][12]==1){$query=$query."Bondage=1, ";}
if ($_SESSION['SearchParameter'][22]==1){$query=$query."Gay=1, ";}
if ($_SESSION['SearchParameter'][23]==1){$query=$query."Bisexual=1, ";}
if ($_SESSION['SearchParameter'][24]==0){$TempGender='Either';}
if ($_SESSION['SearchParameter'][24]==1){$TempGender='Male';}
if ($_SESSION['SearchParameter'][24]==2){$TempGender='Female';}
if ($_SESSION['SearchParameter'][25]==0){$TempPrivacySetting="";}
if ($_SESSION['SearchParameter'][25]==1){$TempPrivacySetting=" AND Users.PrivacySetting = 'Public'";}
$query = substr_replace($query," ",-2);

// Actual query 

$sub_query = "SELECT Interests.*, Users.* ".
"FROM Interests RIGHT JOIN Users ".
"ON Interests.UserID = Users.UserID WHERE Users.Gender = '$TempGender' $TempPrivacySetting AND NOT (Users.Yahoo!='' AND Users.MSN!='') AND $query ORDER BY LogOnDate DESC";

 

I was also thinking about adding Users.UserID != '{$_SESSION['UserID']}' to prevent the person searching from getting their own self as a result... but wasn't sure just how many conditions I could add to a single query and it still work correctly...

 

Am I going about this query all wrong... Is there a better way to format it?

[kickstart]

Hi

 

The way you build up $query doesn't appear correct if there is more than 1 option selected:

 

$query="";

if ($_SESSION['SearchParameter'][0]==1){$query=$query."Roleplay=1 OR ";}

if ($_SESSION['SearchParameter'][4]==1){$query=$query."Old=1 OR ";}

if ($_SESSION['SearchParameter'][5]==1){$query=$query."Fat=1 OR ";}

if ($_SESSION['SearchParameter'][8]==1){$query=$query."ImageExchange=1 OR ";}

if ($_SESSION['SearchParameter'][11]==1){$query=$query."Bukkake=1 OR ";}

if ($_SESSION['SearchParameter'][12]==1){$query=$query."Bondage=1 OR ";}

if ($_SESSION['SearchParameter'][22]==1){$query=$query."Gay=1 OR ";}

if ($_SESSION['SearchParameter'][23]==1){$query=$query."Bisexual=1 OR ";}

if ($_SESSION['SearchParameter'][24]==0){$TempGender='Either';}

if ($_SESSION['SearchParameter'][24]==1){$TempGender='Male';}

if ($_SESSION['SearchParameter'][24]==2){$TempGender='Female';}

if ($_SESSION['SearchParameter'][25]==0){$TempPrivacySetting="";}

if ($_SESSION['SearchParameter'][25]==1){$TempPrivacySetting=" AND Users.PrivacySetting = 'Public'";}

$query = substr_replace($query," ",-4);

 

All the best

 

Keith

[tekrscom]

Actually that part of the query works perfectly...

[kickstart]

Hi

 

Well you current check on the Yahoo / MSN fields will return rows where one or the other or both are blank.

 

Something like this should work and is simpler:-

 

$sub_query = "SELECT Interests.*, Users.* ".

"FROM Interests RIGHT JOIN Users ".

"ON Interests.UserID = Users.UserID

WHERE Users.Gender = '$TempGender' $TempPrivacySetting

AND (Users.Yahoo!='' OR Users.MSN!='')

AND $query ORDER BY LogOnDate DESC";

 

All the best

 

Keith

 

[tekrscom]

Yep, that did it, thank you so much for your assistance...

 

You know what... I think you were onto something the first time... I just did a search query and it didn't show certain results...

[tekrscom]

$query="";
if ($_SESSION['SearchParameter'][0]==1){$query=$query."Roleplay=1 OR ";}
if ($_SESSION['SearchParameter'][4]==1){$query=$query."Old=1 OR ";}
if ($_SESSION['SearchParameter'][5]==1){$query=$query."Fat=1 OR ";}
if ($_SESSION['SearchParameter'][8]==1){$query=$query."ImageExchange=1 OR ";}
if ($_SESSION['SearchParameter'][11]==1){$query=$query."Bukkake=1 OR ";}
if ($_SESSION['SearchParameter'][12]==1){$query=$query."Bondage=1 OR ";}
if ($_SESSION['SearchParameter'][22]==1){$query=$query."Gay=1 OR ";}
if ($_SESSION['SearchParameter'][23]==1){$query=$query."Bisexual=1 OR ";}
if ($_SESSION['SearchParameter'][24]==0){$TempGender='Either';}
if ($_SESSION['SearchParameter'][24]==1){$TempGender='Male';}
if ($_SESSION['SearchParameter'][24]==2){$TempGender='Female';}
if ($_SESSION['SearchParameter'][25]==0){$TempPrivacySetting="";}
if ($_SESSION['SearchParameter'][25]==1){$TempPrivacySetting=" AND Users.PrivacySetting = 'Public'";}
$query = substr_replace($query," ",-4);

$sub_query = "SELECT Interests.*, Users.* ".
"FROM Interests RIGHT JOIN Users ".
"ON Interests.UserID = Users.UserID 
WHERE Users.Gender = '$TempGender' $TempPrivacySetting 
AND (Users.Yahoo!='' OR Users.MSN!='') 
AND $query ORDER BY LogOnDate DESC";

 

Egh! It fixed that problem, now it seems to have broken the rest of the query... It isn't listening to any of the other conditions...

[kickstart]

Hi

 

Try this for assigning the $query variable:-

 

$query = (($query == '') ? '' : '('.substr_replace($query," ",-4).')');

 

If you have mulitple options in the $query then you need to surround them with brackets (and if you only have one then there is no real down side to surrounding it with brackets).

 

All the best

 

Keith

[tekrscom]

Very nice... you saved my behind... I was getting very stressed... LOL... You're a genius!!!