MySQL order by error
9 posts by 3 authors in: Forums > CMS Builder
Last Post: November 13, 2009 (RSS)
By benedict - November 9, 2009
Having an issue with a live site where I am using a gallery to advance through the records of a table one at a time (the page reloads each time they go to the next record).
If you go to http://www.simondstilegallery.com.au/detail.php?colour_tones=Beige
You will see that when you advance one record (click right hand arrow) it works fine, but when you click it again it crashes with the following error:
"MySQL Error: 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 ')) ORDER BY tile_sku' at line 3 "
But when I advance records in other categories, no such error occurs.
Any ideas - is this common?
Re: [benedict] MySQL order by error
Could it just be a corrupt record (?) or two. Images can only be in one category so is it just coincidence that the third image in both categories returns this error?
Re: [benedict] MySQL order by error
MySQL Error: 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 ')) ORDER BY dragSortOrder DESC' at line 3.
Any ideas where to start looking?
Re: [benedict] MySQL order by error
By Dave - November 11, 2009
Sorry for the delay. Any time you have a mysql error you can temporarily add this debug code to see what MySQL CMSB is generating:
'debugSql' => true,
Can you add that option and let me know the output?
Next, if you can attach the viewer file to the thread?
My first guess is there's some custom PHP or MySQL code that's been added in there and it's not escaped or dealing with unexpected values properly.
If you can attach the viewer and post the generated SQL we should be able to debug it pretty quickly.
interactivetools.com
Re: [Dave] MySQL order by error
By benedict - November 11, 2009 - edited: November 11, 2009
SELECT SQL_CALC_FOUND_ROWS `tile_images`.* FROM `cms_tile_images` as `tile_images` WHERE ((`room_type` = '5')) ORDER BY house LIMIT 1 OFFSET 2
SELECT * FROM `cms_uploads` WHERE tableName = 'tile_images' AND fieldName IN ('main_image') AND recordNum IN (29) ORDER BY `order`, num
SELECT SQL_CALC_FOUND_ROWS `accounts`.* FROM `cms_accounts` as `accounts` WHERE (`num` IN (2)) ORDER BY fullname, username
MySQL Error: 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 ')) ORDER BY tile_sku' at line 3And the viewer is attached - this is something you guys helped us out a lot with back in September in this thread:
http://www.interactivetools.com/iforum/gforum.cgi?post=74380;t=search_engine#74380
Re: [benedict] MySQL order by error
By Dave - November 11, 2009
Can you re-attach the viewer? It didn't come through. You need to click the upload button before "Post Reply".
Thanks!
interactivetools.com
Re: [benedict] MySQL order by error
By Chris - November 13, 2009
Please add the line in red to beta_lookupRelatedFields:
// get referenced records
$referencedRecords = array();
foreach ( $lookupsToDo as $refTable => $refNums ) {
if (empty($refNums)) { $referencedRecords[ $refTable ] = array(); continue; }
$refNumsCommaSep = join(', ', array_keys($refNums));
list($refRecords, $refMeta) = getRecords(array(
'tableName' => $refTable,
'where' => "num in ($refNumsCommaSep)",
'debugSql' => true,
));
$refRecordsByNum = array_combine(beta_array_collect($refRecords, 'num'), $refRecords);
$referencedRecords[ $refTable ] = $refRecordsByNum;
}I hope this helps! If that doesn't fix the issue, please add the line in blue and let me know what the output is.
Chris