Detailed explanation of limit usage in PHP mysql (code example)-Mysql Tutorial-php.cn

Detailed explanation of limit usage in PHP mysql (code example)

藏色散人

Release： 2019-03-22 16:05:49

Original

7350 people have browsed it

In MySQL, the LIMIT clause is used with the SELECT statement to limit the number of rows in the result set. The LIMIT clause accepts one or two parameters, offset and count. The value of both parameters can be zero or a positive integer.

Detailed explanation of limit usage in PHP mysql (code example)

offset: Used to specify the offset of the first row to be returned.

Count: Used to specify the maximum number of rows to be returned.

The Limit clause accepts one or two parameters. When two parameters are specified, the first parameter is the offset, and the second parameter represents the count. When only one parameter is specified, it represents the starting point. The number of rows to start returning in the result set.

LIMIT syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

Copy after login

The following table "Data" contains three columns "Firstname", "Lastname" and "Age".

Detailed explanation of limit usage in PHP mysql (code example)

To retrieve the first three rows from the "Data" table, we will use the following query:

SELECT * FROM Data LIMIT 3;

Copy after login

To retrieve the 2nd row from the "Data" table -3 lines (inclusive), we will use the following query:

SELECT * FROM Data LIMIT 1, 2;

Copy after login

The following is a code example for PHP mysql to implement the query:

Example 1: Limit condition

<?php 
$link = mysqli_connect("localhost", "root", "", "Mydb"); 
  
if ($link == = false) { 
    die("ERROR: Could not connect. ".mysqli_connect_error()); 
} 
  
$sql = "SELECT * FROM Data LIMIT 2"; 
if ($res = mysqli_query($link, $sql)) { 
    if (mysqli_num_rows($res) > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>"; 
        while ($row = mysqli_fetch_array($res)) { 
            echo "<tr>"; 
            echo "<td>".$row[&#39;Firstname&#39;]."</td>"; 
            echo "<td>".$row[&#39;Lastname&#39;]."</td>"; 
            echo "<td>".$row[&#39;Age&#39;]."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
        mysqli_free_result($res); 
    } 
    else { 
        echo "No matching records are found."; 
    } 
} 
else { 
    echo "ERROR: Could not able to execute $sql. ".mysqli_error($link); 
} 
  
mysqli_close($link);

Copy after login

Output:

Detailed explanation of limit usage in PHP mysql (code example)

Note: The "res" variable stores the data returned by the function mysql_query().

Every time mysqli_fetch_array() is called, it returns the next row from the res() set.

The while loop is used to traverse all rows of the table "data".

Example 2: Limit clause using object-oriented approach

<?php 
$mysqli = new mysqli("localhost", "root", "", "Mydb"); 
  
if ($mysqli == = false) { 
    die("ERROR: Could not connect. ".$mysqli->connect_error); 
} 
  
$sql = "SELECT * FROM Data LIMIT 2"; 
if ($res = $mysqli->query($sql)) { 
    if ($res->num_rows > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>"; 
        while ($row = $res->fetch_array()) { 
            echo "<tr>"; 
            echo "<td>".$row[&#39;Firstname&#39;]."</td>"; 
            echo "<td>".$row[&#39;Lastname&#39;]."</td>"; 
            echo "<td>".$row[&#39;Age&#39;]."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
        $res->free(); 
    } 
    else { 
        echo "No matching records are found."; 
    } 
} 
else { 
    echo "ERROR: Could not able to execute $sql. ".$mysqli->error; 
} 
  
$mysqli->close();

Copy after login

Output:

Detailed explanation of limit usage in PHP mysql (code example)

Example 3: Limit clause using PDO method

<?php 
try { 
    $pdo = new PDO("mysql:host=localhost;dbname=Mydb", "root", ""); 
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
} 
catch (PDOException $e) { 
    die("ERROR: Could not connect. ".$e->getMessage()); 
} 
  
try { 
    $sql = "SELECT * FROM Data LIMIT 2"; 
    $res = $pdo->query($sql); 
    if ($res->rowCount() > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>"; 
        while ($row = $res->fetch()) { 
            echo "<tr>"; 
            echo "<td>".$row[&#39;Firstname&#39;]."</td>"; 
            echo "<td>".$row[&#39;Lastname&#39;]."</td>"; 
            echo "<td>".$row[&#39;Age&#39;]."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
        unset($res); 
    } 
    else { 
        echo "No matching records are found."; 
    } 
} 
catch (PDOException $e) { 
    die("ERROR: Could not able to execute $sql. ".$e->getMessage()); 
} 
  
unset($pdo);

Copy after login

Output:

Detailed explanation of limit usage in PHP mysql (code example)

Related recommendations:《 mysql tutorial》

This article is about the detailed usage of limit in mysql. I hope it will be helpful to friends in need!

The above is the detailed content of Detailed explanation of limit usage in PHP mysql (code example). For more information, please follow other related articles on the PHP Chinese website!