pull a table from SQL to create javascript array?

Question

I have data like this in a SQL table:

ID  |  Word  |  Count
1     word1     10
2     word2     5

I'd like to request this data from javascript (or an aJax call I guess?) and have it create an array. I also don't care about the ID#, but just the word and it's 'count.' Then I guess it would be a 2d array to store this info? Ie, word[0][0] would return "word1" and word[0][1] would return 10. Maybe there's a better way to do that last part, though. And then I'd like to sort these by count.

Thoughts?

EDIT: It would seem as though I have the data getting piped back via PHP to JSON. However, how the heck do I get the data out of JSON and into a JS array?

$.getJSON('php_file.php', function(response) {
    // response is a JSON object that contains all the info from de sql query
    /* do your JS stuff here */
})

It's saying that response is false, and no more. What's the deal?

Fetch the data in php and process it in javascript or php for that matter. — Rayf, Nov 27 '13 at 23:11
possible duplicate of [JSON encode MySQL results](http://stackoverflow.com/questions/383631/json-encode-mysql-results) — scrowler, Nov 27 '13 at 23:16

score 2 · Answer 1 · edited May 23 '17 at 12:05

Have a look at PHP's json_encode in the manual. It will allow you to convert a PHP array (which you'll populate from a database query) to a JSON object, which you will then output to the Ajax call from your Javascript.

Process:

Javascript calls e.g. results.php
results.php calls database and gets array
results.php uses json_encode on said array and outputs it to the browser (echo)
Javascript receives nice JSON array to use.

There's a nice example here: https://stackoverflow.com/a/383664/2812842

score 1 · Accepted Answer · answered Nov 28 '13 at 00:10

1

The PHP file should look like this:
PHP_FILE.PHP

$sql = "SELECT * FROM table ORDER BY count [ASC|DESC]";
$result = execute_query($sql);
while($array = mysql_fetch_assoc($result));
echo json_encode($array);

And, then you have to make the AJAX call (for example, using JQuery)

$.getJSON('php_file.php', function(response) {
    // response is a JSON object that contains all the info from de sql query
    /* do your JS stuff here */
})

answered Nov 28 '13 at 00:10

Joaquín O

1,431
1
9
16

Thank you for your response, however I'm getting this error: Call to undefined function execute_query() in /Volumes/grayMac/creations/web/NSAgame/_v2/grab.php on line 16 Any ideas? – prismspecs Nov 28 '13 at 00:24
Ah ok nevermind, changed it to mysql_query and it worked. So now to figure out how to make use of a JSON object ;-) – prismspecs Nov 28 '13 at 00:27
I tried $.each(result, function(i, field){ alert(field); }); but it won't give me anything – prismspecs Nov 28 '13 at 00:37
Try using `console.log(response)` to see the object in the js console (F12 in chrome). Then, if it looks like a numeric array use this notation `response[0]` and if it looks like key:value, then use this notation `response.key'. Got it? – Joaquín O Nov 28 '13 at 15:47
As an example, for your response object `response[0].count` is equal to `10`. – Joaquín O Nov 28 '13 at 15:48
@prismspecs Let me know if this was useful.. – Joaquín O Nov 28 '13 at 19:26

pull a table from SQL to create javascript array?

2 Answers2