使用PHP从sql中获取有限的数据

Question

Actually i want to collect data from sql using php while command which is like:

while ($res = mysql_fetch_assoc($dat)){//code here}

But when i run it, it sends all the records in sql. I just want to display the limited records. How to do that. Even one more question with this can i split all the records into various pages?

Answer 1

Note:

• You can use LIMIT to limit the number of rows your query has to get.
• And you are referring to pagination to split the records into pages.
• I'll be using mysqli_* instead of deprecated mysql_*
• I've put some explanations inside
• Replace necessary table name, column name, and connection variable.

You can begin with this:

$result = mysqli_query($con,"SELECT * FROM Devices2 ORDER BY name");

$count = mysqli_num_rows($result); /* COUNT THE TOTAL ROW */

$rowsperpage = 20; /* NUMBER OF ROW PER PAGE */
$totalpages = ceil($count / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
      $currentpage = (int) $_GET['currentpage'];
} else {
     $currentpage = 1;
} 

if ($currentpage > $totalpages) {
   $currentpage = $totalpages;
} 
if ($currentpage < 1) {
   $currentpage = 1;
} 

$offset = ($currentpage - 1) * $rowsperpage;

  ?>
    <table><!-- START OF TABLE OF RECORDS -->
  <?php

/* START OF SHOWING THE RECORD */
if($stmt = $con->prepare("SELECT name FROM Devices2 ORDER BY name LIMIT $offset, $rowsperpage")){

  $stmt->execute();
  $stmt->bind_result($name);
  while($stmt->fetch()){

    ?>
      <tr>
         <td><?php echo $name; ?></td><!-- ROW OF RECORD -->
      </tr>
    <?php

  } /* END OF WHILE LOOP */

  $stmt->close();

} /* END OF PREPARED STATEMENT */

if($count == 0){ /* IF NO RECORD FOUND */
  ?>
    </table>
    <h1>No record found</h1>
  <?php
}

else { /* ELSE, START THE PAGINATION LINK */

  echo '<tr height="30px;" valign="bottom"><td>';

  /* THIS IS THE SECOND TABLE FOR THE PAGINATION LINK */
  echo "<table style='border-collapse:separate; border-spacing:3px;'><tr>";


  /******  build the pagination links ******/
  $range = 2;

  if ($currentpage > 1) {
    $prevpage = $currentpage - 1;
    echo "<td style='width:70px; background-color:fff; border:solid #08c 1px; font-size:14px;' align='center'> <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage' style='background-color:fff;'>Previous</a> </td>";
  } 


  for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {

    if (($x > 0) && ($x <= $totalpages)) {
      if ($x == $currentpage) {    
        echo "<td style='width:20px; background-color:fff; font-size:14px; border:solid #ccc 2px;' align='center'> <font color='#ccc'><b>$x</b></font> </td>";
      } else {
         echo "<td style='width:20px; background-color:fff; font-size:14px; border:solid #08c 1px;' align='center'> <a href='{$_SERVER['PHP_SELF']}?currentpage=$x' style='background-color:fff;'>$x</a> </td>";
      } 
    } 
  }                  

  if ($currentpage != $totalpages) {
    $nextpage = $currentpage + 1;
    echo "<td style='width:70px; background-color:fff; font-size:14px; border:solid #08c 1px;' align='center'> <a href='{$_SERVER['PHP_SELF']}?&currentpage=$nextpage' style='background-color:fff;'>Next</a> </td>";

  } // end if
  /****** end build pagination links ******/

  echo "</tr></table></td></tr>";

} /* END OF ELSE IF COUNT 0 */

?>
  </table>