Deliver rows to JavaScript using JSON

Question

I'm trying to get the rows of my mysql table into Arrays, but I haven't found out how to do it correctly up till now. I would appreciate it if someone could give me a hint. It's probably very, very wrong the way I'm doing it, but I haven't found much information regarding JSON and my problem up till now.

    function updateMap(){
        var latlngArray = new Array();
        var titleArray = new Array();
        var ogvArray = new Array();
        var mp4Array = new Array();
        var webmArray = new Array();

        $.getJSON('getPOI.php', function(data){
            for(var i=0; data.length; i++){
                latlngArray[i] = data[i][0]; 
                alert(latlngArray[i]);
                titleArray[i] = data[i][1];
                ogvArray[i] = data[i][2];
                mp4Array[i] = data[i][3];
                webmArray[i] = data[i][4];
            }
        });
    }

The phpfile:

        $con = mysql_connect($server,$benutzername,$passwort) or 
        die ("Keine Verbindung moeglich");

        mysql_select_db($datenbank, $con) or
        die ("Die Datenbank existiert nicht");

        $res = mysql_query("select * from POI");
        echo json_encode($res);
        mysql_close($con);

UPDATE: It still doesn't work properly. The alert function only displays "0: [object Object], 1: [object Object], ..." I guess I didnt access the data properly:

jsontest.html:
<html>
    <head>
        <script src="jquery-1.6.1.js"></script>
        <script type="text/javascript">
            function updateMap(){           
                $.getJSON('getPOI.php', function(data) {
                        $.each(data, function(key, value){
                            alert(key + ': ' + value);
                        });
                    }
                );
            }   
        </script>
    </head>
    <body onload="updateMap()">
    </body>
</html>

getPOI.php:

<?php
    $server = "localhost";
    $benutzername = "user";
    $passwort = "pw";   

    $datenbank = "d0116c39";

    $con = mysql_connect($server,$benutzername,$passwort) or 
    die ("Keine Verbindung moeglich");

    mysql_select_db($datenbank, $con) or
    die ("Die Datenbank existiert nicht");

    $allResults = array();

    $res = mysql_query("select * from POI");

    while ($row = mysql_fetch_assoc($res)) {
        $allResults[] = $row;
    }

    echo json_encode($allResults);
?>

Finally got it:

<html>
    <head>
        <script src="jquery-1.6.1.js"></script>
        <script type="text/javascript">
            function updateMap(){           
                $.getJSON('getPOI.php', function(data) {
                        $.each(data, function(i, item){
                            alert(data[i]['Adresse']);
                            alert(data[i]['Titel']);
                        });
                    }
                );
            }   
        </script>
    </head>
    <body onload="updateMap()">
    </body>
</html>

Pascal MARTIN · Accepted Answer · 2011-07-20 22:13:13Z

1

Calling this :

$res = mysql_query("select * from POI");

will execute the query, but not get you the data : you must fetch it.

This can be done with functions such as mysql_fetch_assoc() (which has to be called once per row the query has selected -- that's done in a loop, typically)

Each time you fetch a row, add it to an array.

And, when you're done fetching the rows and building up that array, json_encode() it, and echo the resulting string to the client.

You'll end up with a portion of code that could look a bit like this :

$allResults = array();

// Execute the query
$res = mysql_query("select * from POI");

// As long as you get rows, fetch them
while ($row = mysql_fetch_assoc($result)) {
    // And add them to the resulting array
    $allResults[] = $row;
}

// Which, in the end, can be JSON-encoded
echo json_encode($allResults);

answered Jul 20, 2011 at 22:13

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

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

3 Comments

OhSnap Over a year ago

Ok, I did that, but the way it is now its a one dimensional array right? So I somehow need to split the row. Is there any easy way to do that?

Pascal MARTIN Over a year ago

$allResults is an array that contains arrays (one per row)

OhSnap Over a year ago

Ok, even though I can't really test it right now (see original post): How do I address the variables/values of the row? Can I do it the way I did it in the main post or do I have to address them through the given name from the database.

Collectives™ on Stack Overflow

Deliver rows to JavaScript using JSON

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related