0

I am trying to display a graph using Highcharts to display one column of data from our db. In our db the date format is yyyy-mm-dd, but Highcharts requires dd-mm-yyyy. I want to be able to wite php code to download the data to a .csv that will display it in the correct data format for Highcharts. I have done some looking around and I found that I can format the date from the db, but I must not be implementing the DATE_FORMAT correctly. Be kind, I am a noob to php and Mysql!

Original

$result = mysql_query("SELECT date, data 
                       FROM mytablename 
                       ORDER BY date ") or die(mysql_error());

Failed date format

$result = mysql_query("SELECT DATE_FORMAT(date,'%m-%d-%Y'), data 
                       FROM mytablename 
                       ORDER BY date ") or die(mysql_error());
Jakub
  • 20,418
  • 8
  • 65
  • 92
James Millhuff
  • 1
  • 1
  • Google: PHP [date()](http://php.net/manual/en/function.date.php) –  Jan 05 '12 at 15:45
  • See this [link](http://stackoverflow.com/questions/2487921/php-convert-date-format-yyyy-mm-dd-dd-mm-yyyy-not-in-sql). –  Jan 05 '12 at 15:47
  • Your SQL appears to be correct. There must be more going on... – jjs9534 Jan 05 '12 at 15:48
  • 2
    Highcharts requires dd-mm-yyyy, but you're converting the date to mm-dd-yyyy? – JJJ Jan 05 '12 at 15:54
  • 1
    Highcharts works best with javascript Date numbers containing the number of milliseconds since Jan 1, 1970 (no parsing needed on the client). See https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Date – eolsson Jan 05 '12 at 17:16

1 Answers1

1

Try this:

$result = mysql_query("SELECT DATE_FORMAT(date,'%d-%m-%Y') as `date`, data 
                   FROM mytablename 
                   ORDER BY date ") or die(mysql_error());

You're formatting your date incorrectly as mm-dd-yyyy, and you're not aliasing your column as date.

Eric
  • 92,005
  • 12
  • 114
  • 115
  • That was a typo. I meant to put "DATE_FORMAT(date,'%d-%m-%Y')". I put a while loop to make an array and echo it for the date to see if I am getting the correct format. My problem is that when I use my original code I see the dates in the format used by my DB. When I use DATE_FORMAT as in my second example and with Eric's answer, nothing is displayed. – James Millhuff Jan 05 '12 at 16:44
  • @JamesMillhuff - You're going to have to supply more code then. What happens once you get the result back? – Eric Jan 05 '12 at 16:46
  • Hydrogen Data "; $i++; } ?> – James Millhuff Jan 05 '12 at 17:02
  • You're not aliasing your column. I've changed my query--try that. – Eric Jan 05 '12 at 17:07