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

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

其实我想使用php收集来自sql的数据,而命令如下:

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?

但是当我运行它时,它会发送sql中的所有记录。我只想显示有限的记录。怎么做。甚至还有一个问题,我可以将所有记录分成不同的页面吗?

5 个解决方案

#1

Note:

• You can use LIMIT to limit the number of rows your query has to get.

您可以使用LIMIT来限制查询必须获得的行数。

• And you are referring to pagination to split the records into pages.

而你指的是将记录分成页面的分页。

• I'll be using mysqli_* instead of deprecated mysql_*

我将使用mysqli_ *而不是弃用的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>

#2

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++:
}

#3

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

使用sql的限制功能。对于Ex:如果要获取10条记录,那么您的查询应如下所示:

SELECT * FROM table_name LIMIT 0,10;

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

其中“0”是起始值,“10”是上述查询中的记录数。

#4

Use MYSQL LIMIT clause for this.

为此使用MYSQL LIMIT子句。

#5

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

你可以使用for循环也设置开始和结束限制。

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