Topic: Sort array naturally

I have an array that is the result of a query. I want to sort it by a specific column, but:

In MySQL, the column is VARCHAR. Therefore, it is not sorted correctly when using ORDER BY (10 comes before 2).

How can I sort the array as done in natcasesort (can't use that though, because you can't specify which array element is the ordering one.

Wow, that was bad english wink

Thanks in advance

FluxBB - v1.4.8

Re: Sort array naturally

You actually can:
http://www.php.net/array_multisort
Check out the third example, sorting database results.

Re: Sort array naturally

Cool. Thanks. Does that do it like natcasesort?

FluxBB - v1.4.8

Re: Sort array naturally

Well, there's a couple different ways to sort: you specify which one to use.

Re: Sort array naturally

$result = $db->query('SELECT id, size_inches, size_decimal, normal_lbs, 144inch_lbs FROM '.$db->prefix.'sizes WHERE alloy_id='.$id.' ORDER BY size_decimal ASC') or error('Unable to fetch size information', __FILE__, __LINE__, $db->error());
while ($cur_size = $db->fetch_assoc($result))
{
$data['id'][] = $cur_size['id'];
$data['size_inches'][] = $cur_size['size_inches'];
$data['size_decimal'][] = $cur_size['size_decimal'];
$data['normal_lbs'][] = $cur_size['normal_lbs'];
$data['144inch_lbs'][] = $cur_size['144inch_lbs'];
}

array_multisort($data['size_decimal'], SORT_ASC, SORT_REGULAR);

foreach ($data['id'] as $key => $elem_id)
{    
echo '<tr style="text-align: center;"><td style="border: 0;">'.$data['size_inches'][$key].'</td><td style="border: 0;">'.$data['size_decimal'][$key].'</td><td style="border: 0;">'.$data['normal_lbs'][$key].'</td><td style="border: 0;">'.$data['144inch_lbs'][$key].'</td>';
if(!$pun_user['is_guest'])
echo '<td style="border: 0;"><a href="deletesize.php?id='.$elem_id.'">delete</a></td>';

echo '</tr>';
}

This doesn't work.
It only sorts one column and the others stay wrong...

FluxBB - v1.4.8

Re: Sort array naturally

Take a look at the example again, you're doing it wrong

Re: Sort array naturally

Could you define that a little more? I didn't quite understand the example, sorry...

FluxBB - v1.4.8

Re: Sort array naturally

Any reason you can't cast the column in the order by clause?

order by cast(size_decimal as decimal)

so you'd have:

$result = $db->query('SELECT id, size_inches, size_decimal, normal_lbs, 144inch_lbs FROM '.$db->prefix.'sizes WHERE alloy_id='.$id.' ORDER BY cast(size_decimal as decimal) ASC') or error('Unable to fetch size information', __FILE__, __LINE__, $db->error());

Re: Sort array naturally

does cast sort it naturally?

FluxBB - v1.4.8

Re: Sort array naturally

oh I understand now. It's not always only a decimal... but sometimes something lik 2.00x5.04 or whatever...

FluxBB - v1.4.8

Re: Sort array naturally

lie2815 wrote:

oh I understand now. It's not always only a decimal... but sometimes something lik 2.00x5.04 or whatever...

Thats weird data.  What would you want that to sort by?  The cast will cast that as 2.00. not sure what the multisort would do.

Re: Sort array naturally

Lurker.boi wrote:
lie2815 wrote:

oh I understand now. It's not always only a decimal... but sometimes something lik 2.00x5.04 or whatever...

Thats weird data.  What would you want that to sort by?  The cast will cast that as 2.00. not sure what the multisort would do.

It probably wouldn't do much better: the data isn't normalized and it really should be wink
You have to make the last parameter of your multisort call the original array, the one you want sorted (which you don't have in your code).

Re: Sort array naturally

do you know what I want to do? Because it would be great if you could just tell me what kind of array you want and write me the one line with array_multisort.

Pleaaaaaaaaaaaase

FluxBB - v1.4.8

Re: Sort array naturally

As I said, look at the third example. It has the exact correct syntax for generating the array AND for the array_multisort call.
Anyway, I'll modify your code so that it should at least sort the array properly.

$result = $db->query('SELECT id, size_inches, size_decimal, normal_lbs, 144inch_lbs FROM '.$db->prefix.'sizes WHERE alloy_id='.$id.' ORDER BY size_decimal ASC') or error('Unable to fetch size information', __FILE__, __LINE__, $db->error());
$sizes = array();
$size_decimal = array();
while ($cur_size = $db->fetch_assoc($result))
{
$sizes[] = $cur_size;
$size_decimal[] = $cur_size['size_decimal'];
}

array_multisort($data['size_decimal'], SORT_ASC, SORT_REGULAR, $sizes);

foreach ($sizes as $cur_size)
{    
echo '<tr style="text-align: center;"><td style="border: 0;">'.$cur_size['size_inches'].'</td><td style="border: 0;">'.$cur_size['size_decimal'].'</td><td style="border: 0;">'.$cur_size['normal_lbs'].'</td><td style="border: 0;">'.$cur_size['144inch_lbs'].'</td>';
if(!$pun_user['is_guest'])
echo '<td style="border: 0;"><a href="deletesize.php?id='.$cur_size['id'].'">delete</a></td>';

echo '</tr>';
}

15 (edited by lie2815 2008-04-27 13:14)

Re: Sort array naturally

Now, after I copied your code and fixed your error, it works wink

It should have read:

array_multisort($size_decimal, SORT_ASC, SORT_REGULAR, $sizes);

Still:
Thanks a lot!!!

FluxBB - v1.4.8