Get MySql data and store it into Javascript array

Question

want to retrieve a records data from Mysql and store it into Javascript array for heatmap.js map data in this format :

var testData = {max: 46, data: [{lat: 33.5363, lon:-117.044, value: 1},{lat: 33.5608, lon:-117.24, value: 1},..]};

Now I get stuck at here, and I don't know how to connecting from Jquery into my var testData = new Array();, How I should do to solve this?

(UPDATED CORRECT CODE)

get_query.php

<?php
require_once('./db_con.php');
$dbcon=new db;


$query="SELECT (SELECT geo_lat FROM fun WHERE created_at <= DATE_SUB(NOW(), interval 1   minute) AS geo_lat," . 
       "(SELECT geo_long FROM fun WHERE created_at <= DATE_SUB(NOW(), interval 1 minute) AS geo_long";

$result = mysqli_query($dbcon,$query);
$data = array(); 

    while($row= mysqli_fetch_assoc($result)){

     $data[] = array("lat"=>$row["geo_lat"], "lon"=>$row["geo_long"], "value"=>1); 
     $post_data = json_encode(array('max' => 46, 'data' => $data));
    }
    echo $post_data;
 ?>

my_data.js based from here:

jQuery.extend({
getValues: function(url) {
    var result = null;
    $.ajax({
        url: url,
        type: 'get',
        dataType: 'json',
        async: false,
        success: function(data) {
            result = JSON.stringify(data);
        }
    });
   return result;
}
});

var testData = $.getValues("get_query.php");

Thanks to Orangepill and Chrislondon.

`json_encode` should only be called once, at the very end, once the array is built the way you want it. — gen_Eric, May 21 '13 at 15:15

score 3 · Answer 1 · answered May 21 '13 at 15:15

3

while($row= mysqli_fetch_assoc($dbcon,$result)){
    $data[] = array("lat"=>$row["geo_lat"], "lon"=>$row["geo_long"], "value"=>1);
}
echo json_encode($data);

should get you what you are looking for. when building data for json_encode just use native php types instead of json, let json_encode take care of that

answered May 21 '13 at 15:15

Orangepill

24,500
3
42
63

1

Thanks, when I executed the php script, the page result this '[]', are this correct? sorry for my lack of knowledges. – y45 May 21 '13 at 18:10
When I added 'echo $data;' at the end of code, I got 'PHP Warning: mysqli_query() expects parameter 1' and 'PHP Warning: mysqli_fetch_assoc() expects exactly 1 parameter'. – y45 May 21 '13 at 18:28

score 2 · Accepted Answer · answered May 21 '13 at 15:20

2

So your question is how to connect your jQuery to your var data so I won't get into the myriad of problems in your PHP code. In your success function you can set the var data like so:

var data = new Array();

$(function() {
    $.ajax({
        type:     "post",
        url:      "get_query.php",
        data:     $(this).serialize(),
        dataType: "json"
}).done(function(response) {
    data = response;
});

answered May 21 '13 at 15:20

chrislondon

12,487
5
26
65

Hi Chris, how to tested this code, I has implemented with heatmap.js but it still not works. – y45 May 21 '13 at 20:30
I haven't used heatmap.js before but looking at their code try doing this: heatmap.store.setDataSet(response); inside of the done function. – chrislondon May 22 '13 at 13:56
it seems the code not work because the var data resulting double quotes in json data {"lat":"-6.92015","lon":"107.67024","value":1}. how I can remove that? – y45 May 22 '13 at 17:02
Hmm... maybe try this in your php: $data[] = array("lat"=>(float)$row["geo_lat"], "lon"=>(float)$row["geo_long"], "value"=>1) – chrislondon May 22 '13 at 17:35
Thanks, it work, but my json still don't store in javascript variable, it is need to be parse? I really confuse working with jquery-AJAX, I dont receive any data and error. – y45 May 28 '13 at 11:55
When you have `dataType: "json"` in your ajax parameters it automatically parses the response as JSON. If the response isn't being parsed then that probably means your ajax file is returning bad JSON. If you're using Chrome you can open up the Network tab and view the response coming back from the Ajax file. Try pasting it into a JSON validator like: http://jsonlint.com/ and see if that helps – chrislondon May 28 '13 at 12:25

score 0 · Answer 3 · answered May 29 '13 at 09:27

Your json seems incorrect:

var testData = {max: 46, data: [{lat: 33.5363, lon:-117.044, value: 1},{lat: 33.5608, lon:-117.24, value: 1},..]};

should be with quotes around the keys

var testData = {"max": 46, "data": [{"lat": 33.5363, "lon":-117.044, "value": 1},{"lat": 33.5608, "lon":-117.24, "value": 1},..]};

Since you use json_encode() in php I would assume PHP outputs this correctly. But maybe you are for now just working with a fixed (faulty) string for testing purposes?

Get MySql data and store it into Javascript array

3 Answers3

Linked