Migrate to new server and get SQL error

Hey Benedict,

It sounds like CMS Builder is trying to create an empty list field for some reason.

Could you give me a bit more detail about when this error appears? Is it when you edit a particular record in the accounts section? Or is this when you're trying to set up the CMS Builder on the new server? 

Thanks!

Greg

Sorry I haven't come back to you sooner - the client held off the server migration again until now. So I have tracked down the problem to one issue:

This is a Wine List page that shows the wines from a particular range.

The code that worked fine on the old server, but not this one is:

  // load records
  list($winesRecords, $winesMetaData) = getRecords(array(
    'tableName'   => 'wines',
    'where' => "range = '". intval($rangeRecord['num']) ."'" ,
  ));
  


When I remove this code, the page loads at least, without the wine list. When I include the code, I get this (when trying to load the wine range MWC which is range #11):

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 'range = '11')' at line 3


FInal piece of info - the WInes table pulls the Ranges from the Ranges table using the GET OPTIONS FROM DATABASE option, values=num, labels=title.

Detail pages all present fine as well.

Hi Bendict, 

I'm wondering if the new myslq server you're using doesn't allow the word range to be used without it being declared as a table name. Try changing your code to this:

  // load records  list($winesRecords, $winesMetaData) = getRecords(array(
    'tableName'   => 'wines',
    'where' => "`range` = '". intval($rangeRecord['num']) ."'" ,
  ));

So I've added apostrophes around the range table name (note: you have to use the apostrophe below the esc key on the keyboard, as this is what MySQL uses for table names.)

If that doesn't work, could you add the debug SQL command to the getRecords statement:

  // load records  list($winesRecords, $winesMetaData) = getRecords(array(
    'tableName'   => 'wines',
    'debugSql     => true,
    'where' => "`range` = '". intval($rangeRecord['num']) ."'" ,
  ));

Then copy the output that is created into a post so I can see exactly what MySQL statement is being created.

Cheers,

Greg

Just for clarity, those characters are called `backticks`  and you want to make sure you get this one ( ` ) which is a backtick character, not this one ( ' ) which is an apostrophe.
http://en.wikipedia.org/wiki/Grave_accent#Use_in_programming

:

Thanks Greg,

That did the trick - as always I am indebted.

Cheers,

Benedict