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

    Join Date
    Aug 2003
    Posts
    407
    Rep Power
    11

    How to display mySQL record number for each row found


    I have the following:
    PHP Code:
    <?php 
     
    // database connection
     
    require 'dbconnect2.php'
     
     
    //query
     
    $data mysql_query("SELECT * FROM tracks WHERE album='510'"
     or die(
    mysql_error()); 
     Print 
    "<table border cellpadding=3>"
     while(
    $info mysql_fetch_array$data )) 
     { 
     Print 
    "<tr>"
     Print 
    "<th>Title:</th> <td>".$info['title'] . "</td> "
     Print 
    "<th>Date:</th> <td>".$info['rec_date'] . "</td> "
     Print 
    "<th>Composer:</th> <td>".$info['authors'] . "</td> ";
     Print 
    "<th>Artist:</th> <td>".$info['artists'] . "</td> ";
     Print 
    "<th>Time:</th> <td>".$info['time'] . "</td> "
     Print 
    "<th>Blurb:</th> <td>".$info['blurb'] . " </td></tr>"
     } 
     Print 
    "</table>"
     
    ?>
    This works well, but I also need each row to be numbered for each record found. I've tried:

    PHP Code:
    $row mysql_fetch_row($result);
    $count $row[0]; 
    and
    Code:
    Print "<th>Track:</th> <td>".$count . "</td> ";
    But this doesn't work.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,395
    Rep Power
    1688
    I think you need a new column in your table to hold that id number as the number you'd get from any query based on position in the query is not guaranteed as the return order of data is not guaranteed (unless you add an ORDER BY).
    Having said that if all you are after is an incrementing number on the page (and have no intention of daoing naything with it) then there is a way to get that within the query - it's something like using COUNT(*)+1, but the real mySQL gurus over in the mySQL part of the forum will give a fuller answer.
    You could also do it in the php, setting a variable to 0 outsde the loop, then incrementing and displaying the variable within the loop.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    407
    Rep Power
    11
    Originally Posted by SimonJM
    You could also do it in the php, setting a variable to 0 outsde the loop, then incrementing and displaying the variable within the loop.
    Ah, that sounds perfect. Could you give me an example?

    Like this?

    PHP Code:
     $counter 0
     while(
    $info mysql_fetch_array$data )) 
     { 
     Print 
    "<tr>"
     Print 
    "<th>Track:</th> <td>".$counter+"</td> "
    Last edited by groundlevel; January 29th, 2011 at 01:43 PM.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    105
    Rep Power
    51
    PHP Code:
    $count 0;
     while(
    $info mysql_fetch_array$data )) 
     { 
     Print 
    "<tr>"
     Print 
    "<th>Title:</th> <td>".$info['title'] . "</td> "
     Print 
    "<th>Date:</th> <td>".$info['rec_date'] . "</td> "
     Print 
    "<th>Composer:</th> <td>".$info['authors'] . "</td> ";
     Print 
    "<th>Artist:</th> <td>".$info['artists'] . "</td> ";
     Print 
    "<th>Time:</th> <td>".$info['time'] . "</td> "
     Print 
    "<th>Blurb:</th> <td>".$info['blurb'] . " </td></tr>"
    $count++;
     } 
    Then $count will increase for each record found.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    407
    Rep Power
    11
    I think that would put the count at the end of each row.

    Can I do this?
    PHP Code:
     $count 0
     while(
    $info mysql_fetch_array$data ))  
     {  
     
     while(
    $info mysql_fetch_array$data ))  
     {
    $count++;  
     Print 
    "<tr>";  
     Print 
    "<th>Title:</th> <td>".$info['title'] . "</td> ";  
     Print 
    "<th>Date:</th> <td>".$info['rec_date'] . "</td> ";  
     Print 
    "<th>Composer:</th> <td>".$info['authors'] . "</td> "
     Print 
    "<th>Artist:</th> <td>".$info['artists'] . "</td> "
     Print 
    "<th>Time:</th> <td>".$info['time'] . "</td> ";  
     Print 
    "<th>Blurb:</th> <td>".$info['blurb'] . " </td></tr>";  
     } 
    Also, I need to have "Track:" put in front of the $count.
    Last edited by groundlevel; January 29th, 2011 at 02:12 PM.
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    105
    Rep Power
    51
    PHP Code:
    Print "<th>Track:</th> <td>".$count."</td> "
    For example. That does require the records to be in the correct order in the database though.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    407
    Rep Power
    11
    The db is arranged by ID, in sequential order. Does the code I have keep it in the correct order by ID?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    407
    Rep Power
    11
    Drat, again!

    I copied your code, and missed one of the "*"'s. They're pasting as invisibles in my code, sorry.

    This works great, thanks
    Last edited by groundlevel; January 29th, 2011 at 02:44 PM.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,395
    Rep Power
    1688
    Originally Posted by groundlevel
    The db is arranged by ID, in sequential order. Does the code I have keep it in the correct order by ID?
    mySQL does not guarantee the order of returned data unless you impose such an order explicitly. It's very likely you'll get the data in the order you wish, but by no means certain. If you want it in an order then add an ORDER BY column_name.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    407
    Rep Power
    11
    That's exactly what I did

IMN logo majestic logo threadwatch logo seochat tools logo