PHP: sort string

Question

I have a string like this:

{{14,2,53045,2,1,0,156,100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53045,2,1,0,168,500,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,168,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,97,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0}}

But I like to get all 8 lines ordered by the 3rd, then 7th and 8th value so the result would be:

{{14,2,53139,2,1,0,467,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0
{14,2,53045,2,1,0,156,100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53045,2,1,0,168,500,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,168,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,97,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0}}

Does anyone have an idea how to achieve this?

Thanks in advance,

Andreas

edit:

SQLite query:

select owner, '{{' || 
    group_concat(
        event||','||parent||','||key||','||target||','||tick_count||','||tick_interval||','
        ||ifnull(arg1,'NULL')||','||ifnull(arg2,'NULL')||','||ifnull(arg3,'NULL')||','||ifnull(arg4,'NULL')||','
        ||ifnull(arg5,'NULL')||','||ifnull(arg6,'NULL')||','||ifnull(arg7,'NULL')||','||ifnull(arg8,'NULL')||','
        ||ifnull(arg9,'NULL')||','||ifnull(arg10,'NULL')||','||ifnull(arg11,'NULL')||','||ifnull(arg12,'NULL')||','
        ||ifnull(arg13,'NULL')||','||ifnull(arg14,'NULL')||','||ifnull(arg15,'NULL')||','||hasReq, '},{')||'}}' as key_value_pairs 
from items_functions    WHERE owner = 281201 group by owner

This looks almost like JSON but not quite. Is this a format that can be parsed into an array/object…? — deceze, Jul 30 '18 at 08:39
This is the result of my SQLite Query. But order by clause is not working with group_concat so I thought to order the result of it is what has to be done. added query to my post. — Andreas B, Jul 30 '18 at 08:47
If you use `[...]` rather than `{...}`, you could convert it to an array of arrays with [`json_decode`](http://php.net/json_decode) and then use this reference to sort it: https://stackoverflow.com/questions/17364127/how-can-i-sort-arrays-and-data-in-php — IMSoP, Jul 30 '18 at 08:50
Why do you need to concatenate it to pseudo-JSON in the database in the first place? Fetch it as arrays from the database, sort it (why don't you do that in the query too?!), then encode it to whatever string format you need in PHP. — deceze, Jul 30 '18 at 08:52
I'd use @deceze answer in the linked question (with `usort()` and spaceship operator) but you should sort it in your SQL query — AymDev, Jul 30 '18 at 08:54
Why does 156 sort before 168 but 97 sorts after it? Is that what you really want? — Nick, Jul 30 '18 at 09:02
Thanks for the answers so far. Well, those are different approaches worth to try. But unfortunately, not answers to my question. Don't get me wrong, I am thankful for them. But still, I'd like to know how to achieve it the way I described. @Nick well, in fact, I just need it to be sorted by value 3 DESC. — Andreas B, Jul 30 '18 at 09:10
To sort this as a string would be highly complicated. At the very least you'd need to break it apart into separate lines which *can* be sorted, then sort them (e.g. using `usort`, breaking the lines apart more to get the right values to compare by), then you need to reassemble it back into your desired string format. — deceze, Jul 30 '18 at 09:40

score 1 · Accepted Answer · answered Jul 30 '18 at 10:21

This code will do what you have asked for (ordering the elements in the string based on value 3 descending), based on your question. As has been pointed out in the comments, there are probably many better ways to do it, starting with rewriting the query. However without seeing how you are using the data it's hard to say what the best way to rewrite is.

$data = '{{14,2,53045,2,1,0,156,100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53045,2,1,0,168,500,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,168,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,97,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0}}';
$json = str_replace(array('{', '}', 'NULL'), array('[', ']', '"NULL"'), $data);
$arr = json_decode($json);
//print_r($arr);
// sort based on element 3 descending
usort($arr, function ($a, $b) { return - ($a[2] <=> $b[2]); });
//print_r($arr);
$json = json_encode($arr);
$data = str_replace(array('[', ']', '"NULL"'), array('{', '}', 'NULL'), $json);
print_r($data);

Output:

{{14,2,53139,2,1,0,467,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53139,2,1,0,467,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53045,2,1,0,156,100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0},
{14,2,53045,2,1,0,168,500,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,168,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1},
{14,2,53045,2,1,0,97,200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0}}

PHP: sort string

1 Answers1