Php tags display

Question

There are 4 tables used for storing tags:

tagovi, tagovi_vijesti, tagovi_blogovi, tagovi_dogadjanja

Structure od table tagovi:

id - name

Structure of table tagovi_vijesti:

id - vijesti_id - tag_id

The same is for other two tables.

On main page I display tags from all three tables this way:

$sql = "SELECT
      T.id AS id, T.name AS tag,
      TV.id AS tag_vijest_id, TD.id AS tag_dogadjanje_id, TB.id AS tag_blog_id,
      COUNT(*) ponavljanje
      FROM tagovi AS T
      LEFT JOIN tagovi_vijesti AS TV ON T.id = TV.tag_id
      LEFT JOIN tagovi_dogadjanja AS TD ON T.id = TD.tag_id
      LEFT JOIN tagovi_blogovi AS TB ON T.id = TB.tag_id
      WHERE TV.id IS NOT NULL
      GROUP BY id
      ORDER BY id DESC
      LIMIT 35"; 
    $sql_result = mysql_query($sql,$connect)
     or die("Upit nije izvrsen");

    while ($row = mysql_fetch_array($sql_result)){
     $tag_id = $row["id"];
     $tag_url = $row["tag"];
     $tag = preg_replace('/-/', ' ', $tag_url);
     $tag_ponavljanje = $row["ponavljanje"]; 

     if($tag_ponavljanje >= 10 && $tag_ponavljanje < 20) $fontSize = "14px";
     elseif($tag_ponavljanje >= 20 && $tag_ponavljanje < 30) $fontSize = "16px";
     elseif($tag_ponavljanje >= 30 && $tag_ponavljanje < 40) $fontSize = "18px";
     elseif($tag_ponavljanje >= 40 && $tag_ponavljanje < 60) $fontSize = "20px";
     elseif($tag_ponavljanje >= 60) $fontSize = "24px";
     else $fontSize = "12px";

     echo " &nbsp; <a href = \"$ispisi_link/tag/$tag_url\" style=\"font-size: $fontSize;\">$tag</a> &nbsp; ";
    }

But I don't think that this is the perfect way to do it because tags are sorted by id and then all those old tags will never be displayed again because results are sorted by id. What kind of display (query) do you use? What would you advise me about displaying tags? Is it better to sort them by number of repeating of each tag (in my table repeating is called "ponavljanje")? If I use this way then I'm bit afraid that there will be tags that will be repeated in most of content and new one will never be displayed on main page. Or should I maybe use two queries, one for new tags and one for those who are mostly used?

Thanks, Ile

Answer 1

Did you try to use ORDER By RAND() ? It may kill your perfs, but if you cache the result for 5 or 10 minutes, it should be good enough.

Answer 2

You could mix it up by combining both into a single table (that way you have the best of both worlds).

So if you were going to do a tag cloud of 20 tags, select the 10 most tagged and 10 random ones.

ORDER BY RAND() is going to be pretty rough if you have lots of tags though as Arkh mentioned. Alternatively you could insert each tag with a random integer and then have that column indexed. If you want to shuffle the tags around later you could reassign random values to that column.