[solved]paging records

  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

on php and mysql, how do i create a page where it shows a number of records - ie. 10 then after that make it go to another page?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

ignore this post...
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Code: [ Select ]
ORDER BY fieldName DESC


Should do the trick. fieldname could be an key if it auto-incrments or it could be the date, it's up to you,
DESC just means descending.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

oh right, thanks i forgot to add the "id" field i want to go on the descending order.

silly me
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

hey erm, are there any good tutorials about how to do paging....

so i show 10 news articles on one page, then 10 on the other etc
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

limit clause of your sql statement (only works with mysql)
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Nem wrote:
hey erm, are there any good tutorials about how to do paging....

so i show 10 news articles on one page, then 10 on the other etc


not that i know of but you can use something like

this may be to complicated but here it goes

PHP Code: [ Select ]
//FUNCTION
 
//  This function displays the logs for the site.
 
function dispnews($startNum)
 
{
 
 
 
  extract($myrow);
 
 
 
  echo("
 
   <table border=0 width=600 cellspacing=0 cellpadding=1 bgcolor=$tableColor borderColor=black>
 
   <tr><td>
 
     
 
   </td></tr>
 
 
 
   <tr><td>
 
     
 
      <b><font size=2>Lastest News</font></b><hr color=gray>
 
 
 
 ");
 
  $result = @mysql_query("SELECT COUNT(*) FROM news");
 
  $count = mysql_fetch_array($result);
 
  $count = $count[0];
 
 
 
  $num_pages = ($count / 10) + 1;
 
  $limit = 10 + $startNum * 10;
 
  $i = 0;
 
  $result = @mysql_query("SELECT * FROM news ORDER BY id DESC");
 
  while(($row = mysql_fetch_array($result)) && $i <= $limit) {
 
   extract($row);
 
   
 
   if($i >= ($startNum * 10)) {
 
   echo("
 
      <tr><td>
 
         <font face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular" color="#76A5D5" size=0>
 
         $info
 
         <br>
 
      </td>tr>
 
   ");
 
   } 
 
 
 
   $i++;
 
 }
 
 
 
 echo("
 
   <tr><td>
 
     
 
      <br><b>Page:<b>
 
  ");
 
 
 
 for($i = 1; $i <= $num_pages; $i++) {
 
   echo("
 
      <a href=news.php?page=$i>$i</a>
 
   ");
 
 }
 
 
 
 echo("
 
   </table>
 
  ");
 
     
 
}
 
 
 
 
 
//page that displays the news
 
if($page == NULL) {
 
   $page = 0;
 
 }
 
 else {
 
   $page = $page - 1;
 
 }
 
 
 
echo(" <center>$fontString <font face=verdana size=5><b>News</font></b></center>
 
      ");
 
 
 
 dispnews($page);
 
}
 
 
  1. //FUNCTION
  2.  
  3. //  This function displays the logs for the site.
  4.  
  5. function dispnews($startNum)
  6.  
  7. {
  8.  
  9.  
  10.  
  11.   extract($myrow);
  12.  
  13.  
  14.  
  15.   echo("
  16.  
  17.    <table border=0 width=600 cellspacing=0 cellpadding=1 bgcolor=$tableColor borderColor=black>
  18.  
  19.    <tr><td>
  20.  
  21.      
  22.  
  23.    </td></tr>
  24.  
  25.  
  26.  
  27.    <tr><td>
  28.  
  29.      
  30.  
  31.       <b><font size=2>Lastest News</font></b><hr color=gray>
  32.  
  33.  
  34.  
  35.  ");
  36.  
  37.   $result = @mysql_query("SELECT COUNT(*) FROM news");
  38.  
  39.   $count = mysql_fetch_array($result);
  40.  
  41.   $count = $count[0];
  42.  
  43.  
  44.  
  45.   $num_pages = ($count / 10) + 1;
  46.  
  47.   $limit = 10 + $startNum * 10;
  48.  
  49.   $i = 0;
  50.  
  51.   $result = @mysql_query("SELECT * FROM news ORDER BY id DESC");
  52.  
  53.   while(($row = mysql_fetch_array($result)) && $i <= $limit) {
  54.  
  55.    extract($row);
  56.  
  57.    
  58.  
  59.    if($i >= ($startNum * 10)) {
  60.  
  61.    echo("
  62.  
  63.       <tr><td>
  64.  
  65.          <font face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular" color="#76A5D5" size=0>
  66.  
  67.          $info
  68.  
  69.          <br>
  70.  
  71.       </td>tr>
  72.  
  73.    ");
  74.  
  75.    } 
  76.  
  77.  
  78.  
  79.    $i++;
  80.  
  81.  }
  82.  
  83.  
  84.  
  85.  echo("
  86.  
  87.    <tr><td>
  88.  
  89.      
  90.  
  91.       <br><b>Page:<b>
  92.  
  93.   ");
  94.  
  95.  
  96.  
  97.  for($i = 1; $i <= $num_pages; $i++) {
  98.  
  99.    echo("
  100.  
  101.       <a href=news.php?page=$i>$i</a>
  102.  
  103.    ");
  104.  
  105.  }
  106.  
  107.  
  108.  
  109.  echo("
  110.  
  111.    </table>
  112.  
  113.   ");
  114.  
  115.      
  116.  
  117. }
  118.  
  119.  
  120.  
  121.  
  122.  
  123. //page that displays the news
  124.  
  125. if($page == NULL) {
  126.  
  127.    $page = 0;
  128.  
  129.  }
  130.  
  131.  else {
  132.  
  133.    $page = $page - 1;
  134.  
  135.  }
  136.  
  137.  
  138.  
  139. echo(" <center>$fontString <font face=verdana size=5><b>News</font></b></center>
  140.  
  141.       ");
  142.  
  143.  
  144.  
  145.  dispnews($page);
  146.  
  147. }
  148.  
  149.  

now that was original from some scripts that i DID NOT make but it does what you want so have fun hope it works out for ya need some help dosnt ask me lol i dont know how it really works
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

that's a pretty crappy method. The proper way to do it is to use SQL queries that only return the results you want. The way to do that with mysql is 'limit'. Look it up in the mysql manual.

BTW, the reason it is so terrible is that mysql still has to return the unwanted rows to the php script. This could take a very long time (depending on how much news you have) and would slow things down unnecicarily.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

but he wants it so 10 messages will be displayed on one page and at the bottom have links to other pages with all the other news
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

that's not hard to do.
  • Scorpius
  • Proficient
  • Proficient
  • User avatar
  • Posts: 401
  • Loc: Scorpion Hole

Post 3+ Months Ago

Well this is something I made and it should be something like what you want.
Just put this at the with your query.
PHP Code: [ Select ]
  if($_GET['p'] >0) {
 
    $offset = ($_GET['p']-1)*10;
 
    $offset = " LIMIT $offset, 10";
 
  } else {
 
    $offset = " LIMIT 0, 10";
 
  }
 
  $message = mysql_query("SELECT * FROM `messages` WHERE `recipient` = '$_COOKIE[user_id]' ORDER BY `timestamp` DESC$offset");
 
// display your results.
 
// put this where you want to display the page numbers
 
$getlist = mysql_query("SELECT recipient FROM `messages` WHERE `recipient` = '$_COOKIE[userid]'");
 
$messages = mysql_num_rows($getlist);
 
if($messages >10) {
 
    echo "<br><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" align=\"right\">
 
                <tr>
 
                   <td align=\"right\">
 
                            Page: ";
 
    $p0 = $messages/10;
 
    $p1 = round($p0);
 
    if($p1 < $p0) {
 
       $p1 = $p1+.5;
 
       $p1 = round($p1);
 
    }
 
    for ($i = 1; $i <= $p1; $i++) {
 
       echo "<a href=\"messages.php?p=$i\">$i</a> ";
 
    }
 
    echo "    </td>
 
   </tr>
 
   </table>";
 
}
  1.   if($_GET['p'] >0) {
  2.  
  3.     $offset = ($_GET['p']-1)*10;
  4.  
  5.     $offset = " LIMIT $offset, 10";
  6.  
  7.   } else {
  8.  
  9.     $offset = " LIMIT 0, 10";
  10.  
  11.   }
  12.  
  13.   $message = mysql_query("SELECT * FROM `messages` WHERE `recipient` = '$_COOKIE[user_id]' ORDER BY `timestamp` DESC$offset");
  14.  
  15. // display your results.
  16.  
  17. // put this where you want to display the page numbers
  18.  
  19. $getlist = mysql_query("SELECT recipient FROM `messages` WHERE `recipient` = '$_COOKIE[userid]'");
  20.  
  21. $messages = mysql_num_rows($getlist);
  22.  
  23. if($messages >10) {
  24.  
  25.     echo "<br><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" align=\"right\">
  26.  
  27.                 <tr>
  28.  
  29.                    <td align=\"right\">
  30.  
  31.                             Page: ";
  32.  
  33.     $p0 = $messages/10;
  34.  
  35.     $p1 = round($p0);
  36.  
  37.     if($p1 < $p0) {
  38.  
  39.        $p1 = $p1+.5;
  40.  
  41.        $p1 = round($p1);
  42.  
  43.     }
  44.  
  45.     for ($i = 1; $i <= $p1; $i++) {
  46.  
  47.        echo "<a href=\"messages.php?p=$i\">$i</a> ";
  48.  
  49.     }
  50.  
  51.     echo "    </td>
  52.  
  53.    </tr>
  54.  
  55.    </table>";
  56.  
  57. }

You should edit the code and queries accordingly to your liking and how your page is setup.
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

You should NEVER put variables from user input straight into mysql. You should ALWAYS use mysql_escape_string() first.

so that code should look like
PHP Code: [ Select ]
 
if($_GET['p'] >0) {
 
    $offset = ($_GET['p']-1)*10;
 
    $offset = " LIMIT $offset, 10";
 
  } else {
 
    $offset = " LIMIT 0, 10";
 
  }
 
  $message = mysql_query("SELECT * FROM `messages` WHERE `recipient` = '".mysql_escape_string($_COOKIE[user_id])."' ORDER BY `timestamp` DESC$offset");
 
// display your results.
 
// put this where you want to display the page numbers
 
$getlist = mysql_query("SELECT recipient FROM `messages` WHERE `recipient` = '".mysql_escape_string($_COOKIE[userid])."'");
 
$messages = mysql_num_rows($getlist);
 
if($messages >10) {
 
    echo "<br><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" align=\"right\">
 
                <tr>
 
                   <td align=\"right\">
 
                            Page: ";
 
    $p0 = $messages/10;
 
    $p1 = round($p0);
 
    if($p1 < $p0) {
 
       $p1 = $p1+.5;
 
       $p1 = round($p1);
 
    }
 
    for ($i = 1; $i <= $p1; $i++) {
 
       echo "<a href=\"messages.php?p=$i\">$i</a> ";
 
    }
 
    echo "    </td>
 
   </tr>
 
   </table>";
 
}
  1.  
  2. if($_GET['p'] >0) {
  3.  
  4.     $offset = ($_GET['p']-1)*10;
  5.  
  6.     $offset = " LIMIT $offset, 10";
  7.  
  8.   } else {
  9.  
  10.     $offset = " LIMIT 0, 10";
  11.  
  12.   }
  13.  
  14.   $message = mysql_query("SELECT * FROM `messages` WHERE `recipient` = '".mysql_escape_string($_COOKIE[user_id])."' ORDER BY `timestamp` DESC$offset");
  15.  
  16. // display your results.
  17.  
  18. // put this where you want to display the page numbers
  19.  
  20. $getlist = mysql_query("SELECT recipient FROM `messages` WHERE `recipient` = '".mysql_escape_string($_COOKIE[userid])."'");
  21.  
  22. $messages = mysql_num_rows($getlist);
  23.  
  24. if($messages >10) {
  25.  
  26.     echo "<br><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" align=\"right\">
  27.  
  28.                 <tr>
  29.  
  30.                    <td align=\"right\">
  31.  
  32.                             Page: ";
  33.  
  34.     $p0 = $messages/10;
  35.  
  36.     $p1 = round($p0);
  37.  
  38.     if($p1 < $p0) {
  39.  
  40.        $p1 = $p1+.5;
  41.  
  42.        $p1 = round($p1);
  43.  
  44.     }
  45.  
  46.     for ($i = 1; $i <= $p1; $i++) {
  47.  
  48.        echo "<a href=\"messages.php?p=$i\">$i</a> ";
  49.  
  50.     }
  51.  
  52.     echo "    </td>
  53.  
  54.    </tr>
  55.  
  56.    </table>";
  57.  
  58. }


it would also be a very good idea to make sure users don't modify their cookie file to get someone else's messages.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

what does the cookie have anything to do with it? Its free to view from all users.
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

well then clearly I misunderstood the nature of your post. However, from the looks of the queries involved, you are requesting messages that are specific to a particular user. What is to stop a user from putting someone elses user number in their cookie?

//edit: ok I see it was not your code. It was scorpius
  • Scorpius
  • Proficient
  • Proficient
  • User avatar
  • Posts: 401
  • Loc: Scorpion Hole

Post 3+ Months Ago

Yea, mine was just an example, I don't know how you are going to tell the users apart, if you use sessions or cookies. In this I used cookies, you could use sessions or some other method. It doesn't really matter as long as you do something.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

but this is for the main page... :\

Post Information

  • Total Posts in this topic: 16 posts
  • Users browsing this forum: No registered users and 131 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.