如何使用Ajax Json PHP& MySQL的 [英] How to apply sorting and filtering using Ajax Json PHP & MySQL
问题描述
您好，我是一名初学者，我在软件网站工作。我已经构建了网站的所有页面和布局，使用HTML CSS和JAVASCRIPT单独完成了简单的部分，剩下的只是为不同的软件制作主要类别页面，这对我来说很难。
我想在类别页面上添加排序选项，例如（请参见此处）
用户应能根据日期，名称，添加日期等进行分类即可。并且还能够控制最大显示软件数量，如20,30,100等。
>internet_security（它是一个测试表）
HTML Div's
< div class =category-container>
< div class =category-image>< / div>
< div class =category-desc>< a href =＃>＃< / a>< p> text< / p>< / div>
< div class =rating5>编辑评分：< / div>
< div class =category-download-btn>< a href =＃>下载< / a>< / div>
< div class =category-buy-btn>< a href =＃>购买< / a>< / div>
< / div>
经过一些研究，我得到了一个使用JSON AJAX PHP & MySQL
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 +'>下载< / a> ;< / div>'+
'< div class =category-buy-btn>< a href ='+ val.buy +'> Buy< / a>< （< div>'）。attr（'id'，i）.html（data）.appendTo（'＃response'）;
}）;
}
}）;
< / script>
< / head>
< body>
< div id ='response'>< / div>
< / body>
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）;
？>
现在它不工作，因为我没有任何地方可以附上这些代码（分类下拉）在JavaScript中。
< form>
< select name =usersonchange =showUser（this.value）>
< option value =>选择一个人：< / option>
< option value =id> id< / option>
< option value =title> title< / option>
< option value =image>图片< / option>
< option value =description> description< / option>
< option value =description> rating< / option>
< option value =下载>下载< / option>
< option value =buy> buy< / option>
< / select>
< / form>
请帮助我们在哪里添加这些代码以及如何让它工作，我完全困惑。
首先值得注意的是，如果您要显示表格数据...使用表格！它会让事情变得容易许多。其次。
其次。像Ajax不存在一样构建您的代码和表。最初使用PHP在显示数据的页面上填充数据。然后，将列标题连接起来，以便它们链接到您的页面，但要传递您想要排序的列以及哪个方向。
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。'。 $方向;
$ 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'：''）;？>>标题< / a>< / td>
< td>< a href =table.php？column = rating<？=（isset（$ _ GET ['column']）&& $ _GET ['column'] =='rating '&&！isset（$ _ GET ['direction']）？'& direction = desc'：''）;？>>评分< / a>< / td>
< td>< a href =table.php？column = download<？=（isset（$ _ GET ['column']）&& $ _GET ['column'] =='download '&&！isset（$ _ GET ['direction']）？'& direction = desc'：''）;？>>下载< / 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>
上面的代码将放在一个PHP文件中，不包含任何其他的HTML等。您想要显示此表格的页面，只需< ;?包括（路径到file.php）; ？> 包含它。
最后...在页面顶部显示表格，您可以输入：
<？
if（！empty（$ _ SERVER ['HTTP_X_REQUESTED_WITH']）&& strtolower（$ _ SERVER ['HTTP_X_REQUESTED_WITH']）=='xmlhttprequest'）
{
include（'path -to-file.php'）;
die（）;
}
？>
然后，上面的代码会检测到一个Ajax请求，并且只为新数据中的数据提供表。
然后，您需要使用Javascript通过
$ b live（'click'，function（e）
{
e.preventDefault（）;
$ .ajax（（））{code> $（'＃content table thead a'）。
{
url：$（this）.attr（'href'），
success：function（resp）
{
$（'＃content'）。 html（$（resp）.html（））;
}，
错误：function（）
{
alert（'排序表时出现问题......' ）;
}
}）;
}）;
其中 resp 是包含您的变量Ajax响应。
注意：这只是一种非常简单而粗略的（哦，未经测试）处理这种情况的方式。您需要改进自己以防止任何与安全相关的问题，如SQL注入。
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.
i want to to add sorting option on category pages 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.
On my HTML Page i have these div's in 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's
<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
JAVASCRIPT Code i have
<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
$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.
<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.
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.
i.e.
<?
$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.
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.
You would then need to use Javascript to replace the table with the new HTML via
$('#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 resp is the variable that contains your Ajax response.
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.
这篇关于如何使用Ajax Json PHP& MySQL的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
