How to limit results to a range of records

4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 4, 2010   (RSS)

By terryally - January 1, 2010 - edited: January 1, 2010

I want to return the next three individual records: #1, #2 & #3 whose dates are in the future.

Selecting the future dates is the easy part. I am using <field_name >= NOW()> and that works well.

What does not work is retrieving the specific record using LIMIT in the SELECT statement. I normally expect to do <'LIMIT' => '1,1'> for the first; <'LIMIT' => '2,1'> for the second, and <'LIMIT' => '3,1'> for the third.

However CMSB is not recognising the syntax. For <'LIMIT' => '1,1'> it is return one record. For the second it is returning the first two records and for the third it is returning the first three records.

Can you tell me how I can achieve the correct result?

Thanks.

Re: [Dave] How to limit results to a range of records

By terryally - January 2, 2010 - edited: January 2, 2010

Hello Dave,

The offset worked brilliantly and the speed of the query was just as good.

I also want to test your recommended option with variables but I am not sure how to using this with getRecords() - or is this with a straight mysql query?

Happy New Year to you 'n staff.

Terry

Re: [terryally] How to limit results to a range of records

By Dave - January 4, 2010

Hi Terry,

Great, glad to hear it's working.

If you have it working now I wouldn't change it but when you have an array (or list) of records and you want to access them individually you can do so with the list() function in PHP. See: http://php.net/list

So if you had some viewer code like this:
// load matching records
list($newsRecords, $newsMetaData) = getRecords(array(
'tableName' => 'news',
'limit' => '3',
...


You could loop over $newsRecords with foreach() or if you wanted to access them individually you could assign them to variables like this:

list($news1, $news2, $news3) = $newsRecords;

This is sometimes helpful when you want to display records in different places on the page. You could then display record 2's title with <?php echo $news2['title'] ?>.

It can sometimes be an alternative to having to have multiple queries to get individual records. But like I said, if you have it working it's probably safe to leave it (or at least backup your working copy before you experiment!).

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com