Problem in mysql when ordering by more than one column using ORDER BY FIELD

Question

I have a MySQL query where I sort by field like this:

"... WHERE (patterns.id IN($idsJoin)) $where
                       ORDER BY FIELD($idsJoin2) LIMIT 0 , $numLines";

where $idsJoin2 is something like this:

 my $idsJoin = join(',',@ids);
 my $idsJoin2="patterns.id, ".$idsJoin;

and "@ids it's an array with numbers I want to order by.

The thing is that after ordering by $idsJoin2, I want to order by another columns, like this:

"WHERE (patterns.id IN($idsJoin)) $where
                       ORDER BY FIELD($idsJoin2), products.product, versions.version, builds.build LIMIT 0 , $numLines";

If I put that columns before ORDER BY FIELD it sorts by them with any problem, but if they are after the ORDER BY FIELD, which is what I want, it just ignores them and just sorts by the ORDER BY FIELD statement.

I don't see why is this happening. Any idea? Thanks.

Answer 1

Why are you adding the ids into $idsJoin2? Shouldn't it just be 'patterns.id' if you want to order by that column?

Answer 2

Hi, sorry that I forgot to say that I solved the issue. Anyways thanks for the reply.

The problem is that it was getting just one row as result, so it wasn't possible to order by any other criteria. The final solution for the issue is here:

http://forums.devshed.com/mysql-help-4/problem-when-using-a-temporary-table-631676.html