Moodle Reading data from Database: How to SELECT the first record which meets the WHERE clause? TOP does not work

I want to retrieve the blog post title of the latest blog post by a particular user with the id stored in $myUserId. So from this doc, I have learnt to do this:

$latestPostSubject = $DB->get_record_sql('SELECT subject FROM {post} WHERE userid = ?', array($meUserId));

The problem is that this returns all the blog posts by the user, while I need only the latest one.


WHAT I TRIED:

  1. So when I try something like

    $latestPostSubject = $DB->get_record_sql('SELECT TOP(1) subject FROM {post} WHERE userid = ?', array($meUserId));
    

    Reference.

    I get a syntax error.

Error Reading from Database.

Debug info: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ‘FROM mdl_post WHERE userid = ‘2” at line 1 SELECT TOP(1) *
FROM mdl_post WHERE userid = ? [array ( 0 => ‘2’, )]

Error code: dmlreadexception

  1. The post table also contains a created and lastmodified
    fields, but when I printed the entire record of the user with the *
    from the post table, I failed to understand the format of the date

    • it’s a 10 digit number :s Otherwise I would do some math on the dates on which the blog posts were created.

      …[lastmodified] => 1432051856 [created] => 1432051855…


QUESTION:

So what should I do to get the latest blog post from the table?

Secondly, why doesn’t the TOP(1) in the SQL statement work?


Source: mysql

Leave a Reply