javascript 如何使用 Ajax Json PHP & MySQL 应用排序和过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10651387/
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
How to apply sorting and filtering using Ajax Json PHP & MySQL
提问by Gavin
Hi i am a beginner i am working on software site. i have built all the pages and layout for the site that was the easy part done using HTML CSS AND JAVASCRIPT alone, only thing left is to make main categories pages for different software which is tough for me.
嗨，我是一个初学者，我在软件网站上工作。我已经为该站点构建了所有页面和布局，这是仅使用 HTML CSS 和 JAVASCRIPT 完成的简单部分，剩下的就是为不同的软件制作主要类别页面，这对我来说很困难。
i want to to add sortingoption on categorypages like this (See Here)where user shall be able to sort software according to date, name, date added etc. and also be able to control max number of software to display like 20, 30, 100 etc.
我想在这样的类别页面上添加排序选项（见这里），用户应该能够根据日期、名称、添加日期等对软件进行排序。并且还能够控制 显示的最大软件数量，如 20、30、100 等。
On my HTML Page i have these div'sin which i want to display data (different softwares) from MySQL database"security_software" (it is a testing database) from table"internet_security" (it is a testing table)
在我的 HTML 页面上，我有这些div，我想在其中显示来自表“internet_security”（它是一个测试表）的MySQL 数据库“security_software”（它是一个测试数据库）的数据（不同的软件）
HTML Div's
HTML Div
<div class="category-container"> <div class="category-image"></div> <div class="category-desc"><a href="#">#</a><p>text</p></div> <div class="rating5" >Editors' rating: </div> <div class="category-download-btn"><a href="#">Download</a></div> <div class="category-buy-btn"><a href="#">Buy</a></div> </div>
<div class="category-container"> <div class="category-image"></div> <div class="category-desc"><a href="#">#</a><p>text</p></div> <div class="rating5" >Editors' rating: </div> <div class="category-download-btn"><a href="#">Download</a></div> <div class="category-buy-btn"><a href="#">Buy</a></div> </div>
After Some research i have got a solution to use JSON AJAX PHP &MySQL
经过一些研究，我得到了使用 JSON AJAX PHP & MySQL 的解决方案
JAVASCRIPT Code i have
我有 JAVASCRIPT 代码
<head>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script type="text/javascript">
$.ajax({
url: 'ajax.php',
dataType: 'json',
success: function(response){
data = '';
$.each(response,function(i,val){
data = '<div class="category-image">'+val.image+'</div>'+
'<div class="category-link"><a href="#">'+val.id+'</a></div>'+
'<div class="category-desc"><p>'+val.description+'</p> </div>'+
'<div class="rating5" >'+val.rating+'</div>'+
'<div class="category-download-btn"><a href="'+val.download+'">Download</a></div>'+
'<div class="category-buy-btn"><a href="'+val.buy+'">Buy</a></div>';
$('<div>').attr('id',i).html(data).appendTo('#response');
});
}
});
</script>
</head>
<body>
<div id='response'></div>
</body>
PHP Code i have
我有 PHP 代码
<?php
$q=$_GET["q"];
$con = mysql_connect('localhost', 'root', '');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("security_software", $con);
$sql="SELECT * FROM internet_security ORDER by `".$q."` DESC" ;
$result = mysql_query($sql);
$response = array();
$i=0;
while($row = mysql_fetch_array($result))
{
$response[$i]['id'] =$row['id'];
$response[$i]['title'] = $row['title'];
$response[$i]['image'] = $row['image'];
$response[$i]['description'] = $row['description'];
$response[$i]['rating'] = $row['rating'];
$response[$i]['download'] = $row['download'];
$response[$i]['buy'] = $row['buy'];
$i++;
}
mysql_close($con);
echo json_encode($response);
?>
Now it is not working at all as i dont have any place to attach these codes for (categories drop down) in javascript i have.
现在它根本不起作用，因为我没有任何地方可以在我拥有的 javascript 中附加这些代码（类别下拉）。
<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="id">id</option>
<option value="title">title</option>
<option value="image">image</option>
<option value="description">description</option>
<option value="description">rating</option>
<option value="download">download</option>
<option value="buy">buy</option>
</select>
</form>
Please help me guys where can i attach these code and how to get it working, i am totally confused.
请帮助我在哪里可以附加这些代码以及如何使其工作，我完全困惑。
回答by Gavin
First thing worth noting is, if you are going to display tabular data... Use a table! It will make things a lot easier for you.
首先值得注意的是，如果您要显示表格数据...请使用表格！它会让你的事情变得更容易。
Secondly. Build your code and table as if Ajax did not exist. Initially populate the data using PHP on the page your displaying the data. Then, hook up the column header's so they link to your page, but passing which column you want to sort by and also which direction.
其次。构建您的代码和表，就像 Ajax 不存在一样。最初在显示数据的页面上使用 PHP 填充数据。然后，连接列标题，以便它们链接到您的页面，但传递您想要排序的列以及哪个方向。
i.e.
IE
<?
$column = (isset($_GET["column"]) ? $_GET["column"] : 'id');
$direction = (isset($_GET['direction']) ? $_GET['direction'] : 'asc');
$con = mysql_connect('localhost', 'root', '');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("security_software", $con);
$sql="SELECT * FROM internet_security ORDER by '".$column."' " . $direction;
$result = mysql_query($sql);
$response = array();
$i=0;
while($row = mysql_fetch_array($result))
{
$response[$i]['id'] =$row['id'];
$response[$i]['title'] = $row['title'];
$response[$i]['image'] = $row['image'];
$response[$i]['description'] = $row['description'];
$response[$i]['rating'] = $row['rating'];
$response[$i]['download'] = $row['download'];
$response[$i]['buy'] = $row['buy'];
$i++;
}
mysql_close($con);
?>
<div id="content">
<table>
<thead>
<tr>
<td><a href="table.php?column=id<?= (isset($_GET['column']) && $_GET['column'] == 'id' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">ID</a></td>
<td><a href="table.php?column=title<?= (isset($_GET['column']) && $_GET['column'] == 'title' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">Title</a></td>
<td><a href="table.php?column=rating<?= (isset($_GET['column']) && $_GET['column'] == 'rating' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">Rating</a></td>
<td><a href="table.php?column=download<?= (isset($_GET['column']) && $_GET['column'] == 'download' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">Download</a></td>
</tr>
</thead>
<tbody>
<? foreach($response as $i => $row) : ?>
<tr>
<td><?= $row['id']; ?></td>
<td><?= $row['title']; ?></td>
<td><?= $row['rating']; ?></td>
<td><?= $row['download']; ?></td>
</tr>
<? endforeach; ?>
</tbody>
</table>
</div>
The above code would go inside a single PHP file, without any other HTML etc. Then, on the page you want to display this table, you simply <? include('path-to-file.php'); ?>include it.
上面的代码将放在一个单独的 PHP 文件中，没有任何其他 HTML 等。然后，在要显示此表的页面上，您只需<? include('path-to-file.php'); ?>包含它。
Finally... At the top of the page you are displaying the table on, you would put:
最后......在您显示表格的页面顶部，您将放置：
<?
if(!empty($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest')
{
include('path-to-file.php');
die();
}
?>
The above code would then detect an Ajax request and serve only the table with the data in the new order.
然后上面的代码会检测到一个 Ajax 请求，并只为包含新顺序中的数据的表提供服务。
You would then need to use Javascript to replace the table with the new HTML via
然后，您需要使用 Javascript 通过新的 HTML 替换表格
$('#content table thead a').live('click', function(e)
{
e.preventDefault();
$.ajax(
{
url : $(this).attr('href'),
success : function(resp)
{
$('#content').html($(resp).html());
},
error : function()
{
alert('There was a problem sorting your table...');
}
});
});
where respis the variable that contains your Ajax response.
resp包含 Ajax 响应的变量在哪里。
Note: This is just a very simple and crude (oh, and untested) way to handle the situation. You would need to improve it your self to prevent any security related issues such as SQL Injection.
注意：这只是处理这种情况的一种非常简单粗暴（哦，未经测试）的方法。您需要自行改进以防止任何与安全相关的问题，例如 SQL 注入。
