<?php

	$sql = "SELECT
				topics.topic_id,
				topics.topic_subject,
				topics.topic_date,
				topics.topic_cat,
				topics.topic_by,
				COUNT(posts.post_id) AS posts,
				categories.cat_id,
				categories.cat_name,
				users.user_id,
				users.user_name
			FROM
				topics
			LEFT JOIN
				posts
			ON
				posts.post_id = topics.topic_id
			LEFT JOIN
				categories
			ON
			    topics.topic_cat = categories.cat_id
			LEFT JOIN
				users
			ON
			    topics.topic_by = users.user_id
			GROUP BY
				topics.topic_subject, topics.topic_cat, topics.topic_id
			ORDER BY
				posts ASC
            LIMIT
                10";

	$result = mysql_query($sql) OR die(mysql_error());

	if(!$result) {

	echo '<p class="false">' . $lang['no_available_topics'] . '</p>' . mysql_error();

		}else{

		if(intval(mysql_num_rows($result)) == 0) {

		echo '<p class="false">' . $lang['no_available_topics'] . '</p>';

	}else{

    echo '<h1>' . $lang['favourite_topics'] . '</h1>';

    echo '<table width="100%" class="tables">
    <tr>
    <td width="35%"><strong>' . $lang['topic'] . '</strong></td>
    <td width="20%"><strong>' . $lang['one_category'] . '</strong></td>
    <td width="15%"><strong>' . $lang['author'] . '</strong></td>
    <td width="20%"><strong>' . $lang['date'] . '</strong></td>
    <td width="10%" align="center"><strong>' . $lang['plural_post'] . '</strong></td>
    </tr>';

	while($row = mysql_fetch_assoc($result)) {

    $select_posts = mysql_query("SELECT post_id FROM posts WHERE post_topic = " . (int)$row['topic_id'] . "");
    $count_posts = intval(mysql_num_rows($select_posts));

        echo '<tr>
        <td width="35%"><a href="topic.php?id=' . (int)$row['topic_id'] . '" class="topic_head">' . htmlentities($row['topic_subject'], ENT_QUOTES) . '</a></td>
        <td width="20%"><a href="category.php?id=' . (int)$row['cat_id'] . '">' . htmlentities($row['cat_name'], ENT_QUOTES) . '</a></td>
        <td width="15%"><a href="profile.php?user_id=' . (int)$row['user_id'] . '">' . htmlentities($row['user_name'], ENT_QUOTES) . '</a></td>
        <td width="20%">' . date('d.m.Y - H:i', strtotime($row['topic_date'])) . '</td>
        <td width="10%" align="center"><div class="bubble"><span class="bubpoint"></span><span class="bubcon">' . $count_posts . '</span></div></td>
        </tr>';
        }

    echo '</table>';
    }
    }

?>