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 8 of 8
  1. #1
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts

    Query not working right

    I'm trying to get users with the refer field having a value more than 0 to show. That works ok, I'm also trying to arrange this in date order, so if everyone has 1 in their refer field, the person that had the refer field filled in last will show at the top of the list.
    PHP Code:
    select from cpusers where refer>0 order by refer and refertime desc limit 0,10 
    I'm trying to get the list to show like this:

    Username1 - 2 referrers
    Username2 - 1 referrer
    Username3 - 1 referrer

    Username1 will always be above the users with 1 referrer, Username2 is above Username3 as he/she got the referrer after Username3 did.

    Make sense?

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    if you want to sort is you might want to put the ORDER BY refertime DESC in there. AND operator is not what you want there.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Senior Coder
    Join Date
    Aug 2002
    Posts
    3,467
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I'm not mistaken, you can separate ORDER BY fields with a comma. They are given precendence in order from left to right...
    PHP Code:
    SELECT FROM cpusers WHERE refer>0 ORDER BY referrefertime DESC LIMIT 0,10 
    My Site | fValidate | My Brainbench | MSDN | Gecko | xBrowser DOM | PHP | Ars | PVP
    “Minds are like parachutes. They don't work unless they are open”
    “Maturity is simply knowing when to not be immature”

  • #4
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts
    Thanks beetle That was the one that did the trick

  • #5
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts
    I spoke too soon. The DESC or ASC doesn't seem to work in the query. I'm getting:

    Username3 - 1 referrer
    Username2 - 1 referrer
    Username1 - 3 refferers

    Username3 should be at the bottom and Username1 should be at the top, as Username1 has more referrers and Username2 had a referrer after Username3

  • #6
    Senior Coder
    Join Date
    Aug 2002
    Posts
    3,467
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you testing your queries? Use phpMyAdmin or something like DBTools. That way you can test your queries standalone without having to stuff them into any PHP, and gives you a good look at the raw data that is returned...
    My Site | fValidate | My Brainbench | MSDN | Gecko | xBrowser DOM | PHP | Ars | PVP
    “Minds are like parachutes. They don't work unless they are open”
    “Maturity is simply knowing when to not be immature”

  • #7
    New Coder
    Join Date
    Oct 2002
    Location
    nj
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found this in MySql.com, I don't really know if this has something to do with your query:

    If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.

    If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.


    Thanks
    Ray

  • #8
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts
    I'm using phpMyAdmin. I've tried everything I can think of, and everything I was given to me to try, nothing seemed to do it, so I'm just using it to list refer in order, instead of refertime too.

    SELECT * FROM cpusers WHERE refer>0 ORDER BY refer DESC LIMIT 0,10


  •  

    Posting Permissions

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