如何使用php计算数据库中的标签（关键字）数量[关闭]

Question

I like the way this website shows tags count like javascript× 988307 . I want to do same on my website. Please suggest me best approach to do this with php and mysql. one way to doing this is simply counting keywords which i guess that will be a heavy process. Thanks

Answer 1

The database should have a separate foreign key table which contains only the keywords.

QuestionTable(ID, title, body, ...)
FlagTable(ID, name)
QuestionFlagTable(ID, IDquestion, IDflag)

$sql = "SELECT COUNT(*), f.ID, f.name" .
    " FROM QuestionFlagTable qf" .
    " INNER JOIN FlagTable f ON (f.ID = qf.IDflag)" .
    // optional WHERE (e.g. only specific flags)
    " GROUP BY f.ID, f.name" .
    // optional HAVING (e.g. min. count)
    // optional ORDER BY (e.g. by name or count)
    "";

You can, of course, search the question body for keywords, but that is a very db-heavy process. Think of something like this:

$sql = "SELECT COUNT(*) FROM QuestionTable WHERE body LIKE ?";
$sth = $pdo->prepare($sql);
$sth->execute(array('%' . $keyword . '%'));