CODEWITHSUNDEEP
phpmysql
Alberto Rossi
Alberto Rossi

Reputation: 1800

MySQL row not being ordered in php page

Here you can see the code I use to display a table from a MySQL database in a PHP webpage:

<?php
$con=mysqli_connect("localhost","aaaaaa","bbbbb","my_mk7vrlist");
$x = 1;

$result = mysqli_query($con,"SELECT * FROM 0_vrs_japan ORDER BY `vrs` DESC, `date` ASC");

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $x . "</td>";
  echo "<td>" . $row['playername'] . "</td>";
  echo "<td><img src='http://mk7vrlist.altervista.org/flags/" . $row['Country'] . ".gif' /></td>";
  echo "<td>" . $row['contactable'] . "</td>";
  echo "<td>" . $row['vrs'] . "</td>";

  if($row['date'] != "-"){
   $formatted = date('jS F Y', strtotime($row['date']));
  } else {
   $formatted = "-";
  }

  if($row['pic'] != "-"){
   echo "<td><a href=\"" . $row['pic'] ."\" target=\"_blank\">" . $formatted . "</a></td>";
  } else {
   echo "<td>" . $formatted . "</td>";
  }

  echo "</tr>";
  $x = $x+1;
  }

mysqli_close($con);
?>

And the result is this:

enter image description here

Every player with 99'999 points (which are called 'vrs') is sorted by the date. I am having troubles with that player I have just added.

His date 2014/05/22 in the database isn't in the correct position (after Megaman). Do you have any idea?

Upvotes: 0

Views: 31

Answers (1)

bcmcfc
bcmcfc

Reputation: 26755

You're storing the date as a varchar so MySQL is having trouble sorting it in the way you need it to, as it's sorting lexicographically with the field being a varchar.

Convert them to proper date fields and natural date sorting will work as intended.

Upvotes: 2

Related Questions