SQL fetch with user supplied array

Question

I'm trying to build a search function using tags,

My data structure in MySQL

parts                           parts_to_tags            tags
---------------------- ----    -------------------      -----------------
| part_id | part_name     |    |id|part_id|tag_id|      |tag_id|tag_name|
|  1      | 20lb Sledge   |    | 1|      1|     1|      |     1|Hammer  |
|  2      | Framing Hammer|    | 2|      1|     2|      |     2|Sledge  |
|         |               |    | 3|      2|     1|      

I have a list of tags supplied by the user in an array, and I want to compare it to these tables to return all the results, so if the user asks for hammers both tools should be returned, though if they ask for both or just sledge only the 20lb sledge should be returned. I don't want to use the SQL "IN" because I want to use this as a drill down, not if any tag exists but all tags must exist for a result.

My code:

    $keywords = $_POST['tags'];
     // Returns: Array ( [0] => Sledge [1] => Hammer )

    $getInventory = $conn -> prepare("SELECT * FROM 
            parts
            LEFT JOIN parts_to_tags ON parts.part_id = parts_to_tags.part_id
            LEFT JOIN tags ON parts_to_tags.tag_id   = tags.tag_id
            ");

    $getInventory -> execute();

    $partArray = $getInventory -> fetchAll();


             $matched = !array_diff($keywords, $partArray);

             print_r($matched);

However, $partArray seems to be a deep array, so I cannot use array_diff. Does anyone have a different way to go about this? I've tried using a couple different fetch types to but I keep getting array to string conversion errors on the array_diff.

Thanks

Answer 1

You don't have a WHERE clause in the query; it will return the entire table and this is not what you need. More, even if you add a WHERE clause, LEFT JOIN will still return more rows that you need.

A possible solution to your problem could be:

SELECT p.part_id, p.part_name, GROUP_CONCAT(t.tag_name ORDER BY t.tag_name SEPARATOR '|') AS tags
FROM parts p
    INNER JOIN parts_to_tags pt ON p.part_id = pt.part_id
    INNER JOIN tags t ON pt.tag_id = t.tag_id
WHERE t.tag_name IN ('Hammer', 'Sledge')
GROUP BY p.part_id

It selects the parts that have at least one of the tags and also the list of tags associated with each part, sorted ascending and separated by '|'.

Of course, the list of tag names in the IN () condition will be generated in the PHP code from $keywords.

The PHP code after you run the query:

// Sort the keywords ascending
sort($keywords);
// Concatenate them, use '|' as separator
$allTags = implode('|', $keywords);
// Get the result set
$parts = array();
foreach ($getInventory -> fetchAll() as $row) {
    // Keep only the parts that have all the tags
    if ($row['tags'] == $allTags) {
        $parts[] = $row;
    }
}

UPDATE: a simplified version of the query that returns only the parts that have all the specified tags (no extra PHP processing is needed):

SELECT p.part_id, p.part_name, COUNT(t.tag_name) AS nbTags
FROM parts p
    INNER JOIN parts_to_tags pt ON p.part_id = pt.part_id
    INNER JOIN tags t ON pt.tag_id = t.tag_id
WHERE t.tag_name IN ('Hammer', 'Sledge')       # <----------------+
GROUP BY p.part_id        #                                       |
HAVING nbTags = 2         # The number of strings in this list ---+

Answer 2

If I understand correctly, you can resolve as follows. Multidimensional array diff

function arrayRecursiveDiff($aArray1, $aArray2) {
  $aReturn = array();

  foreach ($aArray1 as $mKey => $mValue) {
    if (array_key_exists($mKey, $aArray2)) {
      if (is_array($mValue)) {
        $aRecursiveDiff = arrayRecursiveDiff($mValue, $aArray2[$mKey]);
        if (count($aRecursiveDiff)) { $aReturn[$mKey] = $aRecursiveDiff; }
      } else {
        if ($mValue != $aArray2[$mKey]) {
          $aReturn[$mKey] = $mValue;
        }
      }
    } else {
      $aReturn[$mKey] = $mValue;
    }
  }
  return $aReturn;
} 

  $arr1 =    arrayRecursiveDiff($big_array,$small_array);