Using Php And Mysql To Plot A Graph Axis

[wright67uk]

Im plotting a chart, and everything is working fine apart from the x-axis in the below code.

The x-axis is pulling dates from a MySql db, but displaying them incorrectly

 

eg. 03/12/12 now reads as 0.0001245447361.... etc.....

 

The column that displays is set to varchar(255) Collation latin1_sweedish_ci

 

So i guess my question is, i this a php or a MySql issue?

And secondly if its a MySql issue, is there a php workaround?

 

Many thanks.

 

xAxis: {

categories: [<?php
$sql= "SELECT `sdate` FROM `snag` WHERE `user_id` = $user_id";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['sdate'].',';}
?>]

 

taken from...

 

<?php


CONNECTION BLOCK HERE ###


$connect_solning = mysql_connect($hostname_connect, $username_connect, $password_connect) or trigger_error(mysql_error(),E_USER_ERROR);


@mysql_select_db($database_connect) or die (mysql_error());


$tbl_name = "snag";
$user_id = "7";
?>


<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
var chart;
$(document).ready(function() {
chart = new Highcharts.Chart({
chart: {
renderTo: 'container',
type: 'line',
marginRight: 130,
marginBottom: 25
},
title: {
text: 'Ranges by Club',
x: -20 //center
},
subtitle: {
text: 'Ranges',
x: -20
},
xAxis: {

categories: [<?php
$sql= "SELECT `sdate` FROM `snag` WHERE `user_id` = $user_id";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['sdate'].',';}
?>]


},
yAxis: {
title: {
text: 'Score'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
formatter: function() {
return '<b>'+ this.series.name +'</b><br/>'+
this.x +': '+ this.y +'';
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'top',
x: -10,
y: 100,
borderWidth: 0
},
series: [{
name: 'Driver',
data:
[<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = 'driver'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]

}, {
name: '3 Wood',
data:
[<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '3 wood'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]

}, {
name: '5 Wood',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '5 wood'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '5 wood' AND user_id = 1 */
}, {
name: 'Hybrid',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = 'Hybrid'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = 'Hybrid' AND user_id = 1 */
 }, {
name: '3 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '3 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '3 iron' AND user_id = 1 */
 }, {
name: '4 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '4 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '4 iron' AND user_id = 1 */
 }, {
name: '5 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '5 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '5 iron' AND user_id = 1 */
 }, {
name: '6 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '6 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '6 iron' AND user_id = 1 */
 }, {
name: '7 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '7 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '7 iron' AND user_id = 1 */
 }, {
name: '8 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '8 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '8 iron' AND user_id = 1 */
 }, {
name: '9 iron',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = '9 iron'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = '9 iron' AND user_id = 1 */
 }, {
name: 'P Wedge',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = 'P Wedge'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = 'P wedge' AND user_id = 1 */
 }, {
name: 'S Wedge',
data: [<?php
$sql= "SELECT `yard` FROM `snag` WHERE `user_id` = $user_id AND `club` = 'S Wedge'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){ echo $row['yard'].',';}
?>]
/* SELECT yard FROM `snag` WHERE club = 'S Wedge' AND user_id = 1 */
}]
});
});

});
</script>
</head>
<body>
<script src="../../js/highcharts.js"></script>
<script src="../../js/modules/exporting.js"></script>


<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto">


</div>


</body>
</html>

[Barand]

It's a database table design issue. Use the DATE or DATETIME types for dates, not varchar, and store in yyyy-mm-dd format.

 

You can mysql STR_TO_DATE function in your query to reformat the date