Reputation: 1550

Order by date in mysql

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:

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

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!

Upvotes: 1

Answers (5)

mkamthan

Reputation: 2371

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.

Upvotes: 0

gnarf

Reputation: 106412

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.

Upvotes: 0

Amber

Reputation: 527458

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. :)

Upvotes: 2