current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1


i am working with JSON to send data to and from my server , but normally i work with sending one data at a time , now i want to :
retrieve all the rows from a table (mysql database) --> php put it in a JSON array + callback ---> javascript retrives it and display it out by looping through the data.

Here's my javascript(jQuery):

$.getJSON(domain_path + 'generate.php?table=' +  tbname + '&callback=?', function(data) {
});

As you can see this has table= the table name. which is for php to know which table to extract the data from.

But for the php part im not sure what to use to produce a JSONP array.

<?php
//connects to database
  include 'connect.php';

$tbname = mysql_real_escape_string($_GET['table']);
$callback = $_GET['callback'];

//some mysql commands
//after mysql commands
//i would use this to output data but this is only for one line of data.

    $output = array('error'=>'0');
    $out_string =  json_encode($output);
    echo $callback.'('.$out_string.');';    

?>

Mysql table structure:
Table name : users
name , link , email

How can i get all the rows from users table which contians their name , link and email and out it into a JSON array.
And how would i display it out using javascript(jquery)? Is it using the for function in javascript

share|improve this question
1  
Are you sure you want JSON P output? From your Javascript call it looks like you want regular JSON. –  deceze Apr 3 '12 at 7:59
    
ops sorry its JSON. –  sm21guy Apr 3 '12 at 8:01
    
mysql_real_escape_string() is useless for escaping an object name, like a table or column name. If you quote it with backticks ` in the query, and error out if strpos($_GET['table'], '`') !== FALSE then you should be fairly safe. –  DaveRandom Apr 3 '12 at 8:17
    
thanks for the advice. –  sm21guy Apr 3 '12 at 8:20
add comment

2 Answers

up vote 4 down vote accepted

Print out the JSON array in PHP:

$query = mysql_query("SELECT * FROM ".$tbname."");
$rows = array();
while($r = mysql_fetch_assoc($query)) {
   $rows[] = $r;
}
print json_encode($rows);

Catch and loop over result in jQuery:

    $.getJSON(domain_path + 'generate.php?table=' +  tbname + '&callback=?', function(data) {
         $.each(data, function() {
           $.each(this, function(k, v) {
           /// do stuff
           });
         });
    });

First each loop goes over your rows/objects; second loop goes over your attributes/columns.

share|improve this answer
    
what is function(k , v)? –  sm21guy Apr 3 '12 at 8:32
    
what is $sth ??? –  sm21guy Apr 3 '12 at 8:37
    
@sm21guy Mistake, edited. And for function(k , v) check out documentation: api.jquery.com/jQuery.each –  Nathan Q Apr 3 '12 at 8:53
    
thanks. let me try it first. –  sm21guy Apr 3 '12 at 8:57
    
how can loop the data out? –  sm21guy Apr 3 '12 at 9:09
show 1 more comment
up vote 0 down vote

You approach using a named callback is all wrong, the callback functionality is already provided by jQuery - note that you have passed an empty function as the last argument to getJSON()? Well, that's your callback.

You want to do something like this:

PHP:

<?php

  //connects to database
  include 'connect.php';

  // mysql_real_escape_string() doesn't help, we need to do something like this:
  if (strpos($_GET['table'], '`') !== FALSE) {
    header('HTTP/1.1 400 Bad Request');
    exit;
  }

  // Build/make the query
  $query = "SELECT * FROM `{$_GET['table']}`";
  if (!$result = mysql_query($query)) {
    header('HTTP/1.1 500 Internal Server Error');
    exit;
  }

  // Fetch the returned data into an array of objects:
  $data = array();
  while ($row = mysql_fetch_assoc($result)) {
    $data[] = (object) $row;
  }

  // Send the final data back as JSON
  exit(json_encode($output));

Javascript:

$.getJSON(domain_path + 'generate.php?table=' +  tbname, function(data, status, xhr) {
  // First check the response is a success:
  if (xhr.status != 200) {
    console.log('Server responded with error code '+xhr.status);
    return;
  }
  // Now iterate over the data:
  $.each(data, function(key, item) {
    // Do something with the data here, for example:
    console.log('Name: '+item.name+', Link: '+item.link+', Email: '+item.email);
  });      
});
share|improve this answer
    
is it able to work cross platform? –  sm21guy Apr 3 '12 at 8:42
    
cross domain i meant –  sm21guy Apr 3 '12 at 8:44
    
Yes. Javascript and PHP are both platform independent. Certain browsers provide non-standard bits of Javascript, and certain PHP functions don't work on certain OSes, but none of the above code uses anything that would give you an issue. –  DaveRandom Apr 3 '12 at 8:44
    
Thanks , just tried out the code , but im getting a null for the result of the php script. –  sm21guy Apr 3 '12 at 8:47
    
What do you get if you print_r($data);? And regarding the X-Domain - no, it won't work, but I prefer to do that server-side with cURL etc... is this option available to you? –  DaveRandom Apr 3 '12 at 9:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.