BadTigrou BadTigrou - 1 month ago 6
PHP Question

SQL request files with multiples tags

I'm currently designing a website for file hosting. I'm sorting files by tags and I have an issue for building a search request in SQL. If I search for multiple tags at once, i want to be able to get the files that have all these tags.

Below is an example of what i want to do. If my search is "#code #python", my request must return me the files that have both tags (file with id 2 in this example). If the search is just "#code", the request must return file 2 and 3.

Here is how i store all the data, i have a table for files, one for tags and an other one with the links between files and tags (files_tags).

Table Tags
------------------------
| id | name |
------------------------
| 1 | Code |
------------------------
| 2 | Python |
------------------------
| 3 | PHP |
------------------------

Table Files
------------------------
| id | name |
------------------------
| 1 | python.pdf | ( #python )
------------------------
| 2 | main.py | ( #code #python )
------------------------
| 3 | class.php | ( #code #php )
------------------------

Table files_tags
------------------------
| id | tag | file |
------------------------
| 1 | 1 | 2 |
------------------------
| 2 | 1 | 3 |
------------------------
| 3 | 2 | 1 |
------------------------
| 4 | 2 | 2 |
------------------------
| 5 | 3 | 3 |
------------------------


My current request is :

SELECT DISTINCT `files`.* FROM `files`, `files_tags`, `tags` WHERE `files`.`id` = `files_tags`.`file` AND `files_tags`.`tags` = `tags`.`id` AND `tags`.`name` LIKE %"#code #python"%;


But it gets me all the 3 files, not just file 2.

What am i doing wrong ?

Thanks for help

Answer

first of all, you sould take your search text, and use it as an array, that is , not a string. Split values by space " " so you have an array where each element represents a tag.

select * from files where length_of_tags_array = (select count(1) from tags inner join files_tags on files_tags.tag = tags.id where files_tags.file = files.id and tags.name in ("+putyourtagshere+") )

  • since your tags are strings, add each element enbetween quitations
  • dont use like in this case for it would retrieve tags that are substring pof other tags such as "C" is a substring of "C#"
  • remove the hashtag as well since it is not in your stored tag's name