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
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: [terryally] How to limit results to a range of records
By Dave - January 1, 2010
Are you using getRecords() or a direct mysql query?
If you are using getRecords() you could use this option to return 3 records:
'limit' => 3,
There's another option called 'offset' if you want to get a specific record, such as:
'offset' => 3,
'limit' => 1,
This does the same as LIMIT 3,1 in straight mysql
However, I'd recommend getting all 3 in one query and then doing something like this:
list($record1, $record2, $record3) = $records;
Which will put each record in it's own variable.
Hope that helps!
interactivetools.com
Re: [Dave] How to limit results to a range of records
By terryally - January 2, 2010 - edited: January 2, 2010
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
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!
interactivetools.com