Retrieve data from mysql by php to create flot graph

Question

Hi i am trying to retrieve data from mysql database to create flot graph can anyone walk me through this procedure or give me an idea of what to do thanks

At what point exactly do you need help? I assume you know basically how to extract the data from the database? — JorenB, Aug 21 '09 at 14:36
ya i know how to extract the data but how to create the flot — Sarah, Aug 21 '09 at 14:45

score 22 · Accepted Answer · answered Aug 21 '09 at 14:54

22

You probably want something like this. I haven't used flot but I looked at the example here.

<?php
//create array of pairs of x and y values
$dataset1 = array();
while ($row = mysql_fetch_assoc()) { //or whatever
    $dataset1[] = array( $row['xvalue'], $row['yvalue'] );
}
?>

<script type="text/javascript">
    //put array into javascript variable
    var dataset1 = <?php echo json_encode($dataset1); ?>;

    //plot
    $(function () {
         $.plot($("#placeholder"), [ dataset1 ]);
    });
</script>

answered Aug 21 '09 at 14:54

Tom Haigh

57,217
21
114
142

Hi i did as you said but it isn't working there is an exception $dataset1 = array(); while ($row = mysql_fetch_assoc($sql)) { $dataset1[] = array( $row['msgCount'], $row['Group_ID'] ); } echo json_encode($dataset1); jquery function plotGraph() { alert("In body"); $.ajax({ url:"getData.php", type:"post", datatype:"json", success:function(data) {alert(data); $.plot($("#placeholder"), [ data ]); }, error:function() {alert("There was a problem");} }) } is there something missing – Sarah Aug 25 '09 at 07:44
Hey Tom This example was really helpful but now i am facing a problem of handling the null because it is not plotted – Sarah Aug 27 '09 at 09:08
@Sarah: I suppose you could convert them to zero - either in the query e,g IFNULL( colName, 0 ) or in your PHP loop by casting each value to an integer. – Tom Haigh Aug 27 '09 at 09:48
Thanks for this, was having trouble getting the json in from php to flot and this worked perfectly – going Dec 04 '09 at 09:35

score 3 · Answer 2 · edited May 23 '17 at 12:31

Adding upon the example from @Tom Haigh:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Flot Examples</title>
    <link href="layout.css" rel="stylesheet" type="text/css">
    <!--[if lte IE 8]><script language="javascript" type="text/javascript" src="../excanvas.min.js"></script><![endif]-->
    <script language="javascript" type="text/javascript" src="../jquery.js"></script>
    <script language="javascript" type="text/javascript" src="../jquery.flot.js"></script>
 </head>
    <body>
    <h1>Flot Examples</h1>

    <div id="placeholder" style="width:600px;height:300px;"></div>

<?php

$server = "localhost";
    $user="user";
    $password="password";  
    $database = "some_database";

    $connection = mysql_connect($server,$user,$password);
    $db = mysql_select_db($database,$connection);

query = "SELECT x_axis_values, y_axis_values FROM some_table";
    $result = mysql_query($query);        

    while($row = mysql_fetch_assoc($result))
    {
        $dataset1[] = array($row['x_axis_value'],$row['y_axis_value']);
    }

?>


<script type="text/javascript">
$(function () {
    var dataset1 = <?php echo json_encode($dataset1); ?>;

    $.plot($("#placeholder"), [ dataset1 ]);
});
</script>

 </body>
</html>

Came across this. Just trying to get it to work. I think you are missing a `$` from in front of `query`. Also the variable `$db` is only referenced once. Is that right? The above won't propagate anyway. Would love some help:) — square_eyes, Oct 18 '13 at 17:21

score 2 · Answer 3 · answered Jul 26 '12 at 15:41

as @Tom Haigh say work well, but you need to add another code to work well, I was using the example, but I discover in the source code it add to the result quotes " so to avoid this just add the: intval to the array, example:

<?php
$query = "SELECT valx, valy FROM chart";
    $result = mysql_query($query);        

    while($row = mysql_fetch_assoc($result))
    {
        $d2[] = array (intval($row['valx']),intval($row['valy']));
    }
?>

score 0 · Answer 4 · answered Aug 21 '09 at 14:48

0

This depends largely on your environment and requirements. There's lots of free tools out there you can use. One example is Flot that lets you use jQuery to build graphs. There's link to documentation on the Google Code page.

answered Aug 21 '09 at 14:48

Steven Surowiec

10,030
5
32
37

Retrieve data from mysql by php to create flot graph

4 Answers4

Linked