Method for matching any string in SQL

Question

I have a simple SQL query,

SELECT * FROM phones WHERE manu='$manuf' AND price BETWEEN $min AND $max

The issue is that all of the variable fields are connected to fields that will sometimes be empty, and thus I need a way to make them match any value that their respective field could take if they are empty. I tried

$min=$_REQUEST['min_price'];
$max=$_REQUEST['max_price'];
$manuf=$_REQUEST['manufact'];
if (empty($min)){
    $min=0;}
if (empty($max)){   
    $max=900000;}
if (empty($manuf)){
    $manuf='*';}

which works ok for the numerical fields (although I don't like having to manually set limits like this), but I can't figure out how to get a universal match for the text field. I thought * would do it since it matches all row names, but apparently not. Anyone know how to do this?

Answer 1

I am guessing that you want to make it a "filter" type query that allows the parameters to be optional? You could do something like:

SELECT 
    * 
FROM 
    Phones 
WHERE
    (manu = $manu OR manu IS NULL)
    AND (min = $min OR min IS NULL)
    AND (max = $max OR max IS NULL)

Adding the OR with the null option (you can make it an empty string or whatever) allows you to filter on any parameter passed.

You can combine this with what you have above, checking for min/max and supplying defaults for your BETWEEN clause.

Answer 2

Hi,

If I were you, I would cut the whole SQL into parts, and dealing with the WHERE statement.

Somethin like:

sql = "SELECT * FROM phones WHERE 1=1 "
if (not empty($manu))
    sql = sql + ' AND manu = "$manu"'
...

Regards.

Answer 3

I tend to do this by changing the logic in the SQL and passing in NULL parameters, or parameters with specific values.

WHERE manu='$manuf' AND price BETWEEN $min AND $max

=>

WHERE
    (manu='$manuf' OR '$manuf' = '*')    -- Check for "special value"
    AND (price >= $min OR $min IS NULL)  -- Check for NULL
    AND (price <= $max OR $max IS NULL)  -- Check for NULL

Answer 4

Maybe something like...

SELECT * FROM phones 
WHERE manu = coalesce(?, manu)
AND price >= coalesce(?, price) 
AND price <= coalesce(?, price)

Then pass in parameters instead of putting in the variables directly (not sure how to do that in PHP off the top of my head).