Hi,
I have a grid connector with the following sql
$GetCalloutsSQL = “select t1.CalloutID, t1.ClientID, t1.JobID, t3.CalloutName, t2.TaskDesc, t1.NumberCrew, t1.CalloutDate, t1.ChargeRate, t1.StartTime, t1.NumberHours, t4.lookuptext as Status, t1.Location, t5.ContactName from callouts t1, tasks t2, callouttypes t3, lookups t4, contacts t5 where t1.TaskID=t2.TaskID and t1.CallOutTypeID = t3.CalloutTypeID and t1.Status=t4.lookupvalue and t4.lookupkey=‘calloutstatus’ and t1.JobID=$_GET[JobID] and t1.ContactID=t5.ContactID order by CalloutDate, StartTime, TaskDesc asc”;
$resconn->enable_log(“log.txt”);
$resconn->render_sql($GetCalloutsSQL,“CalloutID”, ‘NumberCrew, TaskDesc, CalloutName, CalloutDate, StartTime, NumberHours, ChargeRate, Location, ContactName, Status’);
This fails, with the response in the log file -
Log started, 10/12/2009 12:12:01
====================================
SELECT t1.CalloutID, t1.ClientID, t1.JobID, t3.CalloutName, t2.TaskDesc, t1.NumberCrew, t1.CalloutDate, t1.ChargeRate, t1.StartTime, t1.NumberHours, t4.lookuptext as Status, t1.Location, t5.ContactName FROM callouts t1, tasks t2, callouttypes t3, lookups t4, contacts t5 WHERE t1.TaskID=t2.TaskID and t1.CallOutTypeID = t3.CalloutTypeID and t1.Status=t4.lookupvalue and t4.lookupkey=‘calloutstatus’ and t1.JobID=951 and t1.ContactID=t5.ContactID ORDER BY CalloutDate, DESC
!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
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 ‘DESC’ at line 1
Which seems to be stripping the last order bys and replacing with a ‘DESC’, any idea why ths is happening?
if i take the last order by off the statement i.e.
$GetCalloutsSQL = “select t1.CalloutID, t1.ClientID, t1.JobID, t3.CalloutName, t2.TaskDesc, t1.NumberCrew, t1.CalloutDate, t1.ChargeRate, t1.StartTime, t1.NumberHours, t4.lookuptext as Status, t1.Location, t5.ContactName from callouts t1, tasks t2, callouttypes t3, lookups t4, contacts t5 where t1.TaskID=t2.TaskID and t1.CallOutTypeID = t3.CalloutTypeID and t1.Status=t4.lookupvalue and t4.lookupkey=‘calloutstatus’ and t1.JobID=$_GET[JobID] and t1.ContactID=t5.ContactID order by CalloutDate”; //, StartTime, TaskDesc asc";
it works fine…
The actual query works fine in mySQL