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

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?

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>

Add limit to query. LIMIT

select * from your_table limit 10

Or add a count for the loop -

$count = 1;
while ($res = mysql_fetch_assoc($dat)){
    if($count > 10) {
        break;
    }
    // rest of the code

    $count++:
}

Use sql's limit feature. For Ex: If you want to fetch say 10 records then your query should look like this:

SELECT * FROM table_name LIMIT 0,10;

Where '0' is start valueand '10' is number of records in above query.

Use MYSQL LIMIT clause for this.

you can use for loop also set start and end limit.

for($i=$start ;$i< ($start + $limit) ; $i++)
{
 //your code   
}