MySQL order by error

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

By benedict - November 9, 2009

Hi guys,

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

By benedict - November 10, 2009

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

By benedict - November 11, 2009

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

By benedict - November 11, 2009

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