ORDER BY in msql query

QuestionsORDER BY in msql query
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

2 Answers
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

Your Answer

4 + 12 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?