Order by date in mysql

Question

I want to sort my result form mysql by date. I use the query like this:

<?php
$date = $db->get_query("select distinct created_date from comments");
$condate = '';
for($i=0; $i < count($date); $i++)
{
  $condate = $date[$i]['created_date'];
  $data = $db->get_query("select id,created_date from comments where created_date='$condate' order by created_date");
  ?>
  <table border='1' style="float: left; margin-left: 5px;">
    <?php
    for($j=0; $j<count($data); $j++)
    {
      echo '<tr><td>';
      echo $data[$j]['id'] ;
      echo '</td><td>';
      echo $data[$j]['created_date'];
      echo '</td></tr>';
    }
    ?>
  </table>
<?php
}
?>

This query produce result like this:

2009-07-10 
2009-07-10
2009-08-21
2009-07-29
2009-08-15

The result is not sorted.
I want to see the result is:

2009-07-10
2009-07-10
2009-07-29
2009-08-15
2009-08-29

with separated table order by created-date.
I want to know sorting date in mysql result .In this case $condate is variable for validate condition.The value of $condate is all created_date in comments table. I produce this as within loop and set the value is.
Please help me!

Answer 1

If you're only selecting results from a single date, then there's nothing to sort by. What exactly is the WHERE condition doing?

Edit: Now that you've posted your code, I can offer a suggestion. Your original code is running a separate query for each different date. What you really want is a single query that returns the results for all dates, but in a specific order, which is what the query in the code below does. Try this instead:

<?php     

    $data = $db->get_query("select id,created_date from comments order by created_date");
?>
<table border='1' style="float: left; margin-left: 5px;">
   <?php


        for($j=0; $j<count($data); $j++)
        {

             echo '<tr><td>';
             echo $data[$j]['id'] ;
             echo '</td><td>';
             echo $data[$j]['created_date'];
             echo '</td></tr>';
        }

   ?>
</table>

Note that you already had all of this in your original code! You just managed to convince yourself that the task was more complicated than it actually was. :)

Answer 2

Because you put = in the where clause, so all the records will be in the same day and the sort will not be useful.

Edit

Are you using date field type for created_date field or string? if it's string so that may cause your problem...

Answer 3

Maybe you have your date field is stored as a "string" and not something like datetime?

If you check with:

describe table_name;

How is your date stored?

Answer 4

You are looping through results that aren't ordered to generate the dates you are asking for in the second query. Up near the top you were querying:

$date = $db->get_query("select distinct created_date from comments");
// should be (and excuse the keyword capitalization, I just think its easier to read)
$date = $db->get_query("SELECT DISTINCT created_date FROM comments ORDER BY created_date");

You just put your ORDER BY clause on the wrong query.

Answer 5

I dont think you need to execute 2 queries. Try executing $data = $db->get_query("select DISTINCT id,created_date from comments where created_date='$condate' order by created_date"); I think you had all built-in in your code but you just got confused a little.

Do let me know whether this solved your problem or not.