[SOLVED] Creating a-z list for users by their usernames first letter

[SocomNegotiator]

Well I am trying to make an a-z list that will show all of my users usernames in the a-z list by the first letter of their username. I have tried a lot of different things, and I can't seem to get it to work. Any help would be much appreciated. Here is the code I have now which isn't working:

 

<?php

$_root = './';
require_once($_root . '_core/common.php');

$skin->initiate();
?>



<table width='450' border='0' cellspacing='1' cellpadding='2' bgcolor='#000000'>
<tr bgcolor="#666666">




<td width='450' valign='center'  background="" align='center' colspan='5'><b><font face='verdana' size='-2' color='#ffffff'>  
<a href='http://somewhere.com/members.php?letter=a'>A</a> | <a href='http://somewhere.com/members.php?letter=b'>B</a> | <a href='http://somewhere.com/members.php?letter=c'>C</a> | <a href='http://somewhere.com/members.php?letter=d'>D</a> | <a href='http://somewhere.com/members.php?letter=e'>E</a> | <a href='http://somewhere.com/members.php?letter=f'>F</a> | <a href='http://somewhere.com/members.php?letter=g'>G</a> | <a href='http://somewhere.com/members.php?letter=h'>H</a> | <a href='http://somewhere.com/members.php?letter=i'>I</a> | <a href='http://somewhere.com/members.php?letter=j'>J</a> | <a href='http://somewhere.com/members.php?letter=k'>K</a> | <a href='http://somewhere.com/members.php?letter=l'>L</a> | <a href='http://somewhere.com/members.php?letter=m'>M</a> | <a href='http://somewhere.com/members.php?letter=n'>N</a> | <a href='http://somewhere.com/members.php?letter=o'>O</a> | <a href='http://somewhere.com/members.php?letter=p'>P</a> | <a href='http://somewhere.com/members.php?letter=q'>Q</a> | <a href='http://somewhere.com/members.php?letter=r'>R</a> | <a href='http://somewhere.com/members.php?letter=s'>S</a> | <a href='http://somewhere.com/members.php?letter=t'>T</a> | <a href='http://somewhere.com/members.php?letter=u'>U</a> | <a href='http://somewhere.com/members.php?letter=v'>V</a> | <a href='http://somewhere.com/members.php?letter=w'>W</a> | <a href='http://somewhere.com/members.php?letter=x'>X</a> | <a href='http://somewhere.com/members.php?letter=y'>Y</a> | <a href='http://somewhere.com/members.php?letter=z'>Z</a>
</b></td>
</tr>





</table>


<br><br />


<table width='450' border='0' cellspacing='1' cellpadding='2' bgcolor='#000000' align='left'>
<tr bgcolor="#CCCCCC">
<td width='450' valign='center'  background="" align='left' colspan='5'><b><font face='verdana' size='-2' color='#ffffff'>  Members</b></td>
</tr>
<tr bgcolor="#666666">
<td width='55%' valign='center'  bgcolor='' align='left'><b><font face='verdana' size='-2' color='#ffffff'>Username</b></td>

<td width='30%' valign='center' bgcolor='' align='left'><b><font face='verdana' size='-2' color='#ffffff'>Date Joined</b></td>
</tr>

<?php

$members=mysql_query("SELECT username, first_name, last_name, date_joined FROM ls_users WHERE username BETWEEN 'A' AND 'Z' ORDER BY username ASC");
while(list($username, $first_name, $last_name, $date_joined) = mysql_fetch_row($members)) {

if ($members['username'] == $_GET['letter']) {
?>
<tr bgcolor="#666666">
<td width='5%' valign='center' align='left'><font face='verdana' size='-1'><?php echo $username; ?></font></td>
<td width='30%' valign='center' align='left'><font face='verdana' size='-1'><?php echo $date_joined; ?></font></td>
</tr>
<?php
}
}
?>


</table>


<br>

<?
$skin->compile();
?>

[chronister]

Are you wanting to show only the first letter? If so here is a few lines of code that will do it for ya

 

<?php
$query="SELECT DISTINCT LEFT(lastname,1) AS firstletter FROM TABLENAME WHERE lastname!='' ORDER BY lastname ASC";
$result=mysql_query($query);  
  if ($num = mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) { 
?>
<a href="/address_book/index.php?sort=<?=$row['firstletter'] ?>"><?=$row['firstletter'] ?></a> | 
<?php
      
    }//endwhile
  }//endif $num
?>

 

If not, lemme know and we can look into it more

 

[SocomNegotiator]

Well I am wanting to query the DB for the users usernames....based on their first letter they will be put on the a-z list accordingly. So when I click on B from the a-z list it will show all of the users full username that start with the letter "B", and then also some other info. on that user like their first name, last name, etc.

[SocomNegotiator]

If you notice how my page is set^ I first create the a-z list and then add a parameter at the end that equals the letter that you are choosing. From there I am trying to match match the letter chosen with the first letter of the username....So simply when I click on A it will grab all of the usernames that start with A, and so on with the rest of the alphabet. The way you have the code looks like you have a page for each letter which I want to use the same page, but change the result based on the letter picked.

[chronister]

All you need to do is query the db and get results that are like your sort term

 

$query ="SELECT * FROM addresses where lastname LIKE '".$sort."%' ORDER BY firstname asc ";

 

 

This query and what I posted previously are from the same address book system. The first part grabs all letters from the database. If there is no one with a lastname that begins with a letter, it is not shown. If it is then it is added to the list. So my a-z list may look like this 

 

A | B | E | F | L | X | Y | Z | Show All

 

 

It skips letters if there are not results that begin with that letter.

 

The query I posted above will take the sort letter and find all people whose name begins with that letter.

the

LIKE '".$sort."%' 

is the key. The $sort is the letter I am sorting by, and the % sign is a wildcard character.  So if I have selected B as my sort term, the query ends up

 

LIKE B% ....... It will find all items where the word begins with a B.

 

I hope this helps you and don't confuse you.

 

Nate

[SocomNegotiator]

Oh I totally understand what your talking about...let me put this together and see if I can get it to work, oh and by the way thanks for your time.

[svivian]

If I understand correctly what you're trying to do, it looks like you have quite a few errors in your code. I think the bit in the middle should be:

 

<?php
$members = mysql_query("SELECT username, first_name, last_name, date_joined FROM ls_users WHERE username LIKE '{$_GET[letter]}%' ORDER BY username ASC");

while(list($username, $first_name, $last_name, $date_joined) = mysql_fetch_row($members)) {

?>

 

And remove the extra } from the next php block.

Also note that you can't do things like $members['username'] as you were trying to do in your previous code. $members a resource variable, not an array.