mysql: select ... where id = any value. is it possible?

Question

look at this table please

table
|id| |name| |order|

i must get the rows, where name = something and order = somevalue

so i write

select `id` from `table` where `name` = 'something' and `order` = 'somevalue'

but depend on php logic, sometimes i need to get all rows, where name = somethimg, independently of order value. i don't want to change the query structure, because in practise there are many number of fields, and possible count of queries will became very big. so i want to save the structure of query, and when i need to select just by name, i want to write something like this

select `id` from `table` where `name` = 'something' and `order` = any value 

is it possible?

thanks

Answer 1

SELECT id FROM table WHERE name='something'

If you don't specify and order= ... it will allow any values.

Answer 2

Well, it's kind of a hack, but if you really need to do this, it'll work like this:

select `id` from `table` where `name` = 'something' and `order` = `order`

Then you're just saying "wherever order is the same as itself", so it's always true.

Answer 3

No, this is not possible. You need to change the structure (optionally to a LIKE so you can use '%', but that's very ugly).

However, you don't need to write a different query to handle every possible combination. You can simply create the query dynamically:

//create base query
$query = "select `id` from `table` where `name` = 'something' ";

//add order if we need it
if ($use_order)
  $query .= "and `order` = 'somevalue' ";

//repeat for any other optional part

Note that you should of course still take proper measures to avoid SQL injection and other security issues - I have not included this here in order to keep things simple.

Answer 4

If you are using bound parameters, it would be impossible.

If you just substitute the values, you can do the following:

select `id` from `table` where `name` = 'something' and `order` = `order`

Answer 5

I don't think you have any choice... Once you do a selection you can't "unfilter" and get more rows.

You should just use two queries-- either two independent queries, or one that selects on the name into a temp table, and then (optionally) one that further selects on the order attribute.

Answer 6

Like Chad said above, just set the column equal to itself. But be careful, on some platforms / install configurations, NULL != NULL:

select `id` from `table` where `name` = 'something' and coalesce(`order`,'') = coalesce(`order`,'')

Answer 7

This is a common theme with database queries - you need a variable query depending on how much filtering you wish to apply to the data it queries. You could go the route of having your query repeated as a string throughout your code, but that is bad practice as it increases the complexity of the code needlessly. Chances for errors occur if you need to change the query for some reason, and have to change it in multiple places as a result.

The better solution is to create a function which builds the query for you execute:

function buildMyQuery($name, $order = null) {
    $sql = "SELECT `id` FROM `table` WHERE `name`='$name'";

    if ($order != null) {
        $sql .= " AND `order`='$order'";
    }

    return $sql;
}

You could then run this for just using the 'name' field:

$query = buildMyQuery("somename");

Or this for using both fields:

$query = buildMyQuery("somename", "someorder");

As someone mentioned above, this code is deliberately simplified and contains no contingency for possibly dangerous data passed in via $name or $order. You would need to use mysql_real_escape_string or something similar to clean the data first, at the beginning of the function before either piece of data is used.

Dynamic query generation is a fact of life as Byron says, so I would become accustomed to it now rather than using hack-ish workarounds.