Select Query Help Please

[Drewser33]

I am stuck with writing a select query that is supposed to be very dynamic.

 

I am trying to run the select query according to what is selected in a form.  I use the GET form to define the variables.  My problem is that I don't know how to define all for a column of the table if the user has not selected a value.

 

Example:

 

In the form if the user chooses to run the query by selecting only who created a ticket, but does not care what the status is.

 

The user would select the name in the select box called "createdby" but leave the status the default "All", they would also leave the rest of the boxes as all as well.

 

I have 9 different select boxes so my query includes all of them.  So I would like to only write one query that takes every condition possible.

 

$query100 = "SELECT * FROM taskinfo WHERE  Type = '$type' AND  Subject = '$subject' AND Priority = '$priority'AND Status = '$status'AND Response = '$employee'AND Response2 = '$employee2'AND Response3 = '$employee3' AND Assignto = '$assignto' AND Createdby = '$createdby'";

 

This is the query that I have written, so in the example above is there a value that I can make for $status that will select all the values in that column.

 

Or will I have to write all the different possible scenarios that will be available and write a separate select query for each??

 

Thanks for all the help

[php_dave]

Im not 100% sure on this but if the value is a char/string you can use '%' and compare with LIKE or if the value is an int you can use a * and use =...

 

Or it may be the otherway round!! -  I have some code that does this somewhere - ill dig it out and post an example

 

 

[cooldude832]

You need to  use logical operators on it to get the query built as you like (connotation is also helpful)

<?php
$q = "Select * from `taskinfo` Where";
if($_GET['status'] == "all") || !is_int($_GET['status'])){
#We will select all users
$q .= " status = '*'";
}
else{
$q .= " status = '".mysql_real_escape_string($_GET['status'])."'";
}
?>

 

Get the idea?

[aschk]

Firstly use POST not GET...

 

How about you just build your query.

e.g.

 

<?php

$query100 = "SELECT * FROM taskinfo ";

foreach($_POST as $val){
  if($val != "ALL"){
    $query100 .= "WHERE ";
    break;
  }
}

$where = array();

if($type != "ALL"){
  $where[] = "Type = '$type' ";
}


if($subject != "ALL"){
  $where[] = "Subject = '$subject' ";
}

etc etc ....

if(!empty($where)){
  $query100 .= implode(" AND ", $where);
}

?>

 

This is a VERY primitive example, and does no filtering (thus allowing potentially harmful content).

[cooldude832]

Firstly use POST not GET...

 

Explain exactly why they shouldn't use GET???

[Drewser33]

aschk, I used your way to build this query.  It works very nicely thank you very much, however, I did stick with the GET.  The only time that I get an error is if I don't fill in a value on any option.  If I read the code correctly it should break if all the values = "ALL"? 

 

Creating a situation where it just selects all $query100 = "SELECT * FROM taskinfo "; ??

 

The error says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"  Which isn't really narrowing down where the error is.

 

Any ideas why i would be getting this error?

 

Thanks for all the help everyone!!

[marcus]

echo your query to see what's missing, the double quotes '' mean you have an empty value in place

[Drewser33]

I believe I have figured it out  the code here:

 

foreach($_GET as $val){

  if($val != "-1"){

    $query100 .= "WHERE ";

    break;

  }

}

 

I am comparing every value that isset GET, which includes the button that I am submitting.  Is there a way to exclude the buttons value from the $_GET = $val??

[craygo]

I would build the query also

<?php
$sql = "SELECT * FROM table WHERE ";
foreach($_GET as $field => $val){
  if($val != "ALL"){
  $sql .= "$field = '$val' AND ";
  }
}
if(substr($sql,-4) == "AND "){
$sql = substr($sql,0,-4);
}
echo $sql;
?>

 

Why bother with the fields that are ALL. It's the same as not putting them in the WHERE clause in the first place.

 

Ray

[Drewser33]

OK,  I may have left out something that may be causing a problem.  I have a date from and date to field that filters records as well, but will never be "ALL".  Also, will it make a difference if my $GET field does not match the column name in the database??

 

Let me try to explain in a bit more detail:

 

The current way that I am running the query would work, except there are many more GET variables than I use in the query at this point, but will need later.  Also, the fields that I am trying to compare are all integers.  And the value I submit for those fields that are displayed as "ALL" is negative one.  Maybe this will help.

 

Thanks again!!

[Drewser33]

I figured it out -- added this to the code that was originally given to me by aschk.

 

As it took me a while to figure out that the WHERE was being but there by additional fields that I accounted for after trying a few more of all of suggestions, and also

 

  if($val != "-1" OR  $val = "Save")

I put the or in there so the button did not interfere.

 

Thanks EVERYONE.