[RESOLVED] MySQL Pulling in Dates in the wrong order

[whobutsb]

Hi All,

I have this really weird problem with a MySQL table. I'm trying to create a line graph with the PHP/SWF charts script. I'm pulling in my data from a table called ELECTRICITY. In that table I have the following columns: id, access_id, bizunit, date, expense. (note: access_id is the id of the business unit that is getting referenced on another table, also the php script sorts the results using that id.) Everything is a varchar except the first id column which a UNIQUE KEY and is an int. I then run this script to get the data for the php/swf chart script:

//start the PHP multi-dimensional array and create the region titles
$chart [ 'chart_data' ][ 0 ][ 0 ] = "";
$chart [ 'chart_data' ][ 1 ][ 0 ] = "NHC";
$chart [ 'chart_data' ][ 2 ][ 0 ] = "CHI";
$chart [ 'chart_data' ][ 3 ][ 0 ] = "MOO";
$chart [ 'chart_data' ][ 4 ][ 0 ] = "BSC";
$chart [ 'chart_data' ][ 5 ][ 0 ] = "SMO";
$chart [ 'chart_data' ][ 6 ][ 0 ] = "BH";
$chart [ 'chart_data' ][ 7 ][ 0 ] = "22B";
$chart [ 'chart_data' ][ 8 ][ 0 ] = "WAT";

//connect to the database
require_once('connect.php');

//get the smallest year to determine which month to start the chart with
$result = mysql_query ( "SELECT MIN(date) AS MinMonth FROM ELECTRICITY" );
$MinMonth = mysql_result ( $result, 0, "MinMonth" );

//get all the data in the ELECTRICITY table
$result = mysql_query ("SELECT * FROM ELECTRICITY");

//extract the data from the query result one row at a time
for ( $i=0; $i < mysql_num_rows($result); $i++ ) {

   //determine which row in the PHP array the current data belongs to
   switch ( mysql_result ( $result, $i, "access_id" ) ) {
      case "1":
      $row = 1;
      break;

  case "2":
  $row = 2;
  break;

  case "3":
  $row = 3;
  break;

  case "5";
  $row = 4;
  break;

  case "7";
  $row = 5;
  break;

  case "9";
  $row = 6;
  break;

  case "10";
  $row = 7;
  break;

  case "12";
  $row = 8;
  break;
   }

  //determine which column in the PHP array the current data belongs to
   $col = mysql_result ( $result, $i, "date") - $MinMonth + 1;

   //populate the PHP array with the Month title
   $chart [ 'chart_data' ][ 0 ][ $col ] = mysql_result ( $result, $i, "date");

   //populate the PHP array with the revenue data
   $chart [ 'chart_data' ][ $row ][ $col ] = mysql_result ( $result, $i, "expense");
}

The results can be viewed here:
http://www.newportharbor.com/green/green.php

As you can see the date starts at 04/01/2007. I can't seem to think why it would be sorting them like this. If I enter the first SQL query to find the MIN date it sends back the correct date of 01/01/2007. Its really strange and I have been trying all sorts of solutions like changing the date column to a date format and putting the dates in different formats like: YYYY-MM-DD, MM/YYYY, etc. Thanks for the help!

[Piranha]

When ordering a varchar (or char) mysql first orders after the first character, then after the second, then the third and so on. So 01-01-2007 is smaller than 01-05-2006. That is all I'm gonna say about ordering a varchar.

You should use a column with the type date. There really is no reason not to use that column type. Then you just do like this:

$sql = "SELECT * FROM ELECTRICITY order by `date` asc"; 
$result = mysql_query ($sql); 

Note that you might want to use desc instead of asc, I didn't understand what way you wanted to order the dates.

[whobutsb]

Thanks alot that works great. I thought the solution would be more complicated than what you suggested. Thank you.

[whobutsb]

I actually just changed around the column format to date and now the script will bring only December 2007 up and nothing else. All I changed in the script was add the ORDER BY 'date' ASC.

Here are the results i'm gettting:
http://www.newportharbor.com/green/green.php