In my connector I have the following query:
require("connector/grid_connector.php");
require("connector/db_mysqli.php");
require("../gridconfig.php");
$mode="";
if( isset($_GET['mode']) ) $mode = mysql_real_escape_string( $_GET['mode'] );
$gridConn = new GridConnector($res,"MySQLi");
if( $mode == "full")
$gridConn->render_table("sealsview01","SealID","SealID,RehabID,Name,NameAdoption,ArrivalDate,TagColor,TagID,ChipID,DaysInRehab,StatusName");
else
$gridConn->render_sql("
SELECT sealgeneraldata.*, tagcolor.TagColor, status.Name as StatusName,
DATEDIFF( CURDATE( ), sealgeneraldata.ArrivalDate ) AS DaysInRehab, pool.PoolName
FROM sealgeneraldata
LEFT JOIN tagcolor ON sealgeneraldata.TagColorID=tagcolor.TagColorID
LEFT JOIN status ON sealgeneraldata.StatusID = status.StatusID
LEFT JOIN (SELECT actionmove.* FROM actionmove ORDER BY actionmove.datetimeinitiated DESC ) AS actionmove ON actionmove.SealID = sealgeneraldata.SealID
LEFT JOIN pool ON pool.PoolID = actionmove.PoolID
WHERE status.Name='In Rehab'
GROUP BY SealID
ORDER BY SealID","SealID","SealID,RehabID,Name,NameAdoption,ArrivalDate,TagColor,TagID,ChipID,DaysInRehab,StatusName,PoolName","","");Which produces the following error:
Fatal error: Uncaught exception ‘Exception’ with 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 ‘’ at line 5’ in D:\data\dl\internet\server\xampp\htdocs\MySeal\connector\db_mysqli.php:13 Stack trace: #0 D:\data\dl\internet\server\xampp\htdocs\MySeal\connector\db_common.php(668): MySQLiDBDataWrapper->query(‘SELECT sealgen…’) #1 D:\data\dl\internet\server\xampp\htdocs\MySeal\connector\base_connector.php(527): DBDataWrapper->select(Object(DataRequestConfig)) #2 D:\data\dl\internet\server\xampp\htdocs\MySeal\connector\base_connector.php(508): Connector->get_resource() #3 D:\data\dl\internet\server\xampp\htdocs\MySeal\connector\base_connector.php(443): Connector->render() #4 D:\data\dl\internet\server\xampp\htdocs\MySeal\ConnectorViewSeal.php(22): Connector->render_sql(’??SELECT sealge…’, ‘SealID’, ‘SealID,RehabID,…’, ‘’, ‘’) #5 {main} thrown in D:\data\dl\internet\server\xampp\htdocs\MySeal\connector\db_mysqli.php on line 13
I have isolated the offending line in the query that seem to produce the error above:
LEFT JOIN (SELECT actionmove.* FROM actionmove ORDER BY actionmove.datetimeinitiated DESC ) AS actionmove ON actionmove.SealID = sealgeneraldata.SealID
The ORDER BY clause in the subquery is the real culprit. I’ve tested the query in phpmyadmin and in php and in both this query works perfectly. If I replace ORDER BY with GROUP BY it works so it’s no unknown column error, that is: it doesn’t throw an exception. The ordering of one column isn’t correct though.
Do I really need to use ORDER BY I don’t know but it would be great if you can help me out with this.