Solved

Sorting by date with php/mysql broke?

Posted on 2004-04-02
1
542 Views
Last Modified: 2012-08-14
I'm currently trying to display a mysql db and order the results by date...  With my current code, the output is put in date order to about 90%, but some of the dates are out of order for some reason.  I've been trying to figure this out for a few days now and I need help! =)  Below is my php source and a link to my php page...

<?php
$db = mysql_connect("localhost","xxxx","xxxxxxxxxxxx") or die("Problem connecting");
mysql_select_db("mikegood_test") or die("Problem selecting database");

// query one
$query = "SELECT date_format(date,'%M %D, %Y') as date, col2, col3 FROM test ORDER BY date ASC";

// query the database for lans in the next two weeks - broke
$query2 = "SELECT date_add('2004-04-01', INTERVAL 31 DAY) as date, col2, col3 FROM test ORDER BY date ASC";

// datesub()
$query3 = "SELECT DATE_SUB('2004-04-15', INTERVAL 31 DAY);
        -> '2004-03-30'";

$result = mysql_query($query) or die ("Query failed");
//get the number of rows in our result to use in the for loop
$numofrows = mysql_num_rows($result);
?>

<?php
echo "<table border=\"1\">\n";
echo "<tr bgcolor=\"lightblue\"><td>date</td><td>col2</td><td>col3</td></tr>\n";
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result); //get a row from the result set
    if($i % 2) { // if there is a remainder
        echo "<tr bgcolor=\"yellow\">\n";
    } else { //if there isn't a remainder
        echo "<tr bgcolor=\"white\">\n";
    }
    echo "<td>".$row['date']."</td><td>".$row['col2']."</td><td>".$row['col3']."</td>\n";
    echo "</tr>\n";
}
//close the table
echo "</table>\n";
?>

<br><br>

<?
$date = date("D j M Y, G:ia");
//$date = date("Y
echo $date;
?>

this page can be viewed live from: http://mikegood.net/displaytest.php
0
Comment
Question by:mgood16
1 Comment
 
LVL 17

Accepted Solution

by:
leannonn earned 125 total points
ID: 10747197
> SELECT date_format(date,'%M %D, %Y') as date, col2, col3 FROM test ORDER BY date ASC

The sorting does not work as expected because you sort the results on the modified/formated field `date`. You might want to try this one:
---
SELECT date_format(date,'%M %D, %Y') as my_date, col2, col3 FROM test ORDER BY date ASC
---

This way you sort it by the original `date` field, not the formatted one.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question