#1
  1. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    15
    Rep Power
    0
    I need to pull up a top 10 list on my webpage, but I need help with writing the right query.

    How would a query be written pull only the top 10 highest values (rows) from a particular column in a table?

    [This message has been edited by Rolemaster (edited November 28, 2000).]
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    SELECT poll.title,polldata.counter FROM poll,polldata WHERE poll.ref_id = polldata.ref_id ORDER BY polldata.count DESC LIMIT 10

    Assuming you have some sort of reference ID relating the counter to the text, that should work.

    ---John Holmes...
  4. #3
  5. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    15
    Rep Power
    0
    That query really did the trick for me. Thanks...

    I have to get 3 separate results though. I only included 2 of the queries.

    $pkquery = "SELECT poll.title,polldata.counter FROM poll,polldata WHERE poll.id
    = polldata.pollid AND polldata.itemtext = 'anti player' ORDER BY polldata.counter DESC LIMIT 10";

    $neutquery = "SELECT poll.title,polldata.counter FROM poll,polldata WHERE poll.id
    = polldata.pollid AND polldata.itemtext = 'neutral player' ORDER BY polldata.counter DESC LIMIT 10";

    $pkresult = mysql_db_query($db, $pkquery, $connection) or die("Error in query");
    $neutresult = mysql_db_query($db, $neutquery, $connection) or die("Error in query");


    How would I write out the WHILE statement to take the various arrays from the queries?

    while($pkrow = mysql_fetch_array($pkresult), $neutrow = mysql_fetch_array($neutresult))
    {
    $pk_player_Name = $pkrow["title"];
    $counter = $pkrow["counter"];
    $neut_player_Name = $neutrow["title"];



    [This message has been edited by Rolemaster (edited November 28, 2000).]
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    How do you want to display the data? The 10 from list 1, 10 from list 2, 10 from list 3? or...#10 from each list, #9 from each list, #8 ...etc.??

    Just reread you comment, I'm guessing method 2.

    while(($pkrow = mysql_fetch_array($pkresult)) AND ($neutrow = mysql_fetch_array($neutresult)))

    should work. add in another AND $cross_dressing_row = ... and you should be good to go..

    ---John Holmes...
  8. #5
  9. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    15
    Rep Power
    0
    Man o man. That solution is exactly what I thought it should have been. I tried something like that, but I didnt get the correct bracket structure right. I do try reading the MySQL manual, but it is VERY hard to find exactly what I am looking for.
  10. #6
  11. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    15
    Rep Power
    0
    One last question...

    How about incrementing a row count. To show the row number next to the outputted row?

    Like:

    1 Row output1
    2 Row output2
    3 Row output2

    <td>$rowcount</td>
    <td>$pk_player_Name</td>
    <td>$pkcounter</td>
  12. #7
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    easiest way...

    set $row_count = 1 before your while loop, then add this in:

    echo "<td>" . $row_count++ . "</td>";

    ---John Holmes...

Similar Threads

  1. MySQL Wildcard to list all values in a column
    By jonki in forum MySQL Help
    Replies: 7
    Last Post: April 7th, 2004, 06:44 PM
  2. setting default values in column field
    By arthats in forum MySQL Help
    Replies: 1
    Last Post: February 9th, 2004, 11:20 PM
  3. List unique values from column
    By k89mmk in forum MS SQL Development
    Replies: 2
    Last Post: February 6th, 2004, 07:21 AM
  4. Replies: 2
    Last Post: January 27th, 2004, 04:34 PM
  5. how to keep table on top, not centered??
    By vinyl in forum HTML Programming
    Replies: 2
    Last Post: January 20th, 2004, 07:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo