There are no more rows in the active result set. Since this result set is not scrollable, no more data may be retrieved

Question

regarding my problem above, I created a system that requires the user to select the date between "from" to "to". I used the AJAX method. When to display the result, it will show the error same as my topic. Below is the code:

<?php
if(isset($_POST["From"], $_POST["to"]))
{
    $result = '';

    $query = $conn->query("SELECT * FROM booking LEFT JOIN room ON booking.Room_ID = room.Room_ID WHERE Book_Status = 'Approve' 
    AND (room.Room_ID = '4' OR room.Room_ID = '5' OR room.Room_ID = '6' OR room.Room_ID = '7'
    OR room.Room_ID = '8' OR room.Room_ID = '9' OR room.Room_ID = '10') 
    AND Book_Date BETWEEN '".$_POST["From"]."' AND '".$_POST["to"]."' ORDER BY StartTime DESC");

    $query -> execute();
    $results = $query -> fetchAll(PDO::FETCH_OBJ);

    if($query->fetchColumn() > 0)
    {
        while($row = $query->fetch(PDO::FETCH_ASSOC))
        {
            $date = $row['StartTime'];
            $date2 = strtotime($date);
            $Start = date('h:i A', $date2);

            $date3 = $row['EndTime'];
            $date4 = strtotime($date3);
            $End = date('h:i A', $date4);

            $book = $row['Book_Date'];
            $book2 = strtotime($book);
            $DateBooked = date('d M Y', $book2);

            $result .='
            <tr>
            <td>'.$DateBooked.'</td>
            <td>'.$Start.'</td>
            <td>'.$End.'</td>
            <td>'.$row["Room_Desc"].'</td>
            <td>'.$row["Meeting_Description"].'</td>
            <td>'.$row["Admin_email"].'</td>
            <td>'.$row["Requested_by"].'</td>
            <td>'.$row["Remark"].'</td>
            </tr>';
        }
    }
    else
    {
        $result .='
        <tr>
        <td colspan="8">No Booking Room Found</td>
        </tr>';
    }
    $result .='</table>';
    echo $result;
}
?>

Can anyone knows what is the problem?

Your program is vulnerable. Read: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?r=SearchResults&s=1|1110.2313) — sticky bit, Nov 21 '19 at 01:23

Zhorov · Answer 1 · 2019-11-21T07:21:39.097

You need to consider the following:

The reason for your error is that you are calling fetchAll() and while ($row = $query->fetch(PDO::FETCH_ASSOC)) with the same statement. You should choose one of these two possible approaches and fetch your data correctly. PDOStatement::fetchAll() returns an array containing all of the remaining rows in the result set, while PDOStatement::fetch() fetches one row from the result set.
Remove the call to $conn->query(). Instead, use parameterized query to prevent SQL injection.

The next example, based on your code may help:

<?php
if (isset($_POST["From"], $_POST["to"])) {
    $result = '';

    $query = $conn->prepare("
        SELECT * 
        FROM booking 
        LEFT JOIN room ON booking.Room_ID = room.Room_ID 
        WHERE 
            Book_Status = 'Approve' AND 
            (room.Room_ID = '4' OR room.Room_ID = '5' OR room.Room_ID = '6' OR room.Room_ID = '7' OR room.Room_ID = '8' OR room.Room_ID = '9' OR room.Room_ID = '10') AND 
            Book_Date BETWEEN :from AND :to  
        ORDER BY StartTime DESC
    ");
    $query->bindParam(':from', $_POST["From"]);
    $query->bindParam(':to', $_POST["to"]);
    $query->execute();

    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        $date = $row['StartTime'];
        $date2 = strtotime($date);
        $Start = date('h:i A', $date2);

        $date3 = $row['EndTime'];
        $date4 = strtotime($date3);
        $End = date('h:i A', $date4);

        $book = $row['Book_Date'];
        $book2 = strtotime($book);
        $DateBooked = date('d M Y', $book2);

        $result .='
        <tr>
        <td>'.$DateBooked.'</td>
        <td>'.$Start.'</td>
        <td>'.$End.'</td>
        <td>'.$row["Room_Desc"].'</td>
        <td>'.$row["Meeting_Description"].'</td>
        <td>'.$row["Admin_email"].'</td>
        <td>'.$row["Requested_by"].'</td>
        <td>'.$row["Remark"].'</td>
        </tr>';
    }

    if ($result === "") {
        $result = '
        <tr>
        <td colspan="8">No Booking Room Found</td>
        </tr>';
    }   
    $result = '<table>'.$result.'</table>';
    echo $result;
}
?>

There are no more rows in the active result set. Since this result set is not scrollable, no more data may be retrieved

1 Answers1