sort list by first letter of last name

Hello everyone. I am trying to sort a member list by the first letter of a members last name. To do so, I have a link for each letter of the alphabet as follows:

<a href='members/users.php?action=Directory&sort=letter&letter=A'>A</a>

Then, I have a conditional statement which I use to choose the correct mysql_query:

if($sort == "letter"){
$result = mysql_query("SELECT * FROM ".$prefix."_users ORDER BY lname ASC LIMIT $nexlimit,$users_per_page WHERE lname LIKE '".$letter."%' ");
};

This query works if I remove the where statement. However, I cannot figure out why I receive an invalid argument statement when clicking on one of the letters to sort the list.

Any advice?

Thanks in advance,

Ryan

What I do when I get errors like this is I echo the text of the query to see if the variable substitutions I'm making are legitimate. You'll have to separate the query text from the mysql_query() function to do this but I think it's worth it.

if($sort == "letter"){

$query = "SELECT *
FROM ".$prefix."_users
ORDER BY lname ASC
LIMIT $nexlimit,$users_per_page
WHERE lname LIKE '".$letter."%' ";

print "Query: $query<br>\n";

$result = mysql_query($query);
}


If the query looks solid, then copy/paste it off your browser into an ad-hoc SQL query and see if it runs ok outside the script.

P.S. the semi-colon after the closing bracket may be the thing giving you an error; it's not supposed to be there.

Fumigator,

Thanks for the help. When I separate the query, I get the following returned:

Query: SELECT * FROM members_users ORDER BY lname ASC LIMIT 0,20 WHERE lname LIKE 'A%'

When I run this query outside the script using phpMyAdmin, I get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE lname LIKE 'A%' LIMIT 0, 30' at line 1

So what I did from there was change the order of my query components to read as follows:

$result = mysql_query("SELECT * FROM ".$prefix."_users WHERE lname LIKE '".$letter."%' ORDER BY lname ASC LIMIT $nexlimit,$users_per_page");

For reasons still beyond me, that completely fixed the problem. I chose to post all this information for reference to others.

Again, thanks for everything!


Ryan

Ah yes, I didn't catch that-- the order of each statement does matter. I'm glad you worked it out :)