The following query works.

"SELECT * FROM jobs WHERE reviewed = 'Yes' AND code = 1"

Now I want to change the conditions to allow a user to specify the
criteria for a query using TWO drop down selection boxes.
So I guess I can replace some of the code with PHP variables like this.

"SELECT * FROM jobs WHERE reviewed = $status AND code = $number"

So far so good.
The tricky part for me is this.
I want to allow "select all" as a menu option in both drop down boxes.

In that case what would the variables need to be set to?
I`ve tried "%" and changing "equals" to "LIKE" but that doesnt work because I dont think "%" wildcard works in isolation.

Is there another MySQL variable that acts as a wildcard?

Of course its possible to re-write the query without the WHERE conditional but that seems grossly inefficient and in any case the number of queries required to handle all variations is going to grow exponentially. There has to be a better way.

Ive also considered using "WHERE IN (|array goes here|)"

And use some PHP script to dynamically update the array contents.
Maybe this is the way to go?

Originally Posted by TWD

Ive also considered using "WHERE IN (|array goes here|)"

And use some PHP script to dynamically update the array contents.
Maybe this is the way to go?

Compared to a second version of the query with no where predicate, this one is massively inefficient. You'll need two queries, the first to fetch a list of all values to build into the second query to go back and find rows with those exact same values.

How about

Where Coalesce(?, Column) = Column

Originally Posted by Learning Newbie

Compared to a second version of the query with no where predicate, this one is massively inefficient.

Well in that case it means I would actually need FOUR separate queries to handle with/without 2 variables.

example.

SELECT * FROM table

SELECT * FROM table WHERE columnA = $variableA

SELECT * FROM table WHERE columnB = $variableB

SELECT * FROM table WHERE columnA = $variableA AND columnB = $variableB

Thats 4 lines of query and the number will increase exponentially if I add a third, fourth or fifth variable. So there is a bit of a dilemma when a wildcard is required to indicate "all" for any of the variables.

Also as I understand it SELECT COALESCE is used to return the first non-null value from an array of columns. How would I use that in this case?

Appreciate your help. Cheers!

So far so good.
The tricky part for me is this.
I want to allow "select all" as a menu option in both drop down boxes.

Pardon me if I've missed something BUT,

wouldn't a "Select all" option from any selection negate the need for a "WHERE" criteria for that column?
Which is what you may be trying to say in the above post.

If so, it's not a matter of setting up 4 separate queries, more a matter of concatenation and leaving out the bits you don't need.

bit of pseudo code:

query = "SELECT fieldlist FROM table"

if (opt1 == selectAll && opt2 != selectAll) {
    query = query + " WHERE col2 = opt2"
} elseif {
if (opt1 != selectAll && opt2 == selectAll) {
    query = query + " WHERE col1 = opt1"
} elseif {
if (opt1 != selectAll && opt2 != selectAll) 
    query = query + " WHERE col1 = opt1 AND col2 = opt2"
}
    query = query + " ;"

I think that covers all options assuming that the end user HAS to include a selection or there is a default already set.

BTW do not use SELECT * FROM always create a fieldlist, even when do do want all the columns.

Originally Posted by TWD

Also as I understand it SELECT COALESCE is used to return the first non-null value from an array of columns. How would I use that in this case?

Appreciate your help. Cheers!

Pass in a null value for your sproc's input parameter, and then compare a coalesced version of that and the column value, against the column value. This is also inefficient compared to just not including a where predicate, but it will do exactly what you want, which is to limit the query's output when you have a valid thing to limit by, but allow all rows to pass through when you don't.

Originally Posted by chrishirst

Pardon me if I've missed something BUT,

wouldn't a "Select all" option from any selection negate the need for a "WHERE" criteria for that column?
Which is what you may be trying to say in the above post.

If so, it's not a matter of setting up 4 separate queries, more a matter of concatenation and leaving out the bits you don't need.

bit of pseudo code:

query = "SELECT fieldlist FROM table"

if (opt1 == selectAll && opt2 != selectAll) {
    query = query + " WHERE col2 = opt2"
} elseif {
if (opt1 != selectAll && opt2 == selectAll) {
    query = query + " WHERE col1 = opt1"
} elseif {
if (opt1 != selectAll && opt2 != selectAll) 
    query = query + " WHERE col1 = opt1 AND col2 = opt2"
}
    query = query + " ;"

I think that covers all options assuming that the end user HAS to include a selection or there is a default already set.

BTW do not use SELECT * FROM always create a fieldlist, even when do do want all the columns.

Chris

Thanks for the input. Thats a nice piece of code but its still a conditional with 4 branches. If I add a third variable I would need to have 9 branches. A fourth variable, 16 branches and so on. If I have 10 variables it requires 100 branches!

I was hoping for a more elegant solution.

An elegant solution would be to remove the where clause when possible. An even more elegant solution would be to use a stored procedure and delegate the database logic to the database.

When you say extra variable are you meaning a extra select element?

If so, the idea would be to make the server side code work smarter rather than harder to write.

set an array with the keys that are always the same in a standard form eg: everything apart from your filter selection
create your select boxes with the names/ids set exactly as the columns they search in.
initialise your query almost as in the code above but with a difference, the addition of a placeholder
$selquery = "SELECT fieldlist [filterfields] FROM table"
( No don't use SELECT * )

PHP Code:

$FirstFilter = false;  // set a flag 
$FilterFields = "";      //initialise a filter string 
// then you loop through the $_POST collection.

foreach ($_POST as $key) { 
if (add in a check for $key being in the standard keys array) {
 // do nothing & loop around 
 } else {
 $FilterFields = $FilterFields . "," . $key; 
    if ($_POST[$key] == "Select All") {
     // do nothing &  loop around 
        } else {
        if (!$FirstFilter) {
        $selquery . " WHERE $key = $_POST[$key] "
 // note that "$key" will be equal to the "columnname" so the query has now become "SELECT fieldlist [filterfields] FROM table WHERE columnname = value".
        $FirstFilter = true
        } else {
            $selquery . " AND $key = $_POST[$key] "
        }
    }
} 


or something like that in real code

at this point $FilterFields should hold a list of columns that are in the filterable fields and the query should be all the necessary "WHERE"s & "AND"s criteria.
replace the placeholder with the value $FilterFields, close the query off with any ORDER BY etc and you should have a metod that will construct the correct query for any amount of selectors.

What I do with this kind of thing is also add a datatype indicator to the selector name (d_ = date, c_ = char, n_ = numeric) so I can add the appropriate delimiters to the value in the concat loop (remember all POSTed values are strings) and do any "sanitising" if needed.
just regex or "str_replace" these out before concating the key name to the query.

Just do this:

SELECT * from table WHERE 1=1

Then if (op1!=selectAll) query = query + " AND col1=" + opt1

etc

Originally Posted by chrishirst

When you say extra variable are you meaning a extra select element?

If so, the idea would be to make the server side code work smarter rather than harder to write.

set an array with the keys that are always the same in a standard form eg: everything apart from your filter selection
create your select boxes with the names/ids set exactly as the columns they search in.
initialise your query almost as in the code above but with a difference, the addition of a placeholder
$selquery = "SELECT fieldlist [filterfields] FROM table"
( No don't use SELECT * )

PHP Code:

$FirstFilter = false;  // set a flag 
$FilterFields = "";      //initialise a filter string 
// then you loop through the $_POST collection.

foreach ($_POST as $key) { 
if (add in a check for $key being in the standard keys array) {
 // do nothing & loop around 
 } else {
 $FilterFields = $FilterFields . "," . $key; 
    if ($_POST[$key] == "Select All") {
     // do nothing &  loop around 
        } else {
        if (!$FirstFilter) {
        $selquery . " WHERE $key = $_POST[$key] "
 // note that "$key" will be equal to the "columnname" so the query has now become "SELECT fieldlist [filterfields] FROM table WHERE columnname = value".
        $FirstFilter = true
        } else {
            $selquery . " AND $key = $_POST[$key] "
        }
    }
} 


or something like that in real code

at this point $FilterFields should hold a list of columns that are in the filterable fields and the query should be all the necessary "WHERE"s & "AND"s criteria.
replace the placeholder with the value $FilterFields, close the query off with any ORDER BY etc and you should have a metod that will construct the correct query for any amount of selectors.

What I do with this kind of thing is also add a datatype indicator to the selector name (d_ = date, c_ = char, n_ = numeric) so I can add the appropriate delimiters to the value in the concat loop (remember all POSTed values are strings) and do any "sanitising" if needed.
just regex or "str_replace" these out before concating the key name to the query.

OK. Thanks. Thats more like what I had in mind. I can see the basic principle at work.

Can I ask why not select "*"? You mentioned before to use a fieldset even when all fields ARE required. So is this a security or efficiency recommendation? Also, most PHP tutorials I`ve read seem to use SELECT * so I`ve had a hard time finding explanations on how to use fieldsets correctly. Do you have a link to anything that would help?

Much appreciated!

SELECT * forces two passes through the DB tables.
One to retrieve the column names, one to extract the data.
And it brings the data from every column when you may only need one or two
Possibly disastrous on performance for a JOIN query.

A fieldset will only return the columns needed which makes it easier for the DB server query optimiser to work.
To use a fieldset just create a comma separated list of the column names to extract

eg:
SELECT col1,col2,x.col2,x.col3 FROM table JOIN table2 as x ON col2 = x.col1

I see your point in the instance of a JOIN, but in the case of a single table what is the downside of doing a SELECT *? Is it simply the db has to do a select to get the column names first, then select from the table?

Originally Posted by ngolding

I see your point in the instance of a JOIN, but in the case of a single table what is the downside of doing a SELECT *?

If it's a query that will only ever run once, not a very big downside. If it's a query that's going to run in a loop, especially a tight loop, you're throwing away performance. The database server has to bind to the metadata, and send more data down the pipeline.

With some RDBMS implementations, this can also affect the granularity of schema modification locks. In some, you can add or remove a column from that table while the select query is running, if you specified the particuclar columns you want, but a select * prevents any modifications to the table (even adding a new column) until the query finishes. With a VLDB that can be bad. But the metadata binding is the real issue in most cases, in production code.