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
– gen_Eric, Commented May 21, 2013 at 15:15

Orangepill · Accepted Answer · 2013-05-21 15:15:20Z

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, 2013 at 15:15

Orangepill

24.7k3 gold badges44 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

y45 Over a year ago

Thanks, when I executed the php script, the page result this '[]', are this correct? sorry for my lack of knowledges.

y45 Over a year ago

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'.

chrislondon · Accepted Answer · 2013-05-21 15:20:55Z

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, 2013 at 15:20

chrislondon

12.6k5 gold badges29 silver badges66 bronze badges

6 Comments

y45 Over a year ago

Hi Chris, how to tested this code, I has implemented with heatmap.js but it still not works.

chrislondon Over a year ago

I haven't used heatmap.js before but looking at their code try doing this: heatmap.store.setDataSet(response); inside of the done function.

y45 Over a year ago

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?

chrislondon Over a year ago

Hmm... maybe try this in your php: $data[] = array("lat"=>(float)$row["geo_lat"], "lon"=>(float)$row["geo_long"], "value"=>1)

y45 Over a year ago

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.

|

nl-x · Accepted Answer · 2013-05-29 09:27:31Z

0

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?

answered May 29, 2013 at 9:27

nl-x

11.9k7 gold badges36 silver badges67 bronze badges

Collectives™ on Stack Overflow

Get MySql data and store it into Javascript array

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related