SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    87
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How to echo the 2nd item from a 3-item ORDER BY list?

    Good morning from Palo Alto. I'm having trouble printing a SINGLE item -- the second, say -- from a 3 item database table. Can you please help?

    I have 3 items in a MYSQL database table. Each has 2 fields: name and score, as shown here:

    Jack 80
    Bob 60
    Sam 40

    Of course, each also has an automatically generated "id" field.

    My PHP code to call the list in decending order of score is:

    <?
    mysql_connect("ip.address.goes.here", "sql@nameofmysite.com", "password") or die(mysql_error());
    @mysql_select_db("nameofdatabase") or die( "Unable to select database");
    $query="SELECT * FROM nameoftable ORDER BY score desc LIMIT 3";
    $result=mysql_query($query);
    $num=mysql_numrows($result);
    mysql_close();
    $i=0;
    while ($i < $num) {
    $id=mysql_result($result,$i,"id");
    $score=mysql_result($result,$i,"score");
    $name=mysql_result($result,$i,"name");
    ?>

    To print the whole list of 3 items, I use this code, which works fine:

    <? echo "$name $score";?>
    <?
    ++$i;
    }
    ?>

    What would I use if I wanted to print JUST the SECOND ordered result? I'd want this output:

    Bob 60

    I imagine the code might be something like this, but this isn't working:

    <? echo "$name $score"; WHERE ORDER BY=2 ?>

    Thanks for helping!

  2. #2
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, I would write a SQL statement that included a WHERE clause so the result coming back only had the records that I wanted to output.

    SELECT * FROM mytable WHERE userid=2

    Now, if there is a reason that you are bringing back the other records you would just need to add an if statement inside your loop that retrieves the records

    PHP Code:
    while($row mysql_fetch_array($myRecordset)){
        if(
    $row['userid'] == 2){
            echo 
    $row['name'] . ' ' $row['score'];
        }

    There is also a seek function mysql_seek() that lets you retrieve a specific row if you don't need to test the information inside before outputting.

    HTH
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  3. #3
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query="SELECT * FROM nameoftable ORDER BY score desc LIMIT 1,1"
    Saul

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by php_daemon View Post
    PHP Code:
    $query="SELECT * FROM nameoftable ORDER BY score desc LIMIT 1,1"
    and also

    Code:
    mysql_result($rs,1);
    will do the same


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
  •