Better way of doing this select

[Anon]

Hello everybody, and let me thank iyou for the help up front 🙂 Thank you...
I was wondering if there way a better way of doing this. Here is my code
if ($state == "any" and $age1 == "any" and $country == "any")
{
$query3 = "select from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" limit $offset,$limit";
}
if ($state == "any" and $age1 == "any")
{
$query3 = "select from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" and UserInfo.Country = \"$country\" limit $offset,$limit";
}
if ($state == "any")
{
$query3 = "select from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" and UserInfo.Country = \"$country\" AND UserInfo.Age =< \"$age1\" and UserInfo.Age => \"$age2\" limit $offset,$limit";
}
if ($state == "any" and $country == "any")
{
$query3 = "select from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" AND UserInfo.Age =< \"$age1\" and UserInfo.Age => \"$age2\" limit $offset,$limit";
}

										if ($age == "any" and $country == "any")
						{
							$query3 =  "select * from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" AND UserInfo.State = \"$state\" limit $offset,$limit";
						}
										if ($country == "any")
						{
							$query3 =  "select * from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" AND UserInfo.State = \"$state\"AND UserInfo.Age =< \"$age1\" and UserInfo.Age => \"$age2\" limit $offset,$limit";
						}

						if ($age == "any")
						{
							$query3 =  "select * from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = \"$status2\" AND Login.Psex = \"$status1\" AND UserInfo.State = \"$state\" and UserInfo.Country = \"$country\" limit $offset,$limit";
						}

All it is, is the query for the select statement.
But i have posted like 4 times trying to get a response to my question.. So maybe i am asking it wrong.. 🙂
IS there a better way of edoing that above?>
Thanks
Chris Calzaretta

[leatherback]

Hi Chris,

UNLESS I am completely confused on what you are trying to doo.. Check my last posting to your previous message, and see code below .. I think it should work for you too..

$AllSub = substr_count(strtolower($Sub), "all"); 

// Check wether the input was all (In my case actually 'use all')
$AllCountry = substr_count(strtolower($Count), "all");
$AllTheme = substr_count(strtolower($Them), "all");

If ($AllSub==1) {
$SubQ = "";
} else {
$SubQ = " and subject.id = '$Sub' " ;
}

If ($AllTheme==1) {
$ThemQ = "";
} else {
$ThemQ = " and theme.id = '$ThemQ' " ;
}

If ($AllCountry==1) {
$CountQ = "";
} else {
$CountQ = " and images.Country = '$Count' " ;
}

$Input_query = " where images.Id = imgtotheme.ImgNr"
			." $SubQ "
			." $ThemQ "
			." $CountQ "
			." AND images.Id = imgtosubject.ImgNr"
	 		." AND theme.Id = imgtotheme.ThemeNr"
	 		." AND subject.Id = imgtosubject.SubjectNr";

[Anon]

All you need is to add a few "OR" clauses.

For example, the first "if" does not consider UserInfo.Country when $country is "all", and the second matches when $country is anything else. So combine them like this:

and (UserInfo.Country = $country OR $country = 'all')

Now do the same thing for all the other combinations...

[fitzbean]

if ($age <> "any") { $sqlage = "AND UserInfo.Age =< '$age1' AND UserInfo.Age => '$age2'";
if ($country <> "any") { $sqlcountry = "AND UserInfo.Country = '$country'";
if ($state <> "any") { $sqlstate = "AND UserInfo.State = '$state'";

$sql = "select * from Login, UserInfo where Login.Ref = UserInfo.Ref AND Login.Sex = '$status2' AND Login.Psex = '$status1' $sqlage $sqlcountry $sqlstate limit $offset,$limit";

There may be some errors in this code, kinda whipped it up fast, but you get the idea.

[Anon]

All is not a value in mysql database

if you go like this
$junk == "";
$query ="select * from table where junk = \"$junk\" ";

you will not get anything from that query,...
Because junk is equaled to nothing..
I need all/everything thing
So all states.. The only way i know how to do this is to not include the field in the select.. so if i was searching on age and the user did not care about the age then age whould half to equal all age's

[fitzbean]

Look at my reply to the problem. I think it's what you are looking for.

[leatherback]

Hi chris,

Look at the replies by Fitz and me: they should both work (Or at least the general idea should work for you). you set a variable for a part of the query. Depending on using 'all' values (The variable is empty) or a specific value (The variable holds the query to search for that value).

J.

[Anon]

If the value coming in from the form in $country is "all", your query will look like this:

and (UserInfo.Country = 'all' or 'all = 'all')

It seems silly, but it gets rid of all the "if" statements in your original attempt and the other suggestions.