Using Javascript I need to get some rows from a DB table, and then loop through each row and use different fields from each row.
For example, if I get the rows like this:
var coordinatesArray = '<?php
global $wpdb;
echo $wpdb->get_var("SELECT * FROM users_coordinates WHERE lat>20 and lat<40 and long>-10 and long<40);
?>';
How should I write the following code:
// for each row do: {
// alert the id field
// alert the lat field
// alert the long field
// }
JSON is the way to put the output in, that ensures that the result will be valid JavaScript and protects you from arbitrary code execution in JS.
var coordinatesArray = <?php
global $wpdb;
// replace * by id, lat and long since you don't need other fields
$sql = "SELECT id, lat, long FROM users_coordinates WHERE lat>20 and lat<40 and long>-10 and long<40";
$rows = $wpdb->get_results($sql);
// if the function fails (?), make valid JS syntax
if (is_array($rows)) {
echo json_encode($rows);
} else {
echo '[]';
}
?>;
for (var i=0; i<coordinatesArray; i++) {
alert(coordinatesArray[i].id);
alert(coordinatesArray[i].lat);
alert(coordinatesArray[i]["long"]);
}
The other way would be creating a JS object with the id field as key of the JS object:
var coordinatesMap = <?php
global $wpdb;
// replace * by id, lat and long since you don't need other fields
$sql = "SELECT id, lat, long FROM users_coordinates WHERE lat>20 and lat<40 and long>-10 and long<40";
// note: OBJECT_K, the result will be an associative array with the first field of a
// row as key
$rows = $wpdb->get_results($sql, OBJECT_K);
// if the function fails (?), make valid JS syntax
if (is_array($rows)) {
echo json_encode($rows);
} else {
echo '{}';
}
?>;
for (var id in coordinatesMap) {
if (coordinatesMap.hasOwnProperty(id)) {
alert(id);
alert(coordinatesMap[id].lat);
alert(coordinatesMap[id]["long"]);
}
}
Please replace alert by something else, it's not really user-friendly. Remember that PHP != JavaScript and you cannot use PHP functions in JavaScript and vice versa. If you're not sure how the output would look like, use the View source option of a page.
References:
long which is a keyword reserved for future use. See my edit.$sql = "SELECT user_id, ... and not $sql = "SELECT id, ... as I wrote here. But in the for loop you suggested I used the var id as you wrote it, and it still works. How does it know to map user_id into id? User_id is not defined as a key in the db although it is unique.user_id is not unique, do not use the second code as it results in data loss when records with the same user_id occur in the result. The id as in id in coordinatesMap defines a JavaScript variable with the key of coordinatesMap, it could be named the_key_in_coordinatesMap as well and has nothing to do with the id field in the database. The $wpdb->get_results() method uses the first field as key if the second argument is set to OBJECT_K.user_id is unique so no problem there. I see, OBJECT_K does the trick. I just have to make sure that user_id stays the first field for good. Thanks.You may have a better time writing your own custom PHP script to fetch these values (rather than depending on WP), loop through them, and build a json object that you return to the javascript.
First, $wpdb->get_var can only get one value. http://codex.wordpress.org/Class_Reference/wpdb#SELECT_a_Variable
Use $wpdb->get_results instead http://codex.wordpress.org/Class_Reference/wpdb#SELECT_Generic_Results
var coordinatesArray = '<?php
global $wpdb;
echo $wpdb->get_results("SELECT * FROM users_coordinates WHERE lat>20 and lat<40 and long>-10 and long<40);
?>';
for(coord in coordinatesArray) {
alert(coordinatesArray[coord].id);
alert(coordinatesArray[coord].lat);
alert(coordinatesArray[coord].long);
}
I haven't tested, but it should be something like that.
[], not ''. echo $wpdb->het_result(...) gives just Array, not the representatation of it.
for (var i = 0; i < coordinatesArray.length; ++i) loop. for ... in is not meant for use with arrays, but instead for use with objects. It will enumerate all enumerable properties of the array, including say any that have been added by the Prototype.js framework or any user code that may or may not be under your control.obj.hasOwnProperty(key) check as well to see whether a property is inherited or not. But I think that falls outside the scope of this question.get_var. Probably saved me a lot of debugging time.