search for all results in mysql

not possible - so how is it done

[whatson]

So if you have a search feature, and drop down boxes are included, but you want to list an option in the drop down box as ALL, displaying all e.g. price ranges, and in your mysql query you have:
SELECT * FROM `products` WHERE `price`=$priceRange AND `locaction`=$location

Then what if you want to choose all? Obviously this will not work. So how is this usually done? Do you have to use switch statements for if isset each variable, and the combination of each, as you must also enter the AND into the query too.
And what if you have 10 or even more search parameters? There are too many combinations.

How is this done? I can't work out the logic.

[LifeinAsia]

You'll need to include some sort of conditional logic in building the SELECT statement. Use the "`price`=$priceRange" unless all is selected. (I don't know PHP so I can't give you the exact way to write it.) Include the same logic for all your other conditionals.

If every conditional can be selected for all, you'll also need an initial "1=1" conditional. Something like the following in pseudo code:
SELECT *
FROM `products`
WHERE 1=1
IF ($priceRange<>'*') AND `price`=$priceRange
IF ($location<>'*') AND `locaction`=$location
...

(NOTE: the psuedocode is used to build the SQL statement in PHP.)

[swa66]

You can extend the logic to detect if there's none, 1 or more options to include and that way avoid that 1=1 - it feels to much like a hacker got to you.

[whatson]

oh awesome I can do an if statement in the mysql, but then I have the issue of the 'AND', what if it becomes WHERE AND `price`=$priceRange?

[vincevincevince]

Either write the statement in PHP and then send it, or have the if:false option as 1:
"WHERE 1 AND `price`=$pricerange"

[swa66]

In pseudo code:

...
$sql = "SELECT field1, field2, ... fieldN FROM tablename";
$counter=0;
if (test to decide you want to output a price restriction) {
  $sql .= "WHERE `price`= $price";
  $counter++;
}
if (test to decide you want to output a location restriction) {
  if ($counter ==0) {
    $sql .= "WHERE ";
  } else {
    $sql .= "AND ";
  }
  $sql .= "`location`= $location";
  $counter++;
}
...

And that way you have no 1=1 that will scare any security minded person to soil their pants.

now that said .. I'd still prefer prepared statements over this.

[whatson]

prepared statements? How do you mean?

[swa66]

Prepared statements: [php.net...]

The huge advantage is that you do not need to worry about mysql mixing commands and data (and start to execute what you think is data as if it were commands.)

The disavantage is that you need a slightly bit more effort to be able to tell mysql what is data and what is the command.

[vincevincevince]

swa66- what's the security risk of "WHERE 1 AND xyz"?

[swa66]

There's no direct risk in it as such.

It's just that if you /a security minded person / anomaly detection software sees that going to the database, there's an almost automatic assumption some SQL injection is happening.

There's a (small) indirect risk in that it would dull the senses and make it harder than it needs to be to put in place anomaly detection (or make it less effective.

It's easy enough to output sql commands that do not have it in them, so why do it then ?