Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2005
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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:

    Code:
    <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:

    PHP Code:
    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.
    PHP Code:
    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.

  • #3
    New Coder
    Join Date
    Jan 2005
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Ah yes, I didn't catch that-- the order of each statement does matter. I'm glad you worked it out


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •