Retrieving JSON data and display in a table with AJAX

Question

I am very new to programming with jQuery. I've spent quite a while trying to move forward with this, and I've managed to get some of it done. But I've really hit a wall and I can't seem to find help from anywhere/anyone.

Scenario:

I am using a select box to store different music genres, which I have retrieved via PHP/MySQL.

    <?php
include 'connectingDB.php';
$catSQL = "SELECT * FROM category ORDER BY catDesc";
$queryresult = mysql_query($catSQL)
or die (mysql_error());
echo "<select id= \"catID\">";
while ($row = mysql_fetch_assoc($queryresult)) {
    $catID = $row['catID'];
    $catDesc = $row['catDesc'];


    echo "<option value = \"$catID\">$catDesc</option>\n";

}
echo "</select>";
mysql_free_result($queryresult);
mysql_close($conn);
?>

When I click on a genre, I want all of the related CDs and CD information to be retrieved in JSON format and dynamically displayed in a table using AJAX (below the select box on that same page)

<?php
header('Content-type: application/json');
include 'connectingDB.php';
$category = $_REQUEST['catname'];
$sql = "SELECT `CDID`, `CDTitle`, `CDYear`, `pubID`, `CDPrice`
        FROM `tiptop_cd`
        INNER JOIN tiptop_category
        ON tiptop_cd.catID=tiptop_category.catID
        WHERE catDesc = '{$category}'";
$result = mysqli_query($con,$sql);
$row = mysqli_fetch_array($result);
while($row = mysqli_fetch_array($result)){
    $returned[] = $row;
}
echo json_encode($returned);

?>

All of the above code works on its own. But I'm looking to connect it all together. I think it needs to be via an onchange event in jQuery?

I've got an alert to pop up after clicking a category, but that's as far as I can get..

$(document).ready(function(){
$("#catID").change(function(){
alert("The text has been changed.");
});
});

Does it need to be in a foreach loop? Or a foreach within a foreach?

To summarize, I'm trying to understand how to: display the cds and cd information that are related to the specific category that is currently selected, in a dynamic table with ajax

Any help is massively appreciated.

score 1 · Answer 1 · answered Dec 17 '13 at 20:22

hopefully this can get you started

$(document).ready(function () {
    $("#catID").change(function () {
        $.post("index.php?catname=" + $(this).val(), function (data) {
            var table = $('<table></table>'); //create table
            $.each(data, function (index, value) { //loop through array
                var row = $('<tr></tr>'); //create row
                var cell1 = $("<td></td>").val(value.CDID); //create cell append value
                //etc
                row.append(cell1); //append cell to row
                table.append(row); //append row to table
            });
            $('#div').append(table); //append table to your dom wherever you want
        });
    });
});

Hi, thanks for getting back to me. I've tried implementing this code and altering it accordingly, but nothing seems to happen. Here is the site. http://www.numyspace.co.uk/~unn_w10012836/fanzine/fanzine.php Here is the pastebin for the js aswell http://pastebin.com/TKaCbxNK — user3112518, Dec 18 '13 at 14:37

score 0 · Accepted Answer · edited May 23 '17 at 11:44

You may want to use AJAX for this purpose. Ajax will allow you to send the user's choice (ie. the dropdown selection) to a back-end PHP file.

The PHP file will process the received data (ie. the user's choice) and perform a database lookup based on that info. It will take the result from db and construct (in a variable) the required HTML for the table, and then echo back the contents of that variable -- which will be received in the AJAX procedure's success: (or .done() to use promise syntax) function.

INSIDE the success/done function, you can use received data. For example, you can use the jQuery .html() method to replace the contents of a specified DIV with the HTML you received.

My approach would differ from the other proposed solutions in the following ways:

I prefer using the full $.ajax() syntax, as it allows for greater structure, which makes it somewhat easier to understand/manipulate at first. Note that .post(), .get() and .load() are all shortcut forms of $.ajax() that make certain assumptions in order to streamline the process. I suggest learning the $.ajax() format first, and then utilizing the others. Having done gazillions of ajax blocks myself, I continue to use $.ajax() most times. Perhaps it is a preference, but I find it much easier to use/read/review -- and it also allows additional params that the others do not, which makes it more flexible and useful**.
It is necessary to use a second .PHP file to act as your ajax processor. You cannot use the same .PHP file that contains your AJAX code block. See this answer.
The place to construct the HTML table is in the PHP (processor file). As mentioned, construct it all in a variable and then, at the end, output that variable:

Note how the $r variable is constructed in the while loop, and only ECHOed out at the end.

$aContact_info = mysql_query("SELECT * FROM `contacts`");
$r = '<table>';
while ($rrow = mysql_fetch_array($aContact_info)) {
    $r .= '<tr>
            <td>
                Name:<br/>
                <input type="text" id="first_name" name="first_name" value="'.$rrow['first_name'].'"> 
                <input type="text" id="last_name" name="last_name" value="'.$rrow['last_name'].'">
            </td>
            <td>
                Email:<br/>
                <input type="text" id="email" name="email" value="'.$rrow['email1'].'">
            </td>
            <td>
                Cell Phone:<br/>
                <input type="text" id="cell_phone" name="cell_phone" value="'.$rrow['cell_phone'].'">
            </td>
        </tr>
    ';
}
$r .= '</table>';
echo $r;

Here are some examples that should help:

Simple explanation of AJAX
Example with MySQL lookup in PHP Processor file

** Differences between .get() and .post() and $.ajax():

GET vs POST in AJAX calls
Kevin Chisholm
Sychronous AJAX

score 0 · Answer 3 · answered Dec 17 '13 at 20:35

another (faster) method would be to return an html table as a string and inject it into the DOM. generate the table in your PHP handler, then do $('#div').load('/index.php?catname=catname'); or do $.get like below

$(document).ready(function () {
    $("#catID").change(function () {
        $.get({               
            url: 'index.php',
            data: { catname: $(this).val() }
            dataType: 'html',
            success: function (html) {
               $('#div').html(html);
            },
            error: function (xhr, err) { displayErrorMessage("Error: \n\nreadyState: " + xhr.readyState + "\nstatus: " + xhr.status + "\nresponseText: " + xhr.responseText, 'nosave'); }
        });
    });
 });

Retrieving JSON data and display in a table with AJAX

3 Answers3