从MySQL数据库中使用Jquery，AJAX和PHP检索数据

I am trying to figure out how to retrieve data from a MySQL database using an AJAX call to a PHP page. I have been following this tutorial

我试图弄清楚如何使用AJAX调用PHP页面从MySQL数据库中检索数据。我一直在关注这个教程

http://www.ryancoughlin.com/2008/11/04/use-jquery-to-submit-form/

But i cant figure out how to get it to send back json data so that i can read it.

但我无法弄清楚如何让它发送回json数据,以便我可以读取它。

Right now I have something like this:

现在我有这样的事情:

$('h1').click(function() {
            $.ajax({
                type:"POST",
                url: "ajax.php",
                data: "code="+ code,
                datatype: "xml",
                success: function() {

                $(xml).find('site').each(function(){
                    //do something
                });
            });


        });

My PHP i guess will be something like this

我想我的PHP会是这样的

    <?php

    include ("../../inc/config.inc.php");

    // CLIENT INFORMATION

    $code        = htmlspecialchars(trim($_POST['lname']));

    $addClient  = "select * from news where code=$code";
    mysql_query($addClient) or die(mysql_error());

?>

This tutorial only shows how to insert data into a table but i need to read data. Can anyone point me in a good direction?

本教程仅显示如何将数据插入表中,但我需要读取数据。任何人都能指出我的方向吗?

Thanks,

Craig

2 个解决方案

#1

It's nothing different. Just do your stuff for fetching data in ajax.php as usually we do. and send response in your container on page.

没什么不同的。就像我们一样,只需要在ajax.php中获取数据。并在页面上的容器中发送响应。

like explained here :

像这里解释的:

http://openenergymonitor.org/emon/node/107

http://www.electrictoolbox.com/json-data-jquery-php-mysql/

#2

First of all I would highly recommend to use a JS object for the data variable in ajax requests. This will make your life a lot simpler when you will have a lot of data. For example:

首先,我强烈建议在ajax请求中使用JS对象作为数据变量。当您拥有大量数据时,这将使您的生活变得更加简单。例如:

$('h1').click(function() {
            $.ajax({
                type:"POST",
                url: "ajax.php",
                data: { "code": code },
                datatype: "xml",
                success: function() {
                $(xml).find('site').each(function(){
                    //do something
                });
            });
        });

As for getting information from the server, first you will have to make a PHP script to pull out the data from the db. If you are suppose to get a lot of information from the server, then in addition you might want to serialize your data in either XML or JSON (I would recomment JSON).

至于从服务器获取信息,首先你必须制作一个PHP脚本来从数据库中提取数据。如果您想从服务器获取大量信息,那么您可能还想用XML或JSON序列化数据(我会推荐JSON)。

In your example, I will assume your db table is very small and simple. The available columns are id, code, and description. If you want to pull all the news descriptions for a specific code your PHP might look like this. (I haven't done any PHP in a while so syntax might be wrong)

在您的示例中,我将假设您的db表非常小而且简单。可用列是id,代码和说明。如果你想提取特定代码的所有新闻描述,你的PHP可能会是这样的。 (我有一段时间没有做过任何PHP,所以语法可能有误)

// create data-structure to handle the db info
// this will also make your code more maintainable
// since OOP is, well just a good practice
class NewsDB {
    private $id = null;
    var $code = null;
    var $description = null;

    function setID($id) {
        $this->id = $id;
    }
    function setCode($code) {
        $this->code = $code;
    }
    function setDescription($desc) {
        $this->description = $desc;
    }
}

// now you want to get all the info from the db
$data_array = array(); // will store the array of the results
$data = null; // temporary var to store info to

// make sure to make this line MUCH more secure since this can allow SQL attacks
$code = htmlspecialchars(trim($_POST['lname']));

// query
$sql = "select * from news where code=$code";
$query = mysql_query(mysql_real_escape_string($sql)) or reportSQLerror($sql);

// get the data
while ($result = mysql_fetch_assoc($query)) {
    $data = new NewsDB();
    $data.setID($result['id']);
    $data.setCode($result['code']);
    $data.setDescription($result['description']);
    // append data to the array
    array_push($data_array, $data);
}

// at this point you got all the data into an array
// so you can return this to the client (in ajax request)
header('Content-type: application/json');
echo json_encode($data_array);

The sample output:

样本输出:

[
  { "code": 5, "description": "desc of 5" },
  { "code": 6, "description": "desc of 6" },
  ...
]

So at this stage you will have a PHP script which returns data in JSON. Also lets assume the url to this PHP script is foo.php.

因此,在这个阶段,您将拥有一个以JSON格式返回数据的PHP脚本。还假设这个PHP脚本的url是foo.php。

Then you can simply get a response from the server by:

然后,您只需通过以下方式从服务器获取响应:

$('h1').click(function() {
            $.ajax({
                type:"POST",
                url: "foo.php",
                datatype: "json",
                success: function(data, textStatus, xhr) {
                   data = JSON.parse(xhr.responseText);
                   // do something with data
                   for (var i = 0, len = data.length; i < len; i++) {
                       var code = data[i].code;
                       var desc = data[i].description;
                       // do something
                   }
            });
         });

That's all.

#1