0

I'm working on outputting multiple MySQL JSON rows to an HTML table via PHP (server side) and jQuery (front end) through a simple search box. I've successfully managed to parse one row of JSON to the table but now need to accommodate multiple rows of JSON data.

My HTML table on the search page looks something like this:

ARTIST | TITLE | LOCATION 
U2     | One   | Ireland 
Foals  | Miami | UK

and the JSON file passed to it utilises similar headings:

{"ARTIST":"Katy Perry","LOCATION":"United States", "TRACKTITLE":"Roar"}

I've been playing around with the code for a while, incorporating arrays to loop through the data. This works in as much as it loops through the MySQL rows correctly and successfully sends multiple rows of data back in JSON format (I've decoded my search page in Firebug). The problem is that my jQuery file can't place it into my HTML table, resulting in a blank page. I've tried various methods of arrays from previous Stackoverflow questions but can't quite get it to work, being returned the 'ARTIST', 'TRACKTITLE' and 'LOCATION' fields with every returned result

extract from PHP file:

$result = mysql_query($sql);
$data = array();
while ($array = mysql_fetch_assoc($result)) {
$data[] = $array;
}
echo json_encode($data);

Edited to include jQuery extract and more specific question:

function(data) {
var sentData=JSON.parse(data)
for (var i = 0; i < sentData.ARTIST.length; ++i)
{
tr = $('<tr/>');
tr.append("<td>" + sentData.ARTIST + "</td>");
tr.append("<td>" + sentData.TRACKTITLE + "</td>");
tr.append("<td>" + sentData.LOCATION + "</td>");
$('#multiple').append(tr);
}
});

How can I parse only the corresponding values of each 'ARTIST', TRACKTITLE' and 'LOCATION' result using the JSON.parse method? This is the full returned JSON string:

[{"ARTIST":"Katy Perry","TRACKTITLE":"Roar","LOCATION":"United States"}, {"ARTIST":"U2","TRACKTITLE":"One","LOCATION":"Ireland"}]
Byate
  • 123
  • 1
  • 2
  • 16
  • It looks to me like the first JSON format is better. You loop through each element in the array and turn it into a row of the table. Why do you want the second format? – Barmar Dec 23 '13 at 18:33
  • @Tim He's already doing what the answers in that question recommend. – Barmar Dec 23 '13 at 18:33
  • Just build the array like you want it. There is no special magic to JSON encoding. It just represents a data structure. So build the data structure you want in PHP. In this case that is either an object or associative array with the column name as keys each holding an array of arrays of values. That being said, I agree with other comments that the first JSON structure would probably be much easier to work with to output an HTML table. – Mike Brant Dec 23 '13 at 18:34
  • 1
    If you can't turn your current JSON into an HTML table, the problem is in your jQuery code, not PHP. – Barmar Dec 23 '13 at 18:34
  • @Barmar then this question is not about what it claims to be. This isn't a php issue. That data format is completely usable for generating html tables. I have done it enough times. – Tim Seguine Dec 23 '13 at 18:36
  • I would agree with @Barmar that that your current method of JSON encoding should be what you work with in javascript. – Mike Brant Dec 23 '13 at 18:37
  • Ok thanks for the advice! I've updated the post to help explain how I currently parse the data into my HTML table. Any pointers would be hugely helpful! – Byate Dec 23 '13 at 18:42
  • There is a matching question + answer concerning the string-type-problem at: http://stackoverflow.com/questions/28261613/convert-mysql-result-to-json-with-correct-types – Marcel Ennix Feb 01 '15 at 11:28

1 Answers1

0

Try this I took the javascript you provided and changed as below. (see also jsfiddle at the bottom)

var sentData=JSON.parse('{"ARTIST":[["Katy Perry"], ["U2"]],"LOCATION":[["United States"], ["Ireland"]],"TRACKTITLE":[["Roar"], ["One"]]}')
for (var i = 0; i < sentData.ARTIST.length; ++i)
{
tr = $('<tr/>');
tr.append("<td>" + sentData.ARTIST[i] + "</td>");
tr.append("<td>" + sentData.TRACKTITLE[i] + "</td>");
tr.append("<td>" + sentData.LOCATION[i] + "</td>");
$('#multiple').append(tr);
}

see the working in jsfiddle here. http://jsfiddle.net/jspatel/Ya82B/

user3123529
  • 112
  • 5
  • Thanks for this! How would I format the `JSON.parse` brackets when the artist, location and titles are unknown and an unknown number of variables (i.e not Katy Perry or U2 because that might not be returned in the database search) ? – Byate Dec 23 '13 at 19:08
  • Ok you can have either empty {"ARTIST":[[""], ["U2"]] or {"ARTIST":[["_UNKNOWN_"], ["U2"]] you can do this on php side and in your javascript catch it and display accordingly. does that answer your question? – user3123529 Dec 23 '13 at 19:20
  • I'm not sure it does, but thanks for your help anyhow. What i'm looking for is something on the jQuery side that only parses the corresponding values from each instance of 'ARTIST', 'LOCATION', 'TRACKTITLE' into the HTML table. Not sure if that's possible with `JSON.parse`? – Byate Dec 23 '13 at 19:29
  • no I dont think you can do that with JSON.parse. you should do it on the php side its faster that way and on the javascript you should do bare minimum . good luck! – user3123529 Dec 23 '13 at 22:11