Alphabetical Ordering from a database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
TravisT6983
Forum Newbie
Posts: 19
Joined: Wed Jul 02, 2008 2:15 pm

Alphabetical Ordering from a database

Post by TravisT6983 »

I am trying to figure out how to alphabetize a client list that draws from my data base here is the code i am playing with if any one could help nudge me ;) in the right direction i would be forever grateful

Code: Select all

$query = "SELECT id, firstName, email, lastName, LEFT(lastName, 1) AS first_char FROM userAccounts WHERE UPPER(LEFT(lastName, 1)) BETWEEN 'A' AND 'Z' OR LEFT(lastName, 1) BETWEEN '0' AND '9' ORDER BY lastName WHERE assignedAgent = ".$_SESSION['agent']['agentID'];
                                $clients = mysql_query($query);
                                $current_char = '';
                                if( mysql_num_rows($clients) > 0 )
                                {
                                    while( $client = mysql_fetch_array($clients) )
                                    {
                                      if ($client['first_char'] != $current_char) {
                                      $current_char = $client['first_char'];
                                      echo '<br />' . strtoupper($current_char) . '<br />-----<br />';
                                  }
                                        echo "<tr>";
                                        echo "  <td>".$current_char['lastName']."</td>";
                                        echo "  <td>".$client['firstName']."</td>";
                                        echo "  <td>".$client['email']."</td>";
                                        echo "  <td>".$client['group']."</td>";
                                        echo "  <td align='center'><a href=\"editClient.php?clientID=".$client['id']."\">Edit Client</a></td>";     
                                        //echo "    <td><a href='clientWebforms.php?clientID=".$client['id']."'>View Webforms</a></td>";
                                        echo "</tr>";   
                                    }
                                }
                                else
                                    echo "<tr><td colspan='3' align='center'><font color='red'>You have no clients assigned!  To have your clients assigned to you search for them using the 'Find Clients' button on the left navigation bar.</font></td>";
                       


Thanks for any and all comments

Travis
User avatar
chopsmith
Forum Commoner
Posts: 56
Joined: Thu Nov 13, 2008 10:40 am
Location: Red Bank, NJ, USA

Re: Alphabetical Ordering from a database

Post by chopsmith »

The MySQL DBMS already alphabetizes for you when you use "ORDER BY lastName".

You do this:

Code: Select all

 
$query = "SELECT id, firstName, email, lastName, LEFT(lastName, 1) AS first_char FROM userAccounts WHERE UPPER(LEFT(lastName, 1)) BETWEEN 'A' AND 'Z' OR LEFT(lastName, 1) BETWEEN '0' AND '9' ORDER BY lastName WHERE assignedAgent = ".$_SESSION['agent']['agentID'];
 
Without completely understanding your data, I would suggest just doing this and seeing if you get the desired result:

Code: Select all

 
$query = "SELECT id, firstName, email, lastName from userAccounts ORDER BY lastName ASC WHERE assignedAgent = " . $_SESSION['agent']['agentID'];
 
Are you expecting that some of your lastName data elements might begin with something other than a letter or number? Why would they even start with numbers?

Anyway, if you do the above, your result should be nicely alphabetized and you don't have to worry about doing it in PHP. I must say, though, that I may not be sure what you're trying to do.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Alphabetical Ordering from a database

Post by AbraCadaver »

I haven't picked through the query, but you probably need the ORDER BY at the end, after all the WHERE conditions. And get rid of the PHP code that is trying to alphabetize.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply