MySQL order by error

9 posts by 3 authors in: Forums > CMS Builder
Last Post: November 13, 2009   (RSS)

Re: [benedict] MySQL order by error

OK, I have tracked this through and it only happens in Bathrooms and Ensuites sections. So I don't think it's the code on the arrows (see attached).

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

I have found the error in another site using the same code/template - it throws this 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

Hi guys, any ideas on this?

Re: [benedict] MySQL order by error

By Dave - November 11, 2009

Hi benedict,

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] MySQL order by error

By benedict - November 11, 2009 - edited: November 11, 2009

Here's the output:

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 3


And 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

Hi benedict,

Can you re-attach the viewer? It didn't come through. You need to click the upload button before "Post Reply".

Thanks!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] MySQL order by error

Sorry - here it is.
Attachments:

detail_007.php 16K

Re: [benedict] MySQL order by error

By Chris - November 13, 2009

Hi benedict,

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.
All the best,
Chris