MYSQl Data not displaying numbers in order

[malkocoglu]

Hello,

 

I have the following code to get data from mysql.

SELECT COUNT(*),pickup FROM journeys WHERE pickup_type='postcode' GROUP BY pickup HAVING COUNT(*)>=1

The result is like this;

 

E1

E10

E11

E12

E13

E2

E3

E4

.......

 

 

I need to display the data starting from E1 ...... to ....E13 (or whatever the last entry is).

 

Could anyone help?

 

Regards

[Barand]

you could store the results in an array then use natsort

$arr = array (
    'E1',
    'E10',
    'E11',
    'E12',
    'E13',
    'E2',
    'E3',
    'E4'
);
natsort($arr);

echo '<pre>',print_r($arr, true),'</pre>';

/* OUTPUT *************************
Array
(
    [0] => E1
    [5] => E2
    [6] => E3
    [7] => E4
    [1] => E10
    [2] => E11
    [3] => E12
    [4] => E13
)
**************************************/

[malkocoglu]

So can I do this with mysql too.

 

Can the following code output the same result?

($pickup is populated from database)

$pickup= array ($pickup);
natsort($pickup);

echo '<pre>',print_r($pickup, true),'</pre>';

[Barand]

Sorry, forget the natsort. If you store the results of the query in an array then you will need a custom sort function using strnatcmp

 

EG

$sql = "SELECT COUNT(*) as ct, pickup 
        FROM journeys 
        WHERE pickup_type='postcode' 
        GROUP BY pickup 
        HAVING COUNT(*)>=1";
$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc()) {
    $data[] = $row;
}


usort($data, function($a,$b){
        return strnatcmp($a['pickup'], $b['pickup']);
        });

echo '<pre>';
foreach ($data as $row) {
    printf ('%-4s %d<br>',  $row['pickup'], $row['ct']);
}
echo '</pre>';

/* OUTPUT EXAMPLE************************

E1   4
E2   2
E10  3
E11  1

**************************************/

[malkocoglu]

Thank you, it worked like a charm.