Radoslav Enev NightWarrior Radoslav Enev NightWarrior - 6 months ago 32
SQL Question

Get all topics from my forums their sub-forums in medoo

I'm trying to get all the topics from my forum and the sub-forums in it. However, for some reason, my code doesn't work properly. It doesn't give me any errors, but it shows wrong information. I am using the medoo.
Here is my function:

public function getForumTopicsCount($forumId)
{
$getForumTopicsCount = $this->db->count('forum_topics', [
"topic_forum" => $forumId
]);

$subForums = $this->db->query("SELECT * FROM `forums` WHERE forum_subforum = $forumId")->fetchAll();
$c = 0;
foreach($subForums as $subForum) {
$subForumTopics = $this->db->query("SELECT * FROM `forum_posts`")->fetchAll();
foreach($subForumTopics as $topic) {
if ($subForum['forum_id'] == $topic['topic_forum']) {
$c++;
}
}
}
return $getForumTopicsCount + $c;
}


So in my database I have a table forums and there I have a column forum_subforum which is an integer and its value is the id of their head forum. I also have a forum_topics table which consists of all the topics in my whole forum, where i have a column named topic_forum which is also and integer and is the id of the forum which the topic belongs to.

Thank you for the help. It is highly appreciated.

Answer

I'm not 100% sure I understand your question, but from your code I guess you want to know how many topics there are in a given forum, including any sub forums that forum may have.

I believe the following query should do exactly that:

select count(*) from forum_topics
right join forums on forum_topics.topics_forum = forums.id
where forums.forum_id = :id or forums.forum_subforum = :id

Allow me to give you a few remarks on your code:

  • Avoid select * unless you really need all the data. Limit your queries to what you need.
  • Work with prepared statements in stead of composing your queries with variables the way you do. This may pose a security thread if the $formId you receive comes from a unsafe source.
  • Be careful when you run queries inside a loop. You should ask yourself if that is really necessary. In this case a single query should suffice
  • Comparing and searching for data in large sets is what databases are designed for. Let them do the work when possible, in stead of fetching to much data and doing the processing in php.