使用Flot，html，PHP和MySql查询绘制多个图表 [英] Draw multiple charts with Flot, html, PHP and MySql query
问题描述
我试图绘制多个图表与Flot，html，PHP和MySql查询，但我被困住，因为我找不到一个方法，以便在同一个html页面中绘制多个浮点。
在数据库（test_db3）中为了简单起见以下字段：
I’m trying to draw multiple charts with Flot, html, PHP and MySql query but I’m stuck because I can’t find a way in order to draw multiple flots in the same html page. In the database (test_db3) image for simplicity the following fields:
- table1（user_name，mail_sent，time）
- table2（user_name2，mail_received，time2）
这两个表不能修改，我不能将user_name2添加到table1，以此类推。
在表1中存储根据发送时间发送的邮件的值
在表2中存储基于接收时间的接收邮件的值
those two tables cannot be modify, I can’t add the user_name2 to the table1 and so on. in table1 are stored the values of the sent mail based on the time of sent in table2 are stored the values of the received mail based on the time of reception
在此代码之前，我已经测试了存储在DB中的数据与另一个前面写的代码，它只能绘制一个用户的2个图表，但数据和图表正确绘制。
现在我想为DB的所有用户绘制2个图表我被卡住了！
如果有必要我可以发布第一个代码，从单个用户的DB中提取数据。
如果有任何建议...谢谢！
Before this code, I’ve tested the data stored in the DB with another code written previously it could only draw 2 charts of one user, but the data and the charts where correctly draw. Now that I'm trying to draw 2 charts for all the users of the DB I’m stuck! if necessary I can post the first code that extracts the data from the DB for a single user. If anyone has any advice … thanks!
<html>
<script language="javascript" type="text/javascript" src="js/jquery.js"></script>
<script language="javascript" type="text/javascript" src="js/jquery.flot.js"></script>
<?php
/*
connection to the database
*/
$server = "localhost";
$user="xxxxxx";
$password=" xxxxxx ";
$database = "test_db3";
$connection = mysql_connect($server,$user,$password) or die (mysql_error());
$db = mysql_select_db($database,$connection) or die (mysql_error());
//The first Sql query is searching for DISTINCT users in the DB
$data = mysql_query("SELECT DISTINCT user_name FROM table1 JOIN table2 ON user_name=user_name2") or die(mysql_error());
while($info = mysql_fetch_array( $data ))
{
$user = $info['user_name']; //It’s the name of user analyzed at the moment
/*
This query extract the first ten more recents values (order by time DESC)
The data retrieved by the query are used to paint the 1° chart for emails sent by the user
but I don't know how to do it recursively
*/
$query = "SELECT user_name,mail_sent,time FROM table1 WHERE user_name='$user' ORDER BY time DESC LIMIT 0,10";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$row['time']=$row['time']*1000; //The time is in millisecond I need to multiply for 1000 in order to obtain the seconds
//the 'time' row is the x-axis , the 'mail_send' row is the y-axsis
$dataset1[] = array($row['time'],$row['mail_sent']); //It contains the time value and the numbers of email sent from the user
}
/*
This query extract the first ten more recents values (order by time2 DESC)
The data retrieved by the query are used to paint the 2° chart for emails received by the user
but I don't know how to do it recursively
*/
$query2 = "SELECT user_name2,mail_received ,time2 FROM table2 WHERE user_name2='$user' ORDER BY time2 DESC LIMIT 0,10";
$result2 = mysql_query($query2);
while($row2 = mysql_fetch_assoc($result2))
{
$row2['time2']=$row2['time2']*1000; //The time is in millisecond I need to multiply for 1000 in order to obtain the seconds
//the 'time' row is the x-axis , the 'mail_send' row is the y-axsis
$dataset2[] = array($row2['time2'],$row2['mail_received ']); //It contains the time value and the numbers of email received from the user
}
/*
Here I should insert some code in order to draw 2 charts for all the users of the DB, so for example if the DB has 30 Users
i need to draw 60 charts, 2 charts for any users
-> the 1° charts represents the mail sent from the user
-> the 2° charts represents the mail received from the user
*/
}
mysql_close($connection); //Close connection DB
?>
<script type="text/javascript">
$(function () {
// setup plot
var options = {
series: {
lines: { show: true },
points: { show: true }
},
//the value of min:0 and max:100 are just examples of course
yaxis: { min: 0, max: 100 },
xaxis: {
mode: "time",
minTickSize: [1, "minute"],
}
};
var dataset1 = <?php echo json_encode($dataset1); ?>;
var dataset2 = <?php echo json_encode($dataset2); ?>;
//This part is not correct because the palaceholder should have a increment value
//placeholder0, placeholder1, placeholder3, placeholder4, ..., placeholderN
//And it is necessary to place a <div id="placeholderN" style="width:350px;height:200px;"> </div> in the PHP code for every placeholder generated
//or find another solution
var plot1 = $.plot($(placeholder0), [ dataset1, dataset2 ], options); //For the 1° charts
var plot2 = $.plot($(placeholder1), [ dataset1, dataset2 ], options); //Fot the 2° charts
});//End script
</script>
</html>
推荐答案
首先看起来有点奇怪user_name2 ，time2等。用于第二个查询。
First of all it seems a little strange to have user_name2,time2,etc. for the second query. Is that really how it's set up in your DB?
无论如何，这里是一种从你的PHP循环中生成图的方法。
Anyway, here is one way to generate plots from within your PHP loops.
echo('<div id="placeholder"></div>');
echo('<script>');
while($info = mysql_fetch_array( $data ))
{
$user = $info['user_name']; //It’s the name of user analyzed at the moment
/*
This query extract the first ten more recents values (order by time DESC)
The data retrieved by the query are used to paint the 1° chart for emails sent by the user
but I don't know how to do it recursively
*/
$query = "SELECT user_name,mail_sent,mail_received,time FROM table1 WHERE user_name='$user' ORDER BY time DESC LIMIT 0,10";
$result = mysql_query($query);
$dataset1 = new Array();
while($row = mysql_fetch_assoc($result))
{
$row['time']=$row['time']*1000; //The time is in millisecond I need to multiply for 1000 in order to obtain the seconds
//the 'time' row is the x-axis , the 'mail_send' row is the y-axsis
$dataset1[] = array($row['time'],$row['mail_sent']); //It contains the time value and the numbers of email sent from the user
}
echo('$.plot( $(\'<div style="width:600px;height:300px;"></div>\').appendTo(\'#placeholder\'),'.json_encode($dataset1).',options);\n');
}
echo('</script>');
这篇关于使用Flot，html，PHP和MySql查询绘制多个图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
