Draw multiple charts with Flot, html, PHP and MySql query
1,116 views
Skip to first unread message
M@rco
Feb 25, 2012, 8:53:53 AM2/25/12
to Flot graphs
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)
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
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>
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)
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
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>
DaveCline
Feb 26, 2012, 12:21:21 PM2/26/12
to Flot graphs
First point: separate your presentation from your data/business logic.
Make two pages, one for you html/css/javascript, and one for your php/
sql/json.
If you think about it, having 6 languages in a single document is
undoubtedly overwhelming.
Using this technique you can then request your data through your biz/
data layer page independently of your presentation. This will give you
a returned text blob of json text that you can then check for
correctness. Get your php/sql/json all returning exactly what you want
to use in your presentation layer and then move on to your charting.
You'll have to use ajax to fetch your data, but this will be so much
easier as you build out your web site.
When you do this you should now have a json object with you multiple
set of data
{
"dataPayloadA" : [0:.0, 1:.1, 2:.2, 3:.3, 4:.4],
"dataPayloadB" : [0:1.1, 1:1.1, 2:1.2, 3:.13, 4:1.4]
}
This should resemble the text you get back from your data page query.
Then you just perform two plots using the json.dataPayloadA for one
and the json.dataPayloadB for the other.
You'll have two different place holders for the two different charts.
You can line them up with axis margin widths if you need them stacked
and perfectly tracking (top to bottom).
Much simpler now I'd say.
-DC
Make two pages, one for you html/css/javascript, and one for your php/
sql/json.
If you think about it, having 6 languages in a single document is
undoubtedly overwhelming.
Using this technique you can then request your data through your biz/
data layer page independently of your presentation. This will give you
a returned text blob of json text that you can then check for
correctness. Get your php/sql/json all returning exactly what you want
to use in your presentation layer and then move on to your charting.
You'll have to use ajax to fetch your data, but this will be so much
easier as you build out your web site.
When you do this you should now have a json object with you multiple
set of data
{
"dataPayloadA" : [0:.0, 1:.1, 2:.2, 3:.3, 4:.4],
"dataPayloadB" : [0:1.1, 1:1.1, 2:1.2, 3:.13, 4:1.4]
}
This should resemble the text you get back from your data page query.
Then you just perform two plots using the json.dataPayloadA for one
and the json.dataPayloadB for the other.
You'll have two different place holders for the two different charts.
You can line them up with axis margin widths if you need them stacked
and perfectly tracking (top to bottom).
Much simpler now I'd say.
-DC
Ced
Feb 26, 2012, 6:33:16 PM2/26/12
to Flot graphs
You can use jQuery to add divs to your page.
in html:
<div id="placeholders"></div>
in your javascript, where i is the plot number:
$('#placeholders').append('<div id="placeholder' + i + '" style="width:
350px;height:200px;"></div>');
in html:
<div id="placeholders"></div>
in your javascript, where i is the plot number:
$('#placeholders').append('<div id="placeholder' + i + '" style="width:
350px;height:200px;"></div>');
Reply all
Reply to author
Forward
0 new messages