Error when PHP select with limit, offset from SQL

Question

I am trying to select data from a database. The php script is to select 4 rows from the database table starting from the very first row, but the code ends up selecting only 3 rows starting from the second row instead of the first. What should i do?

<?php 

        try{

            require 'mysqli_connect.php';

            $maxrow = 4;
            $start = 0;

            $query = "SELECT user_id , first_name , last_name, email , registration_date FROM users LIMIT ? , ?";
            $stmt = mysqli_stmt_init($dbcon);
            mysqli_stmt_prepare($stmt , $query);
            mysqli_stmt_bind_param($stmt , 'ii' , $start ,$maxrow); // still showing only three rows and does not count from zero
            mysqli_stmt_execute($stmt);
            $record = mysqli_stmt_get_result($stmt);
            $row = mysqli_fetch_array($record , MYSQLI_NUM);

            if($record){

                echo "<table>";
                echo "<tr>";
                echo "<th>user_id</th>";
                echo "<th>first_name</th>";
                echo "<th>last_name</th>";  
                echo "<th>email</th>";
                echo "<th>registration_date</th>";
                echo "</tr>";

                while ($row = mysqli_fetch_array($record , MYSQLI_NUM)) {

                    $user_id = $row[0];
                    $first_name = $row[1]; 
                    $last_name = $row[2];
                    $email = $row[3];
                    $registration_date  = $row[4];

                    echo '<tr>
                    <td><a href="edit_user.php?id=' . $user_id . '">Edit</a></td>
                    <td><a href="delete_user.php?id=' . $user_id . '">Delete</a></td>
                    <td>' . $last_name . '</td>
                    <td>' . $first_name . '</td>
                    <td>' . $email . '</td>
                    <td>' . $registration_date . '</td>
                    </tr>';
                }
                echo "</table>";
                mysqli_free_result ($record); // Free up the resources. 

            }

        }catch(Exception $e){
            //echo $e->getMessage();
        }catch(Error $e){
            //echo $e->getMessage();
        }

    ?>

Just do LIMIT 4, no need to declare start row if its starting from the first row. — Jaquarh
– Jaquarh, Commented Dec 15, 2018 at 13:03
i really need that $start because i want to use it for pagination, thanks though — Onyia
– Onyia, Commented Dec 15, 2018 at 13:11

DinoCoderSaurus · Accepted Answer · 2018-12-15 13:08:26Z

1

First row is selected here $row = mysqli_fetch_array($record , MYSQLI_NUM);. So the mysqli_fetch_array() in the while starts with the second row.

answered Dec 15, 2018 at 13:08

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rom · Accepted Answer · 2018-12-15 13:11:17Z

Your first record is fetch here: $row = mysqli_fetch_array($record , MYSQLI_NUM);

It should be

<?php 

        try{

            require 'mysqli_connect.php';

            $maxrow = 4;
            $start = 0;

            $query = "SELECT user_id , first_name , last_name, email , registration_date FROM users LIMIT ? , ?";
            $stmt = mysqli_stmt_init($dbcon);
            mysqli_stmt_prepare($stmt , $query);
            mysqli_stmt_bind_param($stmt , 'ii' , $start ,$maxrow); // still showing only three rows and does not count from zero
            mysqli_stmt_execute($stmt);
            $record = mysqli_stmt_get_result($stmt);
            // Your first record is fetch here! OMG!!
            // $row = mysqli_fetch_array($record , MYSQLI_NUM);

            if($record){

                echo "<table>";
                echo "<tr>";
                echo "<th>user_id</th>";
                echo "<th>first_name</th>";
                echo "<th>last_name</th>";  
                echo "<th>email</th>";
                echo "<th>registration_date</th>";
                echo "</tr>";

                while ($row = mysqli_fetch_array($record , MYSQLI_NUM)) {

                    $user_id = $row[0];
                    $first_name = $row[1]; 
                    $last_name = $row[2];
                    $email = $row[3];
                    $registration_date  = $row[4];

                    echo '<tr>
                    <td><a href="edit_user.php?id=' . $user_id . '">Edit</a></td>
                    <td><a href="delete_user.php?id=' . $user_id . '">Delete</a></td>
                    <td>' . $last_name . '</td>
                    <td>' . $first_name . '</td>
                    <td>' . $email . '</td>
                    <td>' . $registration_date . '</td>
                    </tr>';
                }
                echo "</table>";
                mysqli_free_result ($record); // Free up the resources. 

            }

        }catch(Exception $e){
            //echo $e->getMessage();
        }catch(Error $e){
            //echo $e->getMessage();
        }

    ?>

Collectives™ on Stack Overflow

Error when PHP select with limit, offset from SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related