Accessing JSON results from jQuery Ajax

Question

I'm working on a web application to maintain the administration for a restaurant kind of type. The idea is to make new orders, put order items in that, check finance overviews etc...

I've also got a function to see all the orders in a list, when you select one of them, the order data (such as the name, emailadress, location of the customer) shows up in a another element inside the document.

I'm doing that with this function, every tr inside the has been given a custom attribute; the order_id. When selecting that, a class is given, called selectedRow.

function select_order(order) {
    var item = $(order);
    if (!item.hasClass("selectedRow")) {
        if (!selectedOrderInformation.is(":visible")) {
            switchScreen(selectedOrderInformation, financeOverview);
        }
        item.parent().find(".selectedRow").removeClass("selectedRow");
        item.addClass("selectedRow");
        selectedOrderInformation.html("loading......");
        $.ajax({
            url: "includes/functions/select-order.php",
            type: "get",
            data: {order_id: item.attr("data-order-index")},
            success: function (data) {
                selectedOrderInformation.html(data);
                $("#delete-order-btn").prop("disabled", false);
            }
        });
    } else {
        console.log("DEBUG: Row is already selected");
    }
}

The usage of that function is by doing this:

$("#list tbody tr").click(function () {
    select_order(this);
});

At the first place, i was deploying all the HTML data via PHP. This took a pretty long time, it could take from 500ms to about 1 second. In my opinion thats pretty long.

I was doing that like this (select-order.php):

if (!empty($_GET['order_id'])) {
    $order_id = $_GET['order_id'];

    $order_data = Database::getInstance()->get_all_data_by_order_id($order_id);
    $order_items = Database::getInstance()->get_order_items_by_order_id($order_id);

    while ($row = mysqli_fetch_array($order_data)) {
        echo "<h1>Klant informatie</h1>";
        echo "<p>Voornaam: " . $row['first_name'] . "</p>";
        echo "<p>Achternaam: " . $row['last_name'] . "</p>";
        echo "<p>Emailadres: " . $row['email_adress'] . "</p>";
        echo "<p>Klant informatie: " . $row['customer_info'] . "</p>";
        echo "<br>";
        echo "<h1>Bestellingsinformatie</h1>";
        echo "<p>Order informatie: " . $row['order_info'] . "</p>";
        echo "<p>Locatie: " . $row['location'] . "</p>";
        echo "<p>Gemaakt op: " . $row['created'] . "</p>";
    }

    echo "<br>";
    echo "<table>";
    echo "<thead>";
    echo "<tr>";
    echo "<th>Product naam</th>";
    echo "<th>Hoeveelheid</th>";

    echo "</tr>";
    echo "</thead>";
    while ($row = mysqli_fetch_array($order_items)) {
        echo "<tr>";
        echo "<td>" . $row['name'] . "</td>";
        echo "<td>" . $row['quantity'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    exit;
}

This goes together with the Database class with all the functions:

class Database extends mysqli
{

    // single instance of self shared among all instances
    private static $instance = null;

    private $databaseHost = "";
    private $databaseUser = "";
    private $databasePassword = "";
    private $databaseName = "";

    public static function getInstance() {
        if (!self::$instance instanceof self) {
            self::$instance = new self;
        }
        return self::$instance;
    }

    public function __clone() {
        trigger_error('Clone is not allowed.', E_USER_ERROR);
    }

    public function __wakeup() {
        trigger_error('Deserializing is not allowed.', E_USER_ERROR);
    }

    function __construct() {
        parent::__construct($this->databaseHost, $this->databaseUser, $this->databasePassword, $this->databaseName);
        if (mysqli_connect_error()) {
            exit('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
        }
        parent::set_charset('utf-8');
    }


    function get_all_data_by_order_id($order_id) {
        $query = "SELECT customers.first_name,
                         customers.last_name,
                         customers.email_adress,
                         customers.customer_info,

                         orders.order_info,
                         orders.total_price,
                         orders.location,
                         orders.created

                         FROM customers
                 INNER JOIN orders ON customers.id = orders.customer_id

                 WHERE orders.id = {$order_id}";

        return $this->query($query);
    }

    function get_order_items_by_order_id($order_id) {
        $query = "SELECT `products`.`name`, `orders-items`.`quantity` FROM `orders-items`\n" . "INNER JOIN `products`ON `orders-items`.`products_id` = `products`.`id`\n" . "WHERE order_id=" . $order_id;

        return $this->query($query);
    }
}

Now someone told me i could better translate the data into json and return that, so i did this:

if (!empty($_GET['order_id'])) {
    $order_id = $_GET['order_id'];

    $order_data = Database::getInstance()->get_all_data_by_order_id($order_id);
    $order_items = Database::getInstance()->get_order_items_by_order_id($order_id);

    $rows = array();
    while ($row = mysqli_fetch_array($order_data)) {
        $rows[] = $row;
    }
    return json_encode($rows);
    exit;
}

But as expected, nothing really happened. So i tried changing the javascript to this (trying it as a array because i'm returning it that way?), to deploy one piece of data:

$.ajax({
    url: "includes/functions/select-order.php",
    type: "get",
    data: {order_id: item.attr("data-order-index")},
    success: function (data) {
        selectedOrderInformation.html(data['first_name']);
    }
});

But that didn't work aswell.

Problems

The previous PHP code was to slow, so i had to find another way.
When trying to deploy HTML into the other screen, it doesnt do anything. It stays on the 'loading...' screen, so the success function was'nt reached.

Question

How can my piece of code be changed so it will actually deploy parts of the data from the mysql database?

So to sum up, your ajax request doesn't work ? You have take a look at the developer tools ? — Debflav
– Debflav, Commented Aug 29, 2014 at 9:14
In the network tab, you're requested page does not return a code 500 ? — Debflav
– Debflav, Commented Aug 29, 2014 at 9:20

Gabor · Accepted Answer · 2014-08-29 11:59:25Z

2

In your $.ajax() call you should define what type your response data is expected to be, by adding the following parameter to the call:

dataType: 'json'

Also, you should try echo json_encode($rows); your data instead of returning it.

**Edit: you are receiving an array of arrays, so your original referencing in the success callback won't suffice. Having another look at your MySQL part, If you are only expecting one row to be returned by your query, then you can change your PHP to:

$row = mysqli_fetch_array($order_data);
echo json_encode($row); // instead of $rows

instead of the while loop. That way your selectedOrderInformation.html(data['first_name']); will most likely work.

To clean your query up a bit:

$query = "SELECT p.name, ot.quantity FROM orders-items AS ot
    LEFT JOIN products AS p ON ot.products_id = p.id
    WHERE ot.order_id = " . $order_id;

You could also switch your INNER JOIN to a LEFT JOIN in your "get order data" function. An inner join is absolutely useless here, as you'll have all your data paired based on the foreign keys anyways.

I would try secluding some of the codebase: try commenting out the Database::getInstance() calls, and supplementing some testdata into the processes. To put it short, fake a returned response, by declaring a $row = array('first_name' => 'Joe', 'order_date' => '2014-08-29 11:11:52', ...); and returning that. If its way faster, then your database server might be the bottleneck. If its still slow, then 500ms - 1000ms is actually argueably code related, it might be other hardware aspects that cause the problem. Or for example, do you have your jQuery library loaded from a CDN, or locally?

**Edit: As @Debflav pointed out (and I've also touched upon the matter), that your queries could benefit from not being executed as simple queries, but transforming them into prepared statements. For the full story you could start checking out PHP.net : Prepared Statements, or to keep it short:

Prepared statements look almost just like your everyday query, however variables are not just concatenated into the query string, rather bindings are used. You use the database handler's prepare function instead of query - with this method, you are requesting the MySQL server to inspect your query and optimize it for later use (which will come handy if you're doing the same query over and over again, just with a few varying values).

For more detailed insights on the mechanics of prepared statements and how to get the hang of it for efficiently utilizing it in your projects I recommend you research the topic a bit, but as a quick conversion for your example at hand, it would look like this:

function get_all_data_by_order_id($order_id) {
    $query = "SELECT c.first_name, c.last_name, c.email_adress, c.customer_info, 
                  o.order_info, o.total_price, o.location, o.created
                  FROM customers AS c
                  LEFT JOIN orders AS o ON c.id = o.customer_id
                  WHERE o.id = :order_id";
    $query_params = array( 
        ':order_id' => $order_id 
    ); 

    $preparedStatement = $this->prepare($query);
    return $preparedStatement->execute($query_params);

}

and

function get_order_items_by_order_id($order_id) {
    $query = "SELECT p.name, ot.quantity FROM orders-items AS ot
                  LEFT JOIN products AS p ON ot.products_id = p.id
                   WHERE ot.order_id = :order_id;";
    $query_params = array( 
        ':order_id' => $order_id 
    ); 

    $preparedStatement = $this->prepare($query);
    return $preparedStatement->execute($query_params);
}

And to reflect on how you would build up your JSON response with data including the order headers and the connected order-items would be:

if (!empty($_GET['order_id'])) {
    $order_id = $_GET['order_id'];

    $order_data = Database::getInstance()->get_all_data_by_order_id($order_id);
    $order_items = Database::getInstance()->get_order_items_by_order_id($order_id);

    $orderObject = array();
    $orderObject['header'] = mysqli_fetch_array($order_data);
    $orderObject['items'] = array();    
    while ($orderedItem = mysqli_fetch_array($order_items)){
        $orderObject['items'][] = $orderedItem;
    }

    echo json_encode($orderObject);
}

This way your jQuery could look something as follows:

....
success: function (data) {
    selectedOrderInformation.html('<h3>' + data['header']['first_name'] + '</h3><ul>');
    $.each(data['items'], function(i, item) {
        selectedOrderInformation.append('<li>' + item['name'] + ' x ' + item['quantity'] + '</li>');
    });
    selectedOrderInformation.append('</ul>');
}
....

edited Aug 29, 2014 at 11:59

answered Aug 29, 2014 at 9:21

Gabor

5603 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Bas Over a year ago

I did this and without the dataType: json all the data was printed on the document, but it didnt look good and my css for that element went over its height and width limit. When i added the datatype: json, the screen loaded. but there was nothing on it.

Gabor Over a year ago

@Bas, I've updated the code - I think you weren't receiving any content as you were referencing the returned result incorrectly (as it was an array with further arrays, and not just an array with keys).

Bas Over a year ago

this worked, but its still pretty slow, how does this come then?

Gabor Over a year ago

Well, just a few general notes: the mysqli library already supports prepared statements, so you could take advantage of those, other than that, your query for returning items associated with orders could also be cleaned up a bit. I'll update my answer in a minute (it would be rather ugly to paste in here).

Gabor Over a year ago

@Debflav: I've brushed up the answer to include an example of implementing the queries with prepared statements as per our comments. Bas: I've also included your latest comments solution, however in the future if your original question has been answered, indeed it's best if you start a new question, as to keep questions and their answers more focused on easily overseeable topics.

|

Collectives™ on Stack Overflow

Accessing JSON results from jQuery Ajax

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related