SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    BoOm-Rocka! Smarky's Avatar
    Join Date
    Jun 2000
    Location
    England
    Posts
    1,319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,
    I am doing a fantasy game site now i have to work out teams points and then give them a postion that they are in the fantasy league. I guess how to do this is get the points in a table and sort them in order. But then how do i get there postion in the score table?

    Also if i only want to show 10 rows of a database on one page so it doesn't get to crowed and have the user click the next button for the rest how do i do this

    Thanks

    I have build a site from reading Kevin's stuff on sitepoint and help in these forums and other guess work if your a formula 1 fan you might want to check out this fantasy f1 league. Please don't suggest improvements for the site I am well aware of shortcomings and am already addressing them The address is www.rapidf1.com
    Last edited by Smarky; Feb 28, 2001 at 11:53.
    Garlic bread, I've tasted it, it's the future

  2. #2
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have their points in a MySQL table, you can just use a select statement such as....

    SELECT * FROM scores ORDER BY points DESC

    That would sort the list from highest score to lowest score. Then to get the position, you could use the following.

    a=1;

    begin screen output loop here

    print a; // wherever you want the poisition printed.

    a++; // increments the value of a by 1.

    end output loop

    rest of code...


    It's not the most detailed run-through, I know, but you get the gist. If not, I'll be back later.

  3. #3
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am doing a fantasy game site now i have to work out teams points and then give them a postion that they are in the fantasy league. I guess how to do this is get the points in a table and sort them in order. But then how do i get there postion in the score table?
    Yes, you need to store the points in a table. You could make your table look like this

    MemberID Score

    Store the scores. And then when printing out the output of the best 10 etc, you can sort and print. You could also, cross reference the scores for the team info by the common field MemberID.

    Also if i only want to show 10 rows of a database on one page so it doesn't get to crowed and have the user click the next button for the rest how do i do this
    You can do this by using the LIMIT statement. So your SQL statement will look something like this
    SELECT * FROM tablename WHERE (whatever=$whatever) LIMIT $offest, $limitingvalue

    here
    $offset = Starting row.
    $limitingvalue = Number of rows to return.

    Hope this helps.
    Last edited by kunal; Mar 1, 2001 at 06:52.
    i dunno...

  4. #4
    BoOm-Rocka! Smarky's Avatar
    Join Date
    Jun 2000
    Location
    England
    Posts
    1,319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, thanks for your help but how would i work out the postion of the player in the score tabe so say for example on the front page it tells the player what postion they are in the league.

    Thanks
    Garlic bread, I've tasted it, it's the future

  5. #5
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you could store his UserID in a cookie. And then, check for the UserId against the table?
    i dunno...

  6. #6
    BoOm-Rocka! Smarky's Avatar
    Join Date
    Jun 2000
    Location
    England
    Posts
    1,319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah thatswhat i am doing usedid is in a php session

    but what i want is something like - "You are placed 5th" or something on the first page

    how i count what number of row the user is on so i know what place they are.

    This is if i am correct in thinking that the way to do this is to sort the table by score and then count what row the users score is on giving them there place in the league
    Garlic bread, I've tasted it, it's the future

  7. #7
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by velocity
    ...This is if i am correct in thinking that the way to do this is to sort the table by score and then count what row the users score is on giving them there place in the league
    Yup, this is how you would have to do it. KMxRetro, covered how to implement this in his post.
    i dunno...

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because mySQL doesn't support sub-queries you would need two queiries:

    PHP Code:
    // get the member's points
    $result mysql_query"SELECT points FROM table WHERE memberID = $memberID);
    $row mysql_fetch_array($result);
    $membPoints $row["points"];

    // work out the member's ranking
    $sql " SELECT COUNT(points) AS rank
                  FROM table
                  WHERE points > 
    $membPoints ";

    $result mysql_query$sql);
    $row mysql_fetch_array($result);
    $ranking $row["rank"] + 1

  9. #9
    BoOm-Rocka! Smarky's Avatar
    Join Date
    Jun 2000
    Location
    England
    Posts
    1,319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    huh whats going on KMxRetro post wasn't there and now its appeared as a the first reply hmmmm thanks for your help
    Garlic bread, I've tasted it, it's the future

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    velocity, I just re-read your original post. For the solution spliting your table over severl pages, refer to Karl Yanks posts in this thread:
    http://www.sitepointforums.com/showt...threadid=16004

  11. #11
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As for the front page user position display, if your database updates after a specific time period (ie. scores update once an hour or once a day), you could keep a field called "Position" in the MySQL table. When your script updates, it would update the position field.

    Then, when a user visits the front page, your script checks the cookie and just does a "Select * from scores where UserID = '$id'" or something like that.

  12. #12
    BoOm-Rocka! Smarky's Avatar
    Join Date
    Jun 2000
    Location
    England
    Posts
    1,319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey i used freakysid's code for the pos. thing and now its working it is great. The only trouble is being as quite a few people have the same amount of points they are being told they are in the same place. I.E currently 20 members on the test system are on 0 points and this code is telling them they are all 3rd. Is there a way to once ranking them by score to sort it the postions then by username? So no two users are told that they are in the same postion?
    Garlic bread, I've tasted it, it's the future


Bookmarks

Posting Permissions

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