使用 Jquery、AJAX 和 PHP 从 MySQL 数据库中检索数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9053853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving Data with Jquery, AJAX, and PHP from a MySQL Database
提问by craigtb
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
我想弄清楚如何使用对 PHP 页面的 AJAX 调用从 MySQL 数据库中检索数据。我一直在关注这个教程
http://www.ryancoughlin.com/2008/11/04/use-jquery-to-submit-form/
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
克雷格
回答by miki725
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、code 和 description。如果您想提取特定代码的所有新闻描述，您的 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.
就这样。
回答by miki725
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://openenergymonitor.org/emon/node/107
