SELECT ... ORDER BY an array....

[briant]

I'm new when it comes to arrays. It's pretty confusing but I will try my best to describe what I am trying to accomplish.

 

I would like to SELECT fields from a database and order it. Here is an example.

 

SELECT * FROM mytable ORDER BY ranks;

 

The ranks field has: 11,5,33,2,66,39

So if you were to explode it into an array, the [0] value would be 11.

I want my results to be ordered by the [0] array. How am I to do that?

 

I suppose if you were to nest it, it could work but I'm pretty sure there's an easier way.

 

Thanks for reading... would appreciate any help.

[sasa]

try

SELECT * FROM mytable ORDER BY ranks+0;

[briant]

That don't work... its stored in the database as a string, not an array.

 

I'm pretty sure I gotta explode it somehow.

[briant]

If I'm able to explode it and call that [ 0 ] value all in one line, I think, then I'll be able to order it.

 

SELECT * FROM mytable ORDER BY explode(",", ranks); // But this is not correct.

 

For some reason, I think I should also add the 'list' syntax like I see in this example:

 

$data = "foo:*:1023:1000::/home/foo:/bin/sh";
list($user, $pass, $uid, $gid, $gecos, $home, $shell) = explode(":", $data);
echo $user; // foo
echo $pass; // *

[trq]

The ORDER BY clause expects a field name to order by. its really not clear what field you wish to order by.

[briant]

Well, the field I want order is the 'ranks' field but 'ranks' WAS an imploded array. I just want the first value of 'ranks' to be ordered not all the other values.

[trq]

Ok, thats probably not the best way to store the ranks, hence making it more difficult to sort by... anyway. Something like....

 

SELECT *, SUBSTR(ranks,LOCATE(',',ranks)-1) AS firstranks FROM mytable ORDER BY firstranks;

 

May help. Not at all tested though.

[briant]

GREAT thorpe, I didn't know you were able to call a variable beforehand.

 

That did the trick for sure, thanks again... where is the Topic Solved button?

 

Your time helping me out was much appreciated, thanks so much thorpe.

[sasa]

SELECT * FROM mytable ORDER BY ranks+0