ORDER BY not working

[Stickie]

I have this query, but the ordering by is not working. It should order by value, but doesn't react. you'll see what I mean in the code.

<table class="pme-main">
<tr>
<th class="pme-header">Bestemming</th>
<th class="pme-header">Naam Klant</th>
<th class="pme-header">Aantal Manden</th>
<th class="pme-header">Aantal Duiven</th>
<th class="pme-header">Naam Bestemmeling</th>
</tr>

<?php

$con =

mysql_connect("****","****","****");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("****", $con);

$query = "SELECT z.Bestemming, b.Name, k.Naam, k.ID, m.Aantal_Duiven,

s.Aantal_Manden FROM Shipments AS s JOIN Zendingen AS z ON s.Zending_ID

= z.ID JOIN Klanten AS k ON s.Klant_ID = k.ID JOIN Manden AS m ON

s.Mand_ID = m.ID JOIN Bestemmeling AS b ON s.Bestemmeling_ID = b.ID

WHERE z.Actief = 1 order by k.Naam, s.Aantal_Manden, m.Aantal_Duiven

ASC";

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo " <td class=\"pme-cell-0\">{$row['Bestemming']}</td>\n";
echo " <td class=\"pme-cell-1\">{$row['Naam']}</td>\n";
echo " <td class=\"pme-cell-0\">{$row['Aantal_Manden']}</td>\n";
echo " <td class=\"pme-cell-1\">{$row['Aantal_Duiven']}</td>\n";
echo " <td class=\"pme-cell-0\">{$row['Name']}</td>\n";
echo "</tr>\n";
}
?>

</table>

and this is what it gives me:

Bestemming Naam Klant Aantal Manden Aantal Duiven Naam Bestemmeling
Peking ---- Andre Guebs (via Mr. Lin) 210 Chong Ping
Peking ---- Andre Guebs (via Mr. Lin) 2 12 Nasi Goreng
Peking ---- Andre Guebs (via Mr. Lin) 2 14 Ping Pong
Peking ---- Andre Guebs (via Mr. Lin) 2 3 Nasi Goreng
Peking ---- Andre Guebs (via Mr. Lin) 5 14 Chong Ping
Peking ---- Andre Guebs (via Mr. Lin) 8 8 Chong Ping

As you can see, the underlined values are the ones he orders, but as far as I know, 3 comes before 10,12 and 14...

What am I missing?

[PsychicTide]

If you order a query using multiple columns the ASC or DESC clause needs to be specified after every column name.

IE:

order by k.Naam ASC, s.Aantal_Manden ASC, m.Aantal_Duiven ASC";

[rch1231]

Hello,

Looking at the order of the output I would be willing to bet that the variable type for Aantal_Duiven is varchar or char and is string based and not Integer of Float which are numerically based. What you are seeing is a sort based on alphanumeric characters instead of numbers. If treated as a character it evaluates each position in turn so that ax comes before ba (a being less than b). Using an alphanumeric sort the 3 in the first position of 3 is greater than the 1 in the first position of 12. Does that help?

[Stickie]

Thanks for posting, whilest searching i stumbled upon an article explaining the CHAR and INT problems when you order ;)
But that solved it indeed, muchos gracias, topic solved!

S.