HTML TABLE PHP MySQL在列标题点击时切换MySQL排序顺序ASC DESC [英] HTML TABLE PHP MySQL toggle MySQL sorting order ASC DESC on column header click
问题描述
编辑：从已接受的答案中添加了解决方案
我在这里问的提示是最聪明的方法。
首先需要说明的是：我恳请您避免提示数据包和类似信息。
我正在显示一个MySQL表HTML表格中的内容。
我的观点是 showdbtable.php ，它包含 echohtmltable.php 它查询数据库并回显表行。
因此，在 echohtmltable.php 目前这个问题中涉及到的所有内容都包含在内
GET变量检查
$ pre > if（isset（$ _ GET ['sortby']））{
$ sortby = $ _ GET ['sortby'];
$ _GET ['sortby'] =''; }
else {
$ sortby =id_ord; }
及后续查询
$ query =
SELECT iD_ORD，fornitore，negozio，data_insord，data_prevcons
FROM tesord
ORDER BY。$ sortby。DESC
LIMIT ：from_record_num，：records_per_page;
其次用于回显表行，并在列标题中创建排序机制
echo< th>。'< a href =showdbtable.php？sortby = fornitore>'。 Fornitore< /第> 中;
echo< th>。< a href =showdbtable.php？sortby = negozio>'。 Negozio< /第> 中;
echo< th> ;.'< a href =showdbtable.php？sortby = data_insord>'。 Data_insord< /第> 中;
echo< th> ;.'< a href =showdbtable.php？sortby = data_prevcons>'。 Data_prevcons< /第> 中;
你知道这第一部分是可行的。
当我点击上面的链接之一时，查询就可以工作，但目前在代码中很明显，仅在DESC中。
这种请求完全是一个建议。但请注意，我已经分页了，您应该考虑 LIMIT：from_record_num，：records_per_page ，
这是在ASC和DESC之间切换的最明智，最有效的方式，即点击一个链接后，例如Negozio，再次点击Negozio它会在ASC中排序，然后点击它将切换DESC，然后点击ASC等。
我确定该查询将会变异为具有一个变量，我可能会调用$ ascdesc
$ query =
SELECT id_ord，fornitore， negozio，data_insord，data_prevcons
从tesord
ORDER BY。 $ sortby。 。 $ ascdesc。
LIMIT：from_record_num，：records_per_page;如果当前是ASC或DESC并切换，则需要管理
。
感谢您暗示一些有效且智能的方法来实现此目标。
解决方案：感谢Rudy
为了帮助像我这样的新手，下面是我如何应用这个解决方案。
//链接中使用的函数
函数invdir（$ dir）{return（$ dir ==DESC）？ ASC：DESC; }
//自我解释，它反转链接中的排序字符串
//收集，清理和默认$ _GET变量。
// $ ordinaper是$ sortby的意大利语
$ ordinaper =（isset（$ _ GET ['ordinaper']））？ （filter_var（$ _ GET [ordinaper]，FILTER_SANITIZE_STRING））：id_ord;
$ ascdesc =（isset（$ _ GET ['ascdesc']））？ （filter_var（$ _GET [ascdesc]，FILTER_SANITIZE_STRING））：DESC;
// $ filtraforn是一个筛选器/搜索，只显示一个提供者，查看查询，它被分配了一个AJAX实时搜索
$ filtraforn =（isset（$ _ GET ['filtraforn']） ）？ （filter_var（$ _ GET [filtraforn]，FILTER_SANITIZE_STRING））：;
//构建公共URL GET部分
$ getlinks =& filtraforn =。$ filtraforn。& page =。$ page。& ascdesc =;
//变量$页面来自超出范围的分页。在这里，我正在处理对HTML表格列进行排序的正确管理
//查询是相应构建的，稍后用于PDO语句
$ query =SELECT id_ord，fornitore， negozio，data_insord，data_prevcons FROM tesord;
$ filtro =（strlen（$ filtraforn））？ WHERE fornitore ='。 $ filtraforn。 '：;
$ query = $ query。 $ filtro。 ORDER BY。 $ ordinaper。。 $ ascdesc。LIMIT：from_record_num，：records_per_page;
// LIMIT：from_record_num，：records_per_page稍后会与来自超出范围的分页的变量绑定。在这里，我正在处理对HTML表格列进行排序的正确处理。
//这里是最终表格标题
//三元运算符（（$ ordinaper！==id_ord ）？DESC：invdir（$ ascdesc）），因为当点击不同的列时，我想默认排序在DESC
echo< tr>;
echo< th>< / th>;
回显第>。 '（< a href =read.php？ordinaper = id_ord'。$ getlinks。（（ordinaper！==id_ord）？DESC：invdir（$ ascdesc））。'>'。 id_ord< /第> 中;
回显第>。 '（< a href =read.php？ordinaper = ord_evaso'。$ getlinks。（（ordinaper！==ord_evaso）？DESC：invdir（$ ascdesc））。'>'。 Stato< /第> 中;
回显第>。 '（< a href =read.php？ordinaper = fornitore'。$ getlinks。（（ordinaper！==fornitore）？DESC：invdir（$ ascdesc））。'>'。 Fornitore< /第> 中;
回显第>。 '（< a href =read.php？ordinaper = negozio'。$ getlinks。（（$ ordinaper！==negozio）？DESC：invdir（$ ascdesc））。'>'。 Negozio< /第> 中;
回显第>。 '（< a href =read.php？ordinaper = data_insord'。$ getlinks。（（$ ordinaper！==data_insord）？DESC：invdir（$ ascdesc））。'>'。 Data_insord< /第> 中;
回显第>。 '（< a href =read.php？ordinaper = data_prevcons'。$ getlinks。（（$ ordinaper！==data_prevcons）？DESC：invdir（$ ascdesc））。'>'。 Data_prevcons< /第> 中;
echoPaia Inev。< / th>;
echo< th> Azione< / th>;
回声< / tr>;
如果您已经发送
sortby
通过GET，为什么不发送
ASC，DESC
选项也是，我的意思是，你可以使用1和0代替实际的ASC / DESC，并在代码中切换它，例如：
$ ascdesc =（$ _GET ['ad']）？ '0'：'1';
只需将var添加到链接中即可
echo< th>。'< a href =showdbtable.php？sortby = negozio& ad ='。$ ascdesc。'>'。 Negozio< /第> 中;
在你的查询中，像
$ ascdesc =（$ _GET ['ad']）？ 'asc'：'desc';
这里非常重要的一点是，如果您通过GET接受用户输入，则必须清理var为避免SQL注入，请不要忘记这一点。
更新：
$ sortby =（isset（$ _ GET ['sortby' ]））？ $ _GET ['sortby']：id_ord;
$ ascdesc =（$ _GET ['ad'] =='asc'）？ 'ASC'：'DESC';
查询：
$ query =选择id_ord，fornitore，negozio，data_insord，data_prevcons
从tesord
ORDER BY。$ sortby。。$ ascdesc。
LIMIT： from_record_num，：records_per_page;
链接：
echo< th>'< a href =showdbtable.php？sortby = fornitore& ad =< = =（（$ _ GET ['ad'] =='asc'） ？'desc'：'asc';）？>>'。 Fornitore< /第> 中;
如果您需要添加页面，只需添加当前页面并更改排序，即可添加一个var也链接到
echo< th>。'< a href =showdbtable.php？sortby = fornitore& ad =<？php echo（（$ _GET ['ad'] =='asc'）？'desc'：'asc';）？>& page =<？php echo $ _GET [ 'page]？>>'。 Fornitore< /第> 中;
处理分页和排序有许多其他方法，但我认为，如果不进入很多麻烦，但这可能是一种方法，但是，有关安全性，您可以使用 mysql_real_escape
您也可以通过执行类似于这个
希望这能给你一个更好的理解，开心的编码。 b
EDIT: added solution from the accepted answer
The hint I'm asking here is the smartest method for.
Need to be said first: I kindly ask you to avoid suggesting databales and similar.
I'm displaying a MySQL table content in a HTML table.
I have the view that is showdbtable.php which uses as include echohtmltable.php which queries the database and echoes the table rows.
So in echohtmltable.php currently there is quite everything involved in this question
GET variable check
if(isset($_GET['sortby'])){
$sortby=$_GET['sortby'];
$_GET['sortby']=''; }
else {
$sortby="id_ord"; }
and the consequent query
$query = "
SELECT id_ord, fornitore, negozio, data_insord, data_prevcons
FROM tesord
ORDER BY ".$sortby." DESC
LIMIT :from_record_num, :records_per_page";
which next is used to echo the table rows creating also the sort mechanism in the columns headers
echo "<th>".'<a href="showdbtable.php?sortby=fornitore">' . "Fornitore</th>";
echo "<th>".'<a href="showdbtable.php?sortby=negozio">' . "Negozio</th>";
echo "<th>".'<a href="showdbtable.php?sortby=data_insord">' . "Data_insord</th>";
echo "<th>".'<a href="showdbtable.php?sortby=data_prevcons">' . "Data_prevcons</th>";
as you know this first part works.
When I click one of the above links, the query works but currently as obvious from the code, in DESC only.
The kind request is a suggestion altogether. But PLEASE also NOTE that I have pagination, you see, LIMIT :from_record_num, :records_per_page , that should be taken in consideration.
Which is the smartest and efficient way to toggle between ASC and DESC in such a way that after having clicked one link, e.g. "Negozio", clicking again "Negozio" it goes sorted in ASC, and next click it will toggle DESC, and next click ASC and so on.
I'm sure the query will mutate in having a variable I may call $ascdesc
$query = "
SELECT id_ord, fornitore, negozio, data_insord, data_prevcons
FROM tesord
ORDER BY " . $sortby . " " . $ascdesc . "
LIMIT :from_record_num, :records_per_page";
need to manage if currently is ASC or DESC and toggle.
Thank you for hinting on some efficient and smart method to achieve the goal.
Solution: thank you to Rudy
For the sake of helping others newbies like me, here is how I have applied the solution
// function used in the links
function invdir($dir){ return ($dir == "DESC")? "ASC" : "DESC"; }
// self explaining, it does invert the sort string in the link
// collect, sanitize and default $_GET variables.
// $ordinaper is the Italian of $sortby
$ordinaper = (isset($_GET['ordinaper']))? (filter_var($_GET["ordinaper"], FILTER_SANITIZE_STRING)) : "id_ord";
$ascdesc = (isset($_GET['ascdesc']))? (filter_var($_GET["ascdesc"], FILTER_SANITIZE_STRING)) : "DESC";
// $filtraforn is a filter/search to show only one provider, see the query, it is assigned with a ìn AJAX live search
$filtraforn = (isset($_GET['filtraforn']))? (filter_var($_GET["filtraforn"], FILTER_SANITIZE_STRING)) : "";
// build the common URL GET part
$getlinks = "&filtraforn=".$filtraforn."&page=".$page."&ascdesc=";
// the variable $page comes from the pagination which is out of the scope. Here I was dealing with the correct management of sorting the HTML table columns
// the query is built accordingly, later is used in a PDO statement
$query = "SELECT id_ord, fornitore, negozio, data_insord, data_prevcons FROM tesord ";
$filtro = (strlen($filtraforn))? "WHERE fornitore = '" . $filtraforn . "' " : "";
$query = $query . $filtro . "ORDER BY ". $ordinaper ." ". $ascdesc ." LIMIT :from_record_num, :records_per_page";
// LIMIT :from_record_num, :records_per_page are bound later with variables coming from the pagination which is out of the scope. Here I was dealing with the correct management of sorting the HTML table columns
// and here it is the final table heading
// the ternary operator (($ordinaper!=="id_ord")? "DESC" : invdir($ascdesc)) is used because when clicking a different column, I want to default the sorting in DESC
echo "<tr>";
echo "<th></th>";
echo "<th>". '<a href="read.php?ordinaper=id_ord' .$getlinks. (($ordinaper!=="id_ord")? "DESC" : invdir($ascdesc)) .'">' . "id_ord</th>";
echo "<th>". '<a href="read.php?ordinaper=ord_evaso' .$getlinks. (($ordinaper!=="ord_evaso")? "DESC" : invdir($ascdesc)) .'">' . "Stato</th>";
echo "<th>". '<a href="read.php?ordinaper=fornitore' .$getlinks. (($ordinaper!=="fornitore")? "DESC" : invdir($ascdesc)) .'">' . "Fornitore</th>";
echo "<th>". '<a href="read.php?ordinaper=negozio' .$getlinks. (($ordinaper!=="negozio")? "DESC" : invdir($ascdesc)) .'">' . "Negozio</th>";
echo "<th>". '<a href="read.php?ordinaper=data_insord' .$getlinks. (($ordinaper!=="data_insord")? "DESC" : invdir($ascdesc)) .'">' . "Data_insord</th>";
echo "<th>". '<a href="read.php?ordinaper=data_prevcons' .$getlinks. (($ordinaper!=="data_prevcons")? "DESC" : invdir($ascdesc)) .'">' . "Data_prevcons</th>";
echo "<th>Paia Inev.</th>";
echo "<th>Azione</th>";
echo "</tr>";
If you are already sending the sortby via GET, why wouldn't you send the ASC,DESC Option too, I mean, you could use 1 and 0 instead of the actual ASC/DESC and toggle it inside your code,for example:
$ascdesc = ($_GET['ad'])? '0' : '1';
And just add the var to the link
echo "<th>".'<a href="showdbtable.php?sortby=negozio&ad='.$ascdesc.'">' . "Negozio</th>";
And in you query, something like
$ascdesc = ($_GET['ad'])? 'asc' : 'desc';
Something very important here is that if you are accepting user input via GET, you have to sanitize the var to avoid SQL injections, don't forget this.
UPDATE:
Possible implementation with your own code:
$sortby = (isset($_GET['sortby']))? $_GET['sortby'] : "id_ord";
$ascdesc = ($_GET['ad']=='asc')? 'ASC' : 'DESC';
The Query:
$query = "SELECT id_ord, fornitore, negozio, data_insord, data_prevcons
FROM tesord
ORDER BY ".$sortby." ".$ascdesc."
LIMIT :from_record_num, :records_per_page";
The link:
echo "<th>".'<a href="showdbtable.php?sortby=fornitore&ad=<?=(($_GET['ad']=='asc')? 'desc' : 'asc';)?>">' . "Fornitore</th>";
And if you need to add the page, just add the current page and change sort, you can add a var also to the link
echo "<th>".'<a href="showdbtable.php?sortby=fornitore&ad=<?php echo (($_GET['ad']=='asc')? 'desc' : 'asc';)?>&page=<?php echo $_GET['page]?>">' . "Fornitore</th>";
There are many other ways to handle the pagination and sorting, but I think that, without getting into a lot of trouble, this could be a way, however, about the security, you can use mysql_real_escape
You could also leave everything to javascript/jQuery by implementing something like this
Hope this can give you a better understanding, happy coding.
这篇关于HTML TABLE PHP MySQL在列标题点击时切换MySQL排序顺序ASC DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
