php 按字母顺序排列 mysql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2003666/
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
Order a mysql query alphabetically
提问by Uffo
Let's say that I have the following code:
假设我有以下代码：
SELECT * FROM table where company LIKE '%Auto%'
And I receive more results, and I want to have an option to sort the results alphabetically, let's say that the user wants to sort the search results for the ones which start with "C"!
我收到了更多的结果，我想有一个按字母顺序对结果进行排序的选项，假设用户想要对以“C”开头的搜索结果进行排序！
Best Regards,
此致，
回答by bendewey
Well, it seems that you are talking about two different things. If you are interested in sorting you would need to use the ORDER BY clause:
好吧，看来您在谈论两种不同的事情。如果您对排序感兴趣，则需要使用 ORDER BY 子句：
SELECT * FROM table ORDER BY name
If you want to filter the results by items that start with the letter 'C' then you would want to add another LIKE clause with that letter:
如果要按以字母 'C' 开头的项目过滤结果，则需要添加另一个带有该字母的 LIKE 子句：
SELECT * FROM table where company LIKE '%Auto%' AND name LIKE 'C%'
Additionally you'll notice that the name filter only has the %after the query. This is the syntax for "starts with"
此外，您会注意到名称过滤器只有%在查询之后。这是“开始于”的语法
回答by wallyk
Use the ORDER BYclause:
使用ORDER BY子句：
SELECT *
FROM table
where company LIKE '%Auto%'
order by company
回答by Ned Batchelder
add ORDER BY company, assuming you want to sort by the company value.
add ORDER BY company，假设您想按公司价值排序。
回答by sunil pawar
try it..... its my working examplesorting the record alphetical order A B C D....Z when i click A Letterthen its show all name start with A letter,and click C Letter then its show all name start with C letter
试试看..... 这是我的工作示例，当我单击 A 字母时，对记录字母顺序 ABCD....Z 进行排序，然后显示所有名称以 A 字母开头，然后单击 C 字母，然后显示所有名称以 C 字母开头
<?php
$host = "localhost";
$user = "root";
$pw = "";
$database = "test";
$con = mysql_connect($host,$user,$pw)
or die("Cannot connect to mySQL.");
mysql_select_db($database,$con)
or die("Cannot connect to database.");
$errormsg= "No Record Found...!";
$alpha="%";
if (isset($_REQUEST['alpha'])) {
$alpha = $_REQUEST['alpha']."%";
}
$q1 = mysql_query("select * from registration where firstname like '$alpha%' " );
?>
<div >
<form action="" method="post" >
<table >
<td><div style="float:left;">
<CENTER>
<FONT COLOR=Green>Sort by Alphabet:</FONT>
<A HREF="<?php echo $_SERVER['PHP_SELF']; ?>">All</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=A">A</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=B">B</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=C">C</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=D">D</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=E">E</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=F">F</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=G">G</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=H">H</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=I">I</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=J">J</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=K">K</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=L">L</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=M">M</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=N">N</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=O">O</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=P">P</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=Q">Q</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=R">R</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=S">S</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=T">T</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=U">U</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=V">V</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=W">W</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=X">X</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=Y">Y</A>
<FONT COLOR=Green>|</FONT> <A HREF="?alpha=Z">Z</A>
</CENTER>
</div>
</td></tr>
<tbody>
<?php
$i=1;
$num_rows=mysql_num_rows($q1);
if($num_rows==0)
{
?>
<div align="center">
<div class="alert alert-danger alert-dismissable">
<?php echo $errormsg; ?></div>
</div>
<?php
}
else
{
while($roww=mysql_fetch_array($q1))
{
?>
<tr>
<td><div style="height:100px;float:left;">
<input name="" type="checkbox" value="">
</div>
<div><a href="#"> <b><?php echo $roww["firstname"]; ?></b></a><br />
: <?php echo $roww["firstname"]; ?><br />
<?php echo $roww["lastname"]; ?><br />
<?php echo $roww["gender"]; ?><br />
</div></td>
</tr>
<?php }
$i++;
}
?>
</tbody>
</table>
</form>
</div>
</body></html>
<SCRIPT LANGUAGE=JavaScript>
<!--
document.write(ALPHABET())
//-->
</SCRIPT>
CREATE TABLE IF NOT EXISTS `registration` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(20) NOT NULL,
`lastname` varchar(20) NOT NULL,
`gender` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
running code
运行代码
