Search code examples
phpmysqlsearchtagging

Tagging hierarchy and search

I am trying to create a relatively simple hierarchical tagging system that can be searched. Here's how it works as of now, this is the MySQL table structure:

--------------------------------------------
id  | tag         | parentID | topParentID |
--------------------------------------------
1   | Boston      | NULL     | NULL        |
--------------------------------------------
2   | Events      | 1        | 1           |
--------------------------------------------
3   | June 30th   | 2        | 1           |
--------------------------------------------
4   | NYC         | NULL     | NULL        |
--------------------------------------------
5   | Attractions | 4        | 4           |
--------------------------------------------

So, if a user types Boston in the search bar, they will be delivered the suggestions "Boston Events" and "Boston Events June 30th". Similarly, if they type NYC in the search bar, they will be delivered "NYC Attractions" as a suggestion.

Also, if someone typed Events into the search bar, they would get the suggestion "Boston Events" or if they typed June 30th, they would get the suggestion "Boston Events June 30th"

I've messed around with code to do this, and I can definitely break the query string into keywords then search the tag table for each of the keywords and return matches, but I have not found the correct way to return the full tag strings in the format I mentioned above.

Solution

  • Well, you can join the same table twice. Suppose, we have $id - id of the current tag:

    SELECT
    tags.id,
    tags.tag,
    parent_tags.id,
    parent_tags.tag,
    parent2_tags.id,
    parent2_tags.tag,
FROM
    tags
INNER JOIN
    tags AS parent_tags
ON
    tags.parentID = parent_tags.id
INNER JOIN
    tags AS parent2_tags
ON
    tags.topParentID = parent2_tags.id
WHERE
    tags.id=$id

    But it will give parents and grandparents twice because of the incorrect data in your table: parent.id = parent2.id

    Actually, this is a very primitive solution, allowing only 2 levels of hierarchy to be displayed in 1 request. If you want to implement any levels, read about nested sets on stack. And there is a great book: "Trees and hierarchies in SQL for smarties" by Joe Celko