current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'll start from the question: I have a php file which queries a MySql DB, the result I want to put in an array of javascript so I could plot it (using jQuery's flot). Does anybody have any idea how to do that?

What I've done so far which works:

  1. I have a very nice jQuery-flot code which plots a chart. please notice, the most important variable for this question in this code is "data1".
  2. AJAX (with php file) which prints the data from a mysql database

I want to put in the "data1" variable (in the jquery-flot code), the data from the database (Time and Data) but I don't find a way to "connect" the php file result with the data1 variable.

The jQuery-flot chart plotting code (please notice 'data1' variable):

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html>
<head>
    <title>Flot Example</title>
    <script language="javascript" type="text/javascript" src="js/flot/jquery.js"></script>
    <script language="javascript" type="text/javascript" src="js/flot/jquery.flot.js"></script>
    <script language="javascript" type="text/javascript" src="js/flot/jquery.flot.selection.js"></script>
</head>
<body>
    <h1>Flot Examples</h1>s
    <div id="placeholder" style="width:600px;height:300px"></div>
    <div id="overview" style="width:160px;height:100px"></div>
    <script type="text/javascript">
     /*Show Tooltip*/
     function showTooltip(x, y, contents) {
     $('<div id="tooltip">' + contents + '</div>').css({
         position: 'absolute',
         display: 'none',
         top: y + 5,
         left: x + 5,
         border: '1px solid #fdd',
         padding: '2px',
         'background-color': '#fee',
         opacity: 0.80
     }).appendTo("body").fadeIn(200);
 }

 //End of Tooltip    
 var data, data1, options, optionsOverview, chart, overview;
 var data2 = [],
 data3 = [];
 data1 = [
     [1, 4],
     [2, 5],
     [3, 6],
     [4, 9],
     [5, 7],
     [6, 6],
     [7, 2],
     [8, 1],
     [9, 3]
 ];

 for (var i = 1; i < 10; i++) {
     data2.push([i, i * 2])
 }

 for (var i = 1; i < 10; i++) {
     data3.push([i, 10 * Math.random()])
 }

 data = [{
     data: data1,
     label: "fixed",
     lines: {
         show: true
     }
 }, {
     data: data2,
     label: "linear",
     lines: {
         show: true
     },
     points: {
         show: true
     }
 }, {
     data: data3,
     label: "random",
     bars: {
         show: true,
         barWidth: 0.5
     }
 }];

 options = {
     legend: {
         position: "nw"
     },
     grid: {
         clickable: true,
         hoverable: true
     }
 };

 //SELECTION 
 optionsOverview = {
     legend: {
         show: false
     },
     selection: {
         mode: "xy"
     }
 };

 $(document).ready(function () {
     chart = $.plot($("#placeholder"), data, options);
     //SELECTION 
     overview = $.plot($("#overview"), data, optionsOverview);
 });

 /*SELECTION*/
 $("#overview").bind("plotselected", function (event, ranges) {
     chart.setSelection(ranges);
 });

 $("#placeholder").bind("plotselected", function (event, ranges) {
 if (ranges.xaxis.to - ranges.xaxis.from < 0.00001) {
     ranges.xaxis.to = ranges.xaxis.from + 0.00001;
 }
 if (ranges.yaxis.to - ranges.yaxis.from < 0.00001) {
     ranges.yaxis.to = ranges.yaxis.from + 0.00001;
 }
 plot = $.plot("#placeholder", data,
     $.extend(true, {}, options, {
         xaxis: {
             min: ranges.xaxis.from,
             max: ranges.xaxis.to
         },
         yaxis: {
             min: ranges.yaxis.from,
             max: ranges.yaxis.to
         }
     })
 );
 overview.setSelection(ranges, true);

 });
 //End of Selection
 /*Show Tooltip*/

 $("#placeholder").bind("plothover", function (event, pos, item) {
     $("#tooltip").remove();
     if (item) {
         var x = item.datapoint[0].toFixed(2),
         y = item.datapoint[1].toFixed(2);
        showTooltip(item.pageX, item.pageY, item.series.label + " of " + x + " - " + y);
     }
 });
//End of Tooltip
</script>
    <br /><br /><br />

</body>
</html>

on the other hand I have a MySQL DB:

mysql> select * from example5;
+------+------+
| time | data |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    3 |
|    3 |    6 |
|    4 |    9 |
|    5 |   10 |
|    6 |   15 |
|    7 |   20 |
+------+------+
8 rows in set (0.00 sec)

which I access with a ajax/php file: index.html:

<html>
<head>
<script>
function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    }
    if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp = new XMLHttpRequest();
    } else { // code for IE6, IE5
        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange = function () {
        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
            document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
        }
    }
    xmlhttp.open("GET", "getdata.php?q=" + str, true);
    xmlhttp.send();
}
</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select Time:</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="4">5</option>
<option value="4">6</option>
<option value="4">7</option>
</select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here.</b></div>

</body>
</html>

and getdata.php:

<?php
$q=$_GET["q"];


$con = mysqli_connect('localhost','root','12345678','test');
if (!$con)
  {
  die('Could not connect: ' . mysqli_error($con));
  }

mysqli_select_db($con,"test");
$sql="SELECT * FROM example5 WHERE time = '".$q."'";

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>Time</th>
<th>Data</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['time'] . "</td>";
  echo "<td>" . $row['data'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

Thanks for your help, Noam

share|improve this question
 
it would be easier to use JSON instead of HTML/XML for the AJAX answer. Just echo your PHP $rows as a JSON string, and with JavaScript decode that string into an array again. Just two lines of code. An example: caveofprogramming.com/php/… –  TheBronx Aug 12 '13 at 8:56
add comment

2 Answers

up vote 3 down vote

An easy way to communicate between PHP and Javascript is JSON. PHP can easily encode / decode arrays in JSON, and JS can do the same with objects. You might try the following:

In your PHP script, return a JSON Array with your data:

<?php
    $q=$_GET["q"];
    $con = mysqli_connect('localhost','root','12345678','test');
    if (!$con)
    {
        die('Could not connect: ' . mysqli_error($con));
    }

    mysqli_select_db($con,"test");
    $sql="SELECT * FROM example5 WHERE time = '".$q."'";

    $result = mysqli_query($con,$sql);

    $returnArray = array();
    while($row = mysqli_fetch_array($result))
    {
         $returnArray[] = array($row['time'], $row['data']);
    }
    echo json_encode($returnArray);
    mysqli_close($con);
?>

In your Javascript, create an AJAX call like this (I'm using JQuery):

function retrieveResults(str){
jQuery.ajax({
        type: 'GET',
        url: "getdata.php",
        data: {
            q: str,
        }, 
        success: function(data, textStatus, jqXHR) {
            var json = $.parseJSON(data);
                $.each(json, function(index, value) {
                    data1.push(value); // Not sure if it is correct, you might need to do some changes, but this is for the global idea 
                }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            console.log('An error occurred:'+errorThrown);
        }
    });
}
share|improve this answer
 
doesn't "$result = array();" initialize $result? –  N E Aug 12 '13 at 18:03
 
Yup, just saw it now, I didn't pay attention to the variable name. My mistake, sorry. –  noKid Aug 12 '13 at 18:05
 
and another question, does 'data1' supposed to be a global variable? otherwise, how am I suppose to use it on the javascript section in the <body>? –  N E Aug 12 '13 at 18:15
 
In the sample I wrote, Yes, I assume data1 is global. Or at least in the same scope as the function –  noKid Aug 13 '13 at 0:01
 
I defined at the beginning of the script "var data1;" after the "json = $.parseJSON(data);" i type "data1 = json" and put the rest of the 'success' with comments. when I try to document.write() it in the javascript in the body "document.write(data1);" it is printed as 'undefined'. Is there a different way that I'm not aware of to define a global variable? –  N E Aug 13 '13 at 7:26
add comment
up vote 0 down vote

By looking at it are you trying to output this piece of javascript from your database? data1 = [ [1,4],[2,5],[3,6],[4,9],[5,7],[6,6],[7,2],[8,1],[9,3] ];

you could echo out the javascript line as long as your code is in a .php page.

<?php echo 'data1 = ['.while($row = mysqli_fetch_array($result)){echo '['$row['time'].",".$row['data'].'],';}.'];'

This would be after you have connected to the database similar to the getdata.php

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.