[Resolved] MySQL Query Where variable=any value?

[Tethys]

Is it possible to write a query that would basically look like this.

$query = SELECT * from $table WHERE (var1='blah' AND var2=anything)

The query is activated through a form. If the user doesn't select a value for one of the variables then I'd like the query to default to select everything.

Example.

User selects Human (race) and Warrior (class)
the query selects every row with race=Human and class=Warrior. No problem.

But when User selects All Races and Warrior the query spits an error.

I tried var=* but that gives an error also.

If I have to, I'll make a bunch of if statements, but I'd rather not add more if I don't have to.

Thanks!

[LordShryku]

try WHERE var = '%';

I would try to leave that out though by building you SQL dynamically. I usually just use one of my NOT NULL fields as a bassis, like:

if($_POST['race']) {
    $rQuery = " AND race = '".$_POST['race']."'";
}
if($_POST['class']) {
    $cQuery = " AND class = '".$_POST['class']."'";
}
$query = "SELECT * FROM table WHERE this IS NOT NULL".$rQuery.$cQuery;

[Tethys]

WHERE var='%' does not work.

No error, but no results either.

I very much like the sound of that dynamic thing, so I'll give that a try. Even though you lost me with about half of those functions/variables/things. 🙂

[LordShryku]

I'm horrible at commenting my code. Just be glad you don;t work with me 🙂

if($_POST['race']) { // If race was selected 
    $rQuery = " AND race = '".$_POST['race']."'"; //Make a variable to add to the query
} 
if($_POST['class']) { // if class was selected
    $cQuery = " AND class = '".$_POST['class']."'"; //Make a variable to add to the query
} 
$query = "SELECT * FROM table WHERE this IS NOT NULL".$rQuery.$cQuery; //the query

//Then we just execute the query
mysql_query($query);

[Tethys]

y'know it helps when I pay attention and put in the right variable names and stuff. 😉

But yay! it works!

I did fiddle with it a bit cuz I'm not sure about the whole IS NOT NULL thing. I don't really understand the whole NULL/NOT NULL thing as pertains to databases.

So here's what I ended up with:

if($_GET['select_race']) { 
    $rQuery = "race = '".$_GET['select_race']."'"; 
} 
if($_GET['select_class']) { 
		if ($rQuery){
		$cQuery = " AND class='".$_GET['select_class']."'";
		} else {
    $cQuery = "class = '".$_GET['select_class']."'"; 
		}
} 
$query = "SELECT * FROM $table_name WHERE (".$rQuery.$cQuery.") ORDER BY first_name";

I'll change the GET back to POST when it goes live, but I wanted to see what was being passed along during testing.

If I made some part of it needlessly complicated then I apologize. I'm afraid I know just enough to be dangerous with this stuff.

Thanks very much LordShryku!

[LordShryku]

Ah, the true essence of any code though.....it's nice if it's pretty, but it's better if it works 🙂

Glad I could help bud

[superwormy]

He meant:

fieldname LIKE '%'

And NOT:

fieldname = '%'+

% is a MySQL wildcard character, it means ' any character '

You can do things like this too:
WHERE fieldname LIKE '%keith%'

Which would match all of the following:

Keith Palmer
Mr. Keith Palmer
Keith

While:
WHERE fieldname LIKE 'keith%'

Would only match:

Keith Palmer
Keith

If ya get what I'm saying...

[LordShryku]

/me slaps himself

Thanks superwormy