J13t0u J13t0u - 1 year ago 53
SQL Question

How best to search specific tags with PHP and JS in MySQL

How best to search for an item that only has tags users search for, whether it has all of the tags or just some of the tags?

So for instance:

item | param
1 | a
1 | b
1 | c

2 | b
2 | c
2 | d

3 | c
3 | d
3 | e

4 | d
4 | e
4 | f

item 1 has a,b,c; item 2 has b,c,d; item 3 has c,d,e; item 4 has d,e,f

User input into search bar with params a,c,d,e,g; 1 and 2 have b and 4 has f which are outside user's params. So only item 3 because it has only c,d,e and nothing else and these 3 params fall into user's params. It does not have a or g, but that does not matter.

I can only think of a few ways to do this. And the best way I can think of is:

1: In another table, say the main table for the items, store the number of tags this item has.

2: Loop query through the entire table using parameters like
WHERE (param=$user_para[$key])
one param at a time. Every time a param gets matched, the item id gets added to an array.

3: After the loop finishes, an algorithm counts how many times an item id appears in the array and compares to the number of tags in the main table. If it's less than the stored number (so something other than the tags user wants is in that item), then array removes all of that item id and checks the next one.

I feel like this is still very bad. What is a better solution to this?

I put JS/AJAX in the tag because I want to know what can be done in front end instead of putting more stress on the back end.

Answer Source

1. split the parameters you get from the user into an array and build the WHERE part of the SQL query. Suppose user wants params a,b:

$input = $_POST['params']; // eg: a,b
$params = explode(',', $input); // eg: [a,b]
$qry_params =[]; //holds the processed parameters 

//todo: here you want to escape each input param to prevent SQL injection

foreach($params as $param) $qry_params[] = "params like '%$param%'";    

//$where will be: where params like '%a%' or params like '%b%'
$where = " where ".implode(" or ",$qry_params);

2. Use an SQL query that will give you all params grouped by item. Note that $where is at the bottom

select * from(
    select item, GROUP_CONCAT(param) as params
    from tbl
    group by item
) t
where params like '%a%' or params like '%b%'

See the result set

item | params
1    | a,b,c,d
2    | e,d,c,b

3. Now you have all items that have at least one of the params the user requested. The last step is to loop through results and get rid of those with parameters the user never asked for

Suppose all the db records returned are put in a $rows array

$matches = []; // final result
foreach($rows as $row){
    $dbParams = explode(',', $row['params']); eg: [a,b,c,d] for item 1

    //array_diff returns array of values in $dbParams not found in $params
    //if there is no such value (db has no extras the user doesn't want)
    //add the current row to matches.   
    if(!array_diff($dbParams,$params)) $matches[] = $row;

At this point $matches is the subset of the result set from earlier that satisfies your requirements.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download