$SQLString = "SELECT
count(score) as counts,
score, month,
date FROM persons
GROUP BY day, month, year
ORDER BY date asc";
$result = mysql_query($SQLString);
$num = mysql_num_rows($result);
$data[0] = array('day','counts');
for ($i=1; $i<($num+1); $i++)
{
$data[$i] = array(substr(mysql_result($result, $i-1, "date"), 0, 10),
(int) mysql_result($result, $i-1, "counts"));
}
echo json_encode($data);
This outputs something like this:
[["day","counts"],["2012-01-20",1],["2012-02-06",1],["2012-11-16",2],["2013-04-13",1]]
My problem is that I need that date values (eg. "2012-01-20") in date type instead of string.
I really need your help...
MySQL date values ARE strings. If you stored them as plaintext, they'd be seen as integer subtractions, e.g.
2012-01-02 -> 2012 minus 1 minus 2 -> 2009
Javascript has no native "date" strings. There's only the Date object, which can't be represented in a JSON string. If you want integers, you'll have to store them as unix timestamps, e.g.
2012-01-20 00:00:00 -> 1327039200
However, note that Javascript uses milliseconds for its timestamp values, so you'd need
1327039200000
as the value to pass through.
Note that your code is highly inefficient. mysql_result is painfully slow, and ugly to work with. You could simply things greatly with
$sql = "SELECT COUNT(score) AS counts, unix_timestamp(yourdatefield) AS timestamp ...";
$data = array();
while($row = mysql_fetch_assoc($result)) {
$timestamp = $row['timestamp'];
$data[] = array($row['counts'], $row['timestamp'] * 1000);
}
echo json_encode($data);
then you can convert those timestamps to have JS date objects with
var d = new Date(1327039200000);