Error in your SQL syntax

4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 20, 2010   (RSS)

By theclicklab - January 15, 2010

Hi there, having an issue with this code which I am using on another site (different host) without any problem... not sure if it is my code or version of mysql (version: 5.0.22)??

<?php
list($pagesRecords, $pagesMetaData) = getRecords(array(
'tableName' => 'pages',
'where' => 'active ="1"' . ' AND show ="1"',
'loadUploads' => '0',
'allowSearch' => '0',
));
?>

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 'show ="1") ORDER BY dragSortOrder DESC' at line 3

Re: [aquaman] Error in your SQL syntax

By Dave - January 15, 2010

It looks good to me. Click on: Admin > Section Editors and make sure the 'pages' section has been created. Next, check you can add and modify records through the CMS for that section.

If you're still having problems after that, try this debugging option and let us know what it says:

'debugSql' => true,

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Error in your SQL syntax

By Dave - January 20, 2010

I was just going to post a follow up on this one that 'show' is in fact a reserved mysql word:
http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

And a trick for dealing with those is either renaming the field -or- surrounding it with `backticks` (beside the 1 on most keyboards) like this:

'where' => 'active ="1"' . ' AND `show` ="1" ',

Backticks are of telling MySQL that you mean a table or fieldname and not anything else.

Most of the time it's just easier to rename the field, though. :)
Dave Edis - Senior Developer
interactivetools.com