MySQL row not being ordered in php page

Question

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?

That'll be the issue. If it was a date field it would be sortable. — bcmcfc
– bcmcfc, Commented May 28, 2014 at 15:44
You'll probably need to write a migration script to ensure it can be converted without loss of data. Perhaps add the date column, then process the table and populate it, then you can drop the varchar column and proceed. — bcmcfc
– bcmcfc, Commented May 28, 2014 at 15:45
if you can't change field type you can use order by STR_TO_DATE('2014/05/30', '%Y/%c/%d') — bitkot
– bitkot, Commented May 28, 2014 at 15:49

bcmcfc · Accepted Answer · 2014-05-29 07:12:29Z

2

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.

edited May 29, 2014 at 7:12

answered May 28, 2014 at 15:45

bcmcfc

27k30 gold badges115 silver badges183 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wyzard Over a year ago

It's not really "having trouble" sorting it; it's just sorting the strings lexically, digit-by-digit, which isn't what you want.

bcmcfc Over a year ago

@wyzard you're right, I should have phrased that differently. Will edit when I can.

Collectives™ on Stack Overflow

MySQL row not being ordered in php page

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related