0

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) 2 10 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?

Edited by Stickie: forgot the underlining

3
Contributors
3
Replies
5
Views
5 Years
Discussion Span
Last Post by Stickie
0

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";

Edited by PsychicTide: n/a

0

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<blank> is greater than the 1 in the first position of 12. Does that help?

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.