Page 1 of 1

Alphabetical Ordering from a database

Posted: Wed Feb 17, 2010 11:35 am
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

Re: Alphabetical Ordering from a database

Posted: Wed Feb 17, 2010 2:55 pm
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.

Re: Alphabetical Ordering from a database

Posted: Wed Feb 17, 2010 3:09 pm
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.