从jQuery Ajax访问JSON结果

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`
" . "INNER JOIN `products`ON `orders-items`.`products_id` = `products`.`id`
" . "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?

doubi8383
doubi8383 它只是说:200OK
大约 6 年之前 回复
donglu8779
donglu8779 在网络选项卡中,您要求的页面不返回代码500?
大约 6 年之前 回复
douyan8267
douyan8267 确实,但没有记录任何东西。
大约 6 年之前 回复
douyouyi8878
douyouyi8878 总结一下,你的ajax请求不起作用?您已经了解了开发人员工具?
大约 6 年之前 回复

1个回答

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>');
}
....
douzaipou3327
douzaipou3327 好吧,我主要习惯PDO,不过我认为如果你使用预处理语句并希望在另一个准备好的语句运行后对它执行任何操作,你会首先告诉系统存储其结果 (否则会自动丢弃它)。 这可以通过添加Database :: store_result($ order_data)来实现; 在你的$ order_data = Database :: getInstance() - > ....行之后。
大约 6 年之前 回复
dongliang1654
dongliang1654 啊,谢谢你! 为此+1。 但是,通过准备好的陈述,您如何重新制作功能。 我在select-order.php中调用的mysqli_fetch_array函数给出了错误消息:预期的mysqli_result,得到了bool。 是因为我们没有使用$ this-> query($ query); 了吗?
大约 6 年之前 回复
dongma1666
dongma1666 我已经刷新了答案,根据我们的评论包含一个使用预准备语句实现查询的示例。 Bas:我还提供了您最新的评论解决方案,但是如果您的原始问题得到解答,将来确实最好是开始一个新问题,以便将问题及其答案更多地集中在易于监督的主题上。
大约 6 年之前 回复
dongpao5658
dongpao5658 是的,你是对的。 我想我可以进一步询问这个问题:)
大约 6 年之前 回复
doumeng1897
doumeng1897 :也许你应该开一个新问题,因为Gabor解决了这个问题。
大约 6 年之前 回复
douxin2011
douxin2011 因为我现在只想我可以这样使用它:$('。someElement')。html(data ['first_name'] + data ['last_name']);
大约 6 年之前 回复
douqin6785
douqin6785 对不起,我不得不去,我的反应很短,时间有点不足。 但实际上,感谢您的时间和解释,我担心的唯一问题是订单项目不仅仅是一个数组,在某人的订单中可能只有一个项目,所以在javascript中,我怎么会 去做? 因为我不能在那里调用mysqli_fetch_array?
大约 6 年之前 回复
douyong4842
douyong4842 OP的新问题“为什么它仍然缓慢?” 太宽泛了。 如果您的查询没问题,问题可能来自您的服务器,您的PHP代码'质量'[...]。 此外,如果您正在尝试帮助他查询,请考虑使用预备语句。 为你的努力+1。
大约 6 年之前 回复
dqnek0079
dqnek0079 抱歉,我现在g2g,下班了。 但是一旦我回来,我就会准备好答案。
大约 6 年之前 回复
dongliling6336
dongliling6336 好吧,只是一些一般注意事项:mysqli库已经支持预处理语句,所以你可以利用那些,除此之外,你的查询返回与订单相关的项目也可以稍微清理一下。 我会在一分钟内更新我的答案(粘贴在这里会相当难看)。
大约 6 年之前 回复
dpw50696
dpw50696 这有效,但它仍然很慢,这是怎么来的?
大约 6 年之前 回复
douxun4860
douxun4860 我已经更新了代码 - 我认为你没有收到任何内容,因为你错误地引用了返回的结果(因为它是一个带有更多数组的数组,而不仅仅是带有键的数组)。
大约 6 年之前 回复
dougaoshang0237
dougaoshang0237 我做了这个,没有dataType:json所有数据都打印在文档上,但它看起来不太好,我的css对于该元素超过了它的高度和宽度限制。 当我添加数据类型:json时,屏幕加载。 但没有任何内容。
大约 6 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐