Making a dynamic filter in a sql query-CodePudding

I have this code:

 <?php
$count_item = mysql_query("SELECT * FROM stdevice
    JOIN stlocation ON stdevice.location = stlocation.stdev_id
    JOIN device_name ON stdevice.dev_id = device_name.dev_id
    WHERE dev_name LIKE '%$dev_name%'
    AND stdev_location_name LIKE '%$stdev_location_name%'
    AND dev_serial LIKE '%$dev_serial'");
$count = mysql_num_rows($count_item);
?>

This query works via a search bar where i specify the device name (choosing one from a list), the location name (choosing from another list) and the serial (this time, writing it in the search bar).

What i want is for this query to work when either 1 or 2 of the filters are in blank (so, if i leave dev_name AND stdev_location name in blank on the search bar, it only uses dev_serial as a filter, but if it has dev_name and dev_serial but the other is left blank, it uses the 2 in the search filter, and so on). Ideally it should show a number that is different from 0 whenever the filters work and there is (there are) a device that fullfills the conditions. Now it always sits at 0, even when i filter for a location where i know there are devices (and i leave the other filters blank).

I've tried this:

  <?php
$count_item = mysql_query("SELECT * FROM stdevice
    JOIN stlocation ON stdevice.location = stlocation.stdev_id
    JOIN device_name ON stdevice.dev_id = device_name.dev_id
    WHERE dev_name LIKE '%$dev_name%'
    OR stdev_location_name LIKE '%$stdev_location_name%'
    OR dev_serial LIKE '%$dev_serial'");
$count = mysql_num_rows($count_item);
?>

but it doesnt work as intended. what could i do?

PS: i've searched

Search Form with One or More (Multiple) Parameters

but sadly the difference in here is that i'm forced to use mysql, as my boss uses a legacy system in this database and will not update to PDO

CodePudding user response：

Your question is essentially a SQL one. I'll ignore all the other commentary about PDO and query parameters.

The best performance case would be to use several conditional statements and build your sql query in pieces to remove the unnecessary clauses but if you absolutely want to do this as a single query:

Essentially you have 3 separate conditional cases. You can simply represent them as 3 groups of conditional clauses using Parenthesis.

SELECT * FROM stdevice
JOIN stlocation ON stdevice.location = stlocation.stdev_id
JOIN device_name ON stdevice.dev_id = device_name.dev_id
WHERE 
(
    dev_name LIKE '%$dev_name%'
)
OR
(
    stdev_location_name LIKE '%$stdev_location_name%'
)
OR 
(
    dev_serial LIKE '%$dev_serial'
)

However this does not check to see if the search field has been entered. So you just need to check for each condition.

SELECT * FROM stdevice
JOIN stlocation ON stdevice.location = stlocation.stdev_id
JOIN device_name ON stdevice.dev_id = device_name.dev_id
WHERE 
(
    '$dev_name' <> '' dev_name LIKE '%$dev_name%'
)
OR
(
    '$stdev_location_name' <> '' AND stdev_location_name LIKE '%$stdev_location_name%'
)
OR 
(
    '$dev_serial' <> '' AND  dev_serial LIKE '%$dev_serial'
)

So if you did not provide a value (coalesced into an empty string) for $dev_serial, it would just turn into this after you do your interpolation:

(
    '' <> '' AND  ## always false
    dev_serial LIKE '%' ## ignored
)

By default (just eyeballing it) I believe if you don't enter in any search values, you will get 0 results. I think this would be the intended result, but if not, you will need to add an additional condition that simply checks if all the values are empty strings.

(
    '$dev_serial' = '' AND 
    '$stdev_location_name' = '' AND 
    '$dev_name' = ''
)

This should return every row as this would evaluate to true always.

(
    '' = '' AND 
    '' = '' AND 
    '' = ''
) ## always true

CodePudding user response：

The fact that your code still uses the mysql_query() style functions indicates it's still running PHP 5.x. The extension that includes those functions was removed from PHP in 2015. See Why shouldn't I use mysql_* functions in PHP? for details on that, and I encourage you to show it to your boss.

If it helps, newer versions of PHP have much better performance than the version you're running. You could get a lot more bang for your buck by upgrading the PHP software. See https://dev.to/tomzur/php-benchmarks-2021-for-20-different-php-platforms-on-seven-different-php-versions-5-6-7-0-7-1-7-2-7-3-7-4-8-0-3nbp for benchmark comparisons.

Though admittedly it will require some code updates to work around deprecated features, and I understand why a business may decide not to invest time into developing new code in a legacy application. Anyway, it's up to you.

On to your question. There are solutions to make an SQL query have a variable set of conditions, even in the old version of PHP you're using. Here's a proof of concept, but I won't test it, and it's up to you to apply it to your code:

<?php

$sql = "SELECT * FROM stdevice
    JOIN stlocation ON stdevice.location = stlocation.stdev_id
    JOIN device_name ON stdevice.dev_id = device_name.dev_id
    WHERE ";

$conditions = array("false");

if (isset($dev_name)) {
    $dev_name_esc = mysql_real_escape_string($dev_name);
    $conditions[] = "dev_name LIKE '%$dev_name_esc%'"
}

if (isset($stdev_location_name)) {
    $stdev_location_name_esc = mysql_real_escape_string($stdev_location_name);
    $conditions[] = "stdev_location LIKE '%$stdev_location_name_esc%'"
}

if (isset($dev_serial)) {
    $dev_serial_esc = mysql_real_escape_string($dev_serial);
    $conditions[] = "dev_serial LIKE '%$dev_serial_esc%'"
}

$sql .= implode(" OR ", $conditions);

$count_item = mysql_query($sql);

$count = mysql_num_rows($count_item);
?>

P.S.: Using LIKE with the leading '%' wildcard means the query cannot be optimized with indexes, so perhaps I spoke too soon when I suggested upgrading PHP would yield performance improvements. If a lot of code in your application is designed similarly, and the business has no budget to redesign it, then it's a lost cause. All you can do is hold your nose and try not to think about it.