php - MySQL searching files by file name, tags, file category and author
I'm doing something like youtube.
How to do searching for file?
My database structure:
ID (PK) AuthorID (FK) Name varchar
ID(PK) FileID(FK) CatID(FK)
ID(PK) FileID(FK) Tag varchar
- I would like to let user type something in search textbox and then display him best result according to file name and tags.
- When user is watching some file I want to show him proposed files list according to file names, tags, category(same as actual file) and author.
How to do that? I would appreciate any code or natural language explanation how to do that.
Something like that works but it does not use file name, how to change it?
SELECT f.ID, f.Name, count( t.ID ) AS HowsGood FROM Files f LEFT OUTER JOIN TagsToFile t ON f.ID = t.FileID AND t.Tag IN ( 'lucky', 'wow', 'ninja', 'cat' -- example of searched words ) GROUP BY f.ID, f.Nazwa order by count(t.ID) desc
You could use something like that.