Help with mysql sum and group query and managing results for jquery graph.
- by Scarface
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 = [[1269417600000, 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,
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 "];";
