Limt in query not working
3 posts by 2 authors in: Forums > CMS Builder
Last Post: October 18, 2011 (RSS)
By InHouse - October 18, 2011
I'm trying to use a List View to show a single record. The records may have a "coming soon" checkbox value and a "featured" checkbox. I want to show the first record that is checked as coming soon, but failing that, show the first record which is featured. I'm using the following query:
But, for some reason the limt => '1' is not being honoured so I'm seeing the entire list of records. Plus, it's being ordered by 'collection' and not the first options.
Can anyone shed some light on this for me?
list($collectionsRecords, $collectionsMetaData) = getRecords(array(
'tableName' => 'collections',
'limit' => '1',
'orderBy' => 'coming_soon DESC, featured DESC, collection',
'allowSearch' => '0',
));But, for some reason the limt => '1' is not being honoured so I'm seeing the entire list of records. Plus, it's being ordered by 'collection' and not the first options.
Can anyone shed some light on this for me?
Re: [InHouse] Limt in query not working
By Jason - October 18, 2011
Hi,
I did notice that there was no WHERE clause in your query to only return records where the coming_soon or featured check box is selected:
That being said, it's still strange that you are receiving more than 1 record. From the name of the variable being used ($collectionsRecords), I'm wondering if this record set is being overwritten further down the script by a query that is retrieving all collections records. That would explain why the limit and the sorting is missing. Note that the overwrite could even be happening in an include file that is using the same variable name. To check for this, change the name to something else, for example:
Hope this helps
I did notice that there was no WHERE clause in your query to only return records where the coming_soon or featured check box is selected:
list($collectionsRecords, $collectionsMetaData) = getRecords(array(
'tableName' => 'collections',
'limit' => 1,
'where' => "coming_soon = '1'",
'orderBy' => 'coming_soon DESC, featured DESC, collection',
'allowSearch' => '0',
));That being said, it's still strange that you are receiving more than 1 record. From the name of the variable being used ($collectionsRecords), I'm wondering if this record set is being overwritten further down the script by a query that is retrieving all collections records. That would explain why the limit and the sorting is missing. Note that the overwrite could even be happening in an include file that is using the same variable name. To check for this, change the name to something else, for example:
list($comingSoonRecords, $comingsoonMetaData) = getRecords(array(
'tableName' => 'collections',
'limit' => 1,
'where' => "coming_soon = '1' OR featured = '1'",
'orderBy' => 'coming_soon DESC, featured DESC, collection',
'allowSearch' => '0',
));Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Limt in query not working
By InHouse - October 18, 2011 - edited: October 19, 2011
Thanks Jason.
I've added a where clause to control the results based on another variable in the data.
My thinking with this was that in the likely event that nothing was "coming soon", then the query would fall back to the first "featured". If none of those are present, then just grab the first record in the colleciton. But this relies on the limit of 1 actually working.
I'll take a look at the rest of the page to see if something in hijacking the response. You just have to love inheriting projects from other people - you never know what else is being done.
FOLLOW UP:
Turns out there was another query in an include file which was hijacking the process. Changed the names of the queried list variables and all was well again. Thanks for pointing me in the right direction Jason. BTW, the use of OrderBy in this way worked well as a type of "if" statement.
I've added a where clause to control the results based on another variable in the data.
My thinking with this was that in the likely event that nothing was "coming soon", then the query would fall back to the first "featured". If none of those are present, then just grab the first record in the colleciton. But this relies on the limit of 1 actually working.
I'll take a look at the rest of the page to see if something in hijacking the response. You just have to love inheriting projects from other people - you never know what else is being done.
FOLLOW UP:
Turns out there was another query in an include file which was hijacking the process. Changed the names of the queried list variables and all was well again. Thanks for pointing me in the right direction Jason. BTW, the use of OrderBy in this way worked well as a type of "if" statement.