Add related tags to tag page

Question

I want to add "related tags" on tag pages. So if someone visits a tag page he will see a list of tags which are often used together with the currently viewed tag. Therefor i added a query to qa_include/pages/tag.php

function get_related_tags($tag) {
    $query = "
        SELECT w2.word AS tag, COUNT(*) AS count
        FROM ^tagwords t1
        JOIN ^tagwords t2 ON t1.postid = t2.postid
        JOIN ^words w2 ON t2.wordid = w2.wordid
        JOIN ^words w1 ON t1.wordid = w1.wordid
        WHERE w1.word = $ AND w2.word != $ 
        GROUP BY w2.word
        ORDER BY count DESC
        LIMIT 10
    ";
    return qa_db_query_sub($query, $tag, $tag);
}

and added it to the title.

if (count($related_tags) > 0) {
    $qa_content['title'] .= '</h3><br>';
    foreach ($related_tags as $related_tag) {
        $qa_content['title'] .= '<a href="' . qa_path_html('tag/' . qa_html($related_tag['tag'])) . '">' . qa_html($related_tag['tag']) . ' (' . $related_tag['count'] . ')</a>, ';
    }
    $qa_content['title'] .= ' ';
}

But it seems this are not tags but all words used in questions/answers. How can i display related tags?

Answer 1

I changed the query to a more simple one and do the rest in php.

function get_tag_related_tags($tag) {
    $query = "
        SELECT tags
        FROM ^posts
        WHERE type='Q' AND tags LIKE CONCAT('%', # , '%')
    ";
    $results = qa_db_read_all_assoc(qa_db_query_sub($query, $tag));
    
    $tag_counts = [];
    foreach ($results as $row) {
        $tags = explode(',', $row['tags']);
        foreach ($tags as $t) {
            $t = trim($t);
            if ($t !== $tag && $t !== '') {
                if (!isset($tag_counts[$t])) {
                    $tag_counts[$t] = 0;
                }
                $tag_counts[$t]++;
            }
        }
    }

    arsort($tag_counts);

    $related_tags = [];
    $i = 0;
    foreach ($tag_counts as $t => $count) {
        $related_tags[] = ['tag' => $t, 'count' => $count];
        $i++;
        //if ($i >= 10) break;
    }

    return $related_tags;
}

Now it works.