Building a query

[Deathwillow]

I'm going to have trouble explaining the problem here but bare with me!

 

So I'm trying to build a search query that search the database for information the user has entered.

 

Basically the setup is:

 

Editbox

Editbox

Dropdown

Dropdown

Dropdown

Dropdown

 

 

So the first thing I did was check to see if all those were blank to set the string as :

 

$query = "SELECT * FROM wow_character";

 

if even one of those form elements aren't blank, set $query as:

 

$query = "SELECT * FROM wow_character WHERE ";

 

Now the problem I'm having and can't figure out is how I would code it to where if say the first editbox is empty but the second isn't it'll return the right information.  The only way I can think of is using a TON of if else statements and that just doesn't seem efficient and I think there's a better cleaner and shorter way of doing it.

 

Here's what I've done so far:

 

// Build Query
if ($name == '' && $level == '' && $race == '' && $gender == '' && $type == '' && $rank == '') {
$query = "SELECT * FROM wow_character";
} else {
$query = "SELECT * FROM wow_character WHERE ";
}

if ($name != '') {
$query .= " name = '$name'";
}

if ($level != '') {
$query .= " AND level = '$level'";
}

 

 

Now that's just testing the first 2 edit boxes, but the errors arise when:

 

1)  if name isn't empty but level is, works like a charm

2)  if name isn't empty and level isn't empty, works like a charm

3)  if name is empty and level is empty, works like a charm

4)  if name is empty and level isn't empty, ERROR

 

 

Is there a shorter, better, more efficient way of coding this without doing:

 

 

// Build Query
if ($name == '' && $level == '' && $race == '' && $gender == '' && $type == '' && $rank == '') {
$query = "SELECT * FROM wow_character";
} else {
$query = "SELECT * FROM wow_character WHERE ";
}

if ($name != '') {
$query .= " name = '$name'";
}

if ($level != '') {
if ($name == '') {
	$query .= " level = '$level'";
} else {
	$query .= " AND name = '$name'";
}
}

 

 

 

 

You can see where it's lengthy with just 2 with 6 it'll be a ton of if else statements... a ton... What's a better way of coding this?

[Deathwillow]

May have babbled a little bit but I couldn't put into words what I was trying to do   Just trying to give you guys a precise picture of the issue.

[fenway]

Don't build the query, build the clauses -- push onto an array, join at the end.

[Deathwillow]

Alright, I may not have done it efficiently but I created a loop that builds an array and then at the end combines it into a query and it all seems to be working.  Thanks for your help!

[Deathwillow]

// Build Query array.

if ($name == '' && $level == '' && $race == '' && $gender == '' && $type == '' && $rank == '') {
$query = "SELECT * FROM wow_character";
$counter = 0;
} else {
$counter = 0;
if ($name != '') {
	$dataName[$counter] = "name";
	$dataQuery[$counter] = $name;
	$counter++;
}
if ($level != '') {
	$dataName[$counter] = "level";
	$dataQuery[$counter] = $level;
	$counter++;
}
if ($race != '') {
	$dataName[$counter] = "raceid";
	$dataQuery[$counter] = $race;
	$counter++;
}
if ($gender != '') {
	$gender = $gender - 1;
	$dataName[$counter] = 'genderid';
	$dataQuery[$counter] = $gender;
	$counter++;
}
if ($type != '') {
	$dataName[$counter] = 'classid';
	$dataQuery[$counter] = $type;
	$counter++;
}
if ($rank != '') {
	$rank = $rank - 1;
	$dataName[$counter] = 'rank';
	$dataQuery[$counter] = $rank;
	$counter++;
}
$x = 0;
while ($x < $counter) {
	if ($x == 0) {
		$query = "SELECT * FROM wow_character WHERE $dataName[$x] = $dataQuery[$x]";
		$x++;
	} else {
		$query .= " AND $dataName[$x] = $dataQuery[$x]";
		$x++;
	}
}
}

 

This is how I did it, is there a more efficient way of doing it?