1 Topic by MattF (edited by MattF 2007-12-20 04:43)

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Topic: Fetching topic list

Just wondering if someone could give me a whack with the clue stick on this one. big_smile Trying to fetch a list of topics, (just the first message from), and it ain't working. big_smile It's bringing several posts from one topic, instead of just the first. My db skillls are letting me down yet again. What am I missing?

$result = $db->query('SELECT t.id, t.posted, t.subject, t.poster, p.message, p.hide_smilies, f.forum_name AS forum, c.cat_name AS category FROM '.$db->prefix.'topics t LEFT JOIN '.$db->prefix.'posts p ON p.topic_id=t.id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'categories AS c ON f.cat_id=c.id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.moved_to IS NULL '.$where.' ORDER BY posted DESC LIMIT '.$postslimit) or error('Unable to fetch forum posts', __FILE__, __LINE__, $db->error());

The where var is one of either topic, forum or category id.


Cheers,

Matt

MattF's Website

2 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Fetching topic list

The penny finally dropped. big_smile


$result = $db->query('SELECT t.id, t.posted, t.subject, t.poster, p.message, p.hide_smilies, p.posted AS pposted, f.forum_name AS forum, c.cat_name AS category FROM '.$db->prefix.'topics t LEFT JOIN '.$db->prefix.'posts p ON (p.topic_id=t.id AND t.posted=p.posted) INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'categories AS c ON f.cat_id=c.id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.moved_to IS NULL '.$where.' ORDER BY posted DESC LIMIT '.$postslimit) or error('Unable to fetch forum topics', __FILE__, __LINE__, $db->error());

It was due to retrieving the message content, I was still pulling from posts by topic id, so I've changed it to compare topic posted time by message posted time as in the above. Is that code above optimal, btw?


Cheers,

Matt

MattF's Website