Help with mysql sum and group query and managing results for jquery graph
I have a system I am trying to design that will retrieve information from a database, so that it can be plotted in a jquery graph. I need to retrieve the information and somehow put it in the necessary coordinates format (for example two coordinates var d = [[126941760000开发者_如何学C0, 10],[1269504000000, 15]];
).
My table that I am selecting from is a table that stores user votes with fields:
points_id (1=vote up, 2=vote down),
user_id,
timestamp, (UNIX TIMESTAMP)
topic_id
What I need to do is select all the votes and somehow group them into respective days and then sum the difference between 1 votes and 2 votes for each day. I then need to somehow display the data in the appropriate plotting format shown earlier. For example April 1, 4 votes. The data needs to be separated by commas, except the last plot entry, so I am not sure how to approach that. I showed an example below of the kind of thing I need but it is not correct,
echo "var d=[";
$query=mysql_query(
"SELECT *, SUM(IF(points_id = \"1\", 1,0))-SUM(IF([points_id = \"2\", 1,0)) AS 'total'
FROM points LEFT JOIN topic ON topic.topic_id=points.topic_id
WHERE topic.creator='$user' GROUP by timestamp
HAVING certain time interval"
);
while ($row=mysql_fetch_assoc($query)){
$timestamp=$row['timestamp'];
$votes=$row['total'];
echo "[$timestamp,$vote],";
}
echo "];";
It would certainly be a lot saner to just use -1 as a downvote. Then you can simply run SUM(points_id)
To generate javascript-friendly notation, you can use good old mate json_encode
.
$sql = "SELECT `timestamp`, SUM(points_id) FROM ..."; // yadda yadda
$result = mysql_query($sql);
$out = array();
while ($row = mysql_fetch_assoc($result)) {
$out[] = array((int) $row['timestamp'], (int) $row['total']);
}
echo "d = " . json_encode($out) . ";\n";
精彩评论