• From: France
  • Registered: 2004-11-06
  • Posts: 695

Topic: How to get the timestamp of the last post?

I'm doing a little Google Sitemap thingie (setting a dynamic sitemap index to have all?Textpattern, Mediawiki, PunBB, etc.?sitemaps in one place, with appropriate <lastmod> so that the bots know what to parse when I ping), and I have a question.

What function, php file, and/or SQL request can I make to get the date and time of the last guest-accessible post made on my PunBB 1.2.14?

Something quite simple and basic, since it's a simple job, and 1.3 is near anyway.

My PHP skills are very, _very_ basics ; and PunBB source code is even somewhat heavy for me sad

Jeu de rôle en ligne sur table virtuelle ⁂ Shadowrun France ⁂ Chez Blacky

Jérémie's Website

  • Registered: 2006-06-05
  • Posts: 410

Re: How to get the timestamp of the last post?

I didn't test it, but something like this should work:

$result = $db->query('SELECT t.last_post FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_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 ORDER BY t.last_post DESC LIMIT 1') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$last_post = format_time($db->result($result));
  • From: France
  • Registered: 2004-11-06
  • Posts: 695

Re: How to get the timestamp of the last post?

Thanks. With what PunBB files included ? It's an external script, so I don't include anything from PunBB (but I can).

Jeu de rôle en ligne sur table virtuelle ⁂ Shadowrun France ⁂ Chez Blacky

Jérémie's Website

  • From: Detroit, MI USA
  • Registered: 2006-08-11
  • Posts: 80

Re: How to get the timestamp of the last post?

guardian34 wrote:

I didn't test it, but something like this should work:

$result = $db->query('SELECT t.last_post FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_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 ORDER BY t.last_post DESC LIMIT 1') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$last_post = format_time($db->result($result));

is it really necessary to join 3 tables whereas to get the same results, this simple query command outputs the exact same thing?

$result = $db->query('SELECT MAX(last_post) FROM '.$db->prefix.'topics WHERE moved_to IS NULL') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$last_post = format_time($db->result($result));
~thegleek

thegleek's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: How to get the timestamp of the last post?

thegleek wrote:
guardian34 wrote:

I didn't test it, but something like this should work:

$result = $db->query('SELECT t.last_post FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_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 ORDER BY t.last_post DESC LIMIT 1') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$last_post = format_time($db->result($result));

is it really necessary to join 3 tables whereas to get the same results, this simple query command outputs the exact same thing?

$result = $db->query('SELECT MAX(last_post) FROM '.$db->prefix.'topics WHERE moved_to IS NULL') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$last_post = format_time($db->result($result));

No, it doesn't output the same thing.
He wants the date and time of the last guest-accessible post, not any post.

  • Registered: 2006-06-05
  • Posts: 410

Re: How to get the timestamp of the last post?

Jérémie wrote:

Thanks. With what PunBB files included ? It's an external script, so I don't include anything from PunBB (but I can).

Well, I pulled most of that query from extern.php, but you should be fine with just including `include/common.php`.

7 Reply by Jérémie (edited by Jérémie 2007-02-16 01:03)

  • From: France
  • Registered: 2004-11-06
  • Posts: 695

Re: How to get the timestamp of the last post?

Thank you all. It seems to work fine, and I have borrowed the PUN require/define from Smartys sitemap.php smile

All I need is to format the date, and test if it matches the post I need; but it seems fine.

Edit: confirmed, it all works well.

Thanks guys.

The code is pretty short and basic (and probably very badly written), but if anyone is interested by a global sitemap index generator from other sources, just ask.

Jeu de rôle en ligne sur table virtuelle ⁂ Shadowrun France ⁂ Chez Blacky

Jérémie's Website