Gary Brett asked 10 years ago
Hello there, I have a query that runs fine myphpadmin but throws errors when added to the grid, I've worked out that it doesn't like the following line..
Order By
If(q.Adviser Is Null, 1, 0), q.Total Desc"
I could take it out but its using a rollup to sum data and keep total at the bottom..
Can I not use ORDER BY inside the mysql query for the grid?
Thank you
Abu Ghufran answered 10 years ago
Order by cannot be used in select_command.
You have to use sortname option. This is required as we need function for sort using grid headers.
$opt["sortname"] = "field_name desc";
$g->set_options($opt);
One more limitation is, you cannot use complex statement in it. Alternate way is to alias the fields in select query, and use that alias in 'sortname' option.
e.g.
$g->select_command = "select …. If(q.Adviser Is Null, 1, 0) as adv, q.Total, …. ";
Then you can use,
$opt["sortname"] = "adv, q.total";
$grid["sortorder"] = "desc";
This will add ORDER BY adv, q.total desc in SQL.
Gary Brett answered 10 years ago
Thanks Abu, think it was the 'WITH ROLLUP' in query, now took your afbvice and working ok..
If it helps anyone removed order by in mysql and added to grid options as below;
// include to make grid changes
$grid["sortname"] = 'If(q.Adviser Is Null, 1, 0), q.Total'; // by default sort grid by this field
$grid["sortorder"] = "DESC"; // ASC or DESC
