PHP - 循环A-Z使用动态链接对Mysql结果进行排序

I have the following table in mysql:-

我在mysql中有以下表: -

id | bandname
1  | a perfect circle
2  | aerosmith
3  | b.b king
4  | cat stevens

I am fetching all of the results in a single query with:-

我在一个查询中获取所有结果: -

$result = mysql_query("SELECT id, bandname FROM bands ORDER BY bandname ASC");

On my page I have A-Z anchor links which bring up a new tab:-

在我的页面上,我有一个A-Z锚点链接,它会显示一个新标签: -

    <ul class="tabs-nav">
        <li class="active"><a href="#0">0-9</a></li>
    <?php
        // Print a-z link
        for ($i=97; $i<=122; $i++) {
         $curletter = chr($i);
         echo '<li><a href="#'.$curletter.'" title="Band names beginning with letter '.$curletter.'" class="uppercase">'.$curletter.'</a></li>';
        }
    ?>
    </ul>

I'm having trouble getting the bands listed under their tab. Currently my code is:-

我在选项卡下列出的乐队时遇到了麻烦。目前我的代码是: -

    <div class="tabs-container">
    <?php
        // Print a-z tabs
        for ($i=97; $i<=122; $i++) {
         $curletter = chr($i);
    ?>
        <div class="tab-content" id="<?php echo $curletter; ?>">
            <?php
            while($bands = mysql_fetch_array( $result )) {
                $bandname = $bands['bandname'];
                $bandid = $bands['id'];
                $bandletter = strtolower(substr($bandname , 0 , 1));
            }
            if($curletter==$bandletter) {
                echo '<a href="'.$bandid.'/" title="'.$bandname.'>'.$bandname.'</a>';
            }
            ?>
        </div>
    <?php } ?>
    </div>

I know this is incorrect because I'm calling the while loop inside the for loop and that just doesn't seem right to me - as every iteration of the A-Z tab creation process will have to run through the while loop.

我知道这是不正确的,因为我在for循环中调用while循环,这对我来说似乎不对 - 因为A-Z制表过程的每次迭代都必须通过while循环运行。

If I will be dealing with say 5,000 bands what is the best approach for this? One sql result looped multiple times, one result with each band then held in an alphabetical array, or an ajax sql query whenever a user clicks on one of the anchor links?

如果我要处理5,000个乐队,那么最好的方法是什么?一个sql结果循环多次,每个band的一个结果然后保存在一个字母数组中,或者每当用户点击其中一个锚链接时ajax sql查询?

None of this even starts to deal with the 0-9 tab which I think will be an issue itself given my current code. Any pointers really would be appreciated.

这甚至没有开始处理0-9选项卡,我认为鉴于我目前的代码,我认为这将是一个问题。任何指针真的很感激。

I have searched for an answer but not found something similiar to my question :)

我已经找到了答案,但没有找到与我的问题类似的东西:)

1 个解决方案

#1

Though I wouldn't recommend the method you've used at all, if you want to keep it as simple as you have then you need to extract all of the band names using the while before you enter the name looping foreach by putting them into a temporary array, or better still before any output takes place. This will allow you to use foreach() on this array instead of the while. You could also sort these into arrays by letter too, making it simpler to see which letters have bands that start with it

虽然我不推荐你所使用的方法,如果你想保持它的简单,那么你需要在输入名称循环foreach之前使用while提取所有的band名称,方法是将它们放入一个临时数组,或者更好地在任何输出发生之前。这将允许您在此数组上使用foreach()而不是while。您也可以按字母对这些数组进行排序,这样可以更简单地查看哪些字母带有以它开头的带

Some extra tips:

• Use range() instead of the for($i = ... ) and chr() for the letters listings

对于字母列表,使用range()而不是for($ i = ...)和chr()

• Give your href values more than just the letter as the id (aesthetic but certainly better) such as letter-a letter-b and so on

给你的href值不仅仅是字母作为id(美学但肯定更好),例如letter-a letter-b等等

• If you're using 5000+ records you should be serving these on separate pages in my opinion with pagination rather than on one large page of bands

如果你使用的是5000多条记录,你应该在我看来在分页上提供这些记录,而不是在一大页乐队中

• If these are not going to change very often, you'd be best using a caching system of some sort to keep database querying to a minimum

如果这些不会经常发生变化,那么最好使用某种缓存系统将数据库查询保持在最低限度

• If you use the array by letter method I've given above, you can use the count() function to add the band number counts to the letter listings (and even hiding letters you have no bands for)

如果你使用我上面给出的逐字母方法,你可以使用count()函数将字母数字计数添加到字母列表中(甚至隐藏你没有字母的字母)