JSON file import into geochart markers map won't work

[Diane Golay]

Hello,

I have been working on trying to draw a geochart markers map with data from a database for the last few hours and just can't seem to make it work. I hope somebody can give me hand.

Here's what I have so far: I have a main php file "index.php", in which I send a SQL request to my database. I save the results in the array $row, that I use to create the "rows" of the map I want to create.
Here's the code:

$markers = array();
$markersize = 2;
$markercolor = 5;

do {
        $markers[] = "[{v:'".$row['strasse_strassennummer']." ".$row['plz']." ".$row['stadt']."',f:'".$row['stadt']."'},".$markersize.",".$markercolor.",'Verbund: ".$row['verbund']."'],";
    } while ($row = mysql_fetch_assoc($query));

The data for the rows is saved in the array $markers, which is then encoded into JSON and written in a JSON file.

//Json encode
$markers = json_encode($markers);
   
$fp = fopen('jsonkarte.json', 'w');
fwrite($fp, $markers);
fclose($fp);

A php file gets the contents of the JSON file. The last comma of the file is deleted (to correspond to the syntax wanted by geochart):

<?php
$string = file_get_contents("jsonkarte.json");
$anzahlzeichen = strlen($string);
$pos = strrpos($string, ",", -1);
$string = substr_replace($string,'',$pos,1); 
echo $string;
?>

Finally, the data is imported into the js file with AJAX:
function drawMarkersMap() {
   
    var jsonData = $.ajax({
          url: "jsonkarte.php",
          dataType:"json",
          async: false
          }).responseText;
         
    // Create our data table out of JSON data loaded from server.     
    var data = new google.visualization.DataTable();
   
    data.addColumn('string', 'City');
    data.addColumn('number', 'Color');
    data.addColumn('number', 'Size');
    data.addColumn({type:'string', role:'tooltip', p:{html:true}});
   
    data.addRows(JSON.parse(jsonData));  //Found this solution on the Internet...  
};

This solution is not working and, since there are no error messages, I have no idea where I have to look for the errors. I am thinking it could lie in the syntax I use in php to create the array $markers, but I am at a loss about how to change it for the better...

I would be very grateful for your help. Many thanks.

[asgallant]

I suspect you have multiple issues with this code.  First, your do...while loop won't have any values in $row the first time through - typically this is done with a while loop instead.  Second, you are writing to a file and then retrieving from the file, but never using the retrieved data.  Third, your AJAX call attempts to fetch data from jsonkarte.php, but there is no data output, so it gets back a blank result.

There is no need to write your json to a file (unless you need to log it or something) - the AJAX call can take care of everything for you.  You can also take advantage of PHP's json_encode function to handle the work of properly formatting your JSON string.  Here's an example you can try:

[PHP]

$markers = array();

$markersize = 2;

$markercolor = 5;

$table = array(

    'cols' => array(

        array('type' => 'string', 'label' => 'City'),

        array('type' => 'string', 'label' => 'Color'),

        array('type' => 'string', 'label' => 'Size'),

        array('type' => 'string', 'role' => 'tooltip', 'p' => array('html' => true))

    ),

    'rows' => array()

);

while ($row = mysql_fetch_assoc($query)) {

    $table['rows'][] = array('c' => array(

        array('v' => "{$row['strasse_strassennummer']} {$row['plz']} {$row['stadt']}", 'f' => $row['stadt']),

        array('v' => $markersize),

        array('v' => $markercolor),

        array('v' => "Verbund: {$row['verbund']}")

    ));

}

echo json_encode($table, JSON_NUMERIC_CHECK);

[javascript]

function drawMarkersMap() {

    $.ajax({

        url: "jsonkarte.php",

        dataType:"json",

        success: function (json) {

            // Create our data table out of JSON data loaded from server.      

            var data = new google.visualization.DataTable(json);

            

            // create and draw chart (example code since you didn't include any)

            var chart = new google.visualization.GeoChart(document.querySelector('#myChartDiv'));

            chart.draw(data, {

                height: 400,

                width: 600,

                displayMode: 'markers'

                // set other options like region

            });

        }

    });

}

[Diane Golay]

Thank you Asgallant for your explications and the code! I had no idea it was possible to create such complex arrays. Thank you for taking the time to shape it exactly like I needed it. I do have a last question though: with the json and the jsonkarte.php file "gone", how can AJAX get the code? With the "echo" order in the index.php, the json code only gets displayed on the page. Or am I missing something?

[asgallant]

You have two options: either you can skip the AJAX call entirely and output your JSON directly into the DataTable, like this:

function drawMarkersMap() {

    // Create our data table out of JSON data loaded from server.      

    var data = new google.visualization.DataTable(<?php echo json_encode($table, JSON_NUMERIC_CHECK); ?>);

    

    // create and draw chart (example code since you didn't include any)

    var chart = new google.visualization.GeoChart(document.querySelector('#myChartDiv'));

    chart.draw(data, {

        height: 400,

        width: 600,

        displayMode: 'markers'

        // set other options like region

    });

}

or you can move your PHP code for pulling from the database and outputting the JSON to jsonkarte.php and use AJAX to fetch the data.  The first method is simpler, the second is more flexible (if you need to access this data from multiple pages, or you want to be able to refresh your chart data without reloading the page).

[Diane Golay]

Many thanks for your precious help asgallant! :-)