0

I am simply trying to output the data from database with while loop and using a prepared statement. The code looks as follows:

$result = $conn->prepare("SELECT * FROM students WHERE class=?");
$result->bind_param("s",$class);
if($result->execute()){
    $result->store_result();
    if($result->num_rows>0){
        $res = $result->get_result();
        while($row = $res->fetch_assoc()){  //line no. 73, error is here
            $rollno = $row['rollno'];
            $name = $row['name'];
            $image = $row['image'];
            $mobile = $row['mobile'];
            echo '<tr>
                    <td>'.$rollno.'</td>
                    <td>'.$name.'</td>
                    <td><img src="images/'.$image.'" class="img-thumbnail" width="8%" height="8%"></td>
                    <td>'.$mobile.'</td>
                    <td>
                        <a href="all_students.php?class='.$class.'&&rollno='.$rollno.'&&action=view"><i class="fa fa-eye" aria-hidden="true"></i></a>&nbsp;&nbsp;
                        <a href="all_students.php?class='.$class.'&&rollno='.$rollno.'&&action=trash"><i class="fa fa-trash" aria-hidden="true"></i></a>
                    </td>
                </tr>';
        }
        $result->free();
    } else {
        echo '<tr><td colspan="5">No Records Found</td></tr>';
    }
    $result->close();
}

The above code is throwing error Fatal error: Call to a member function fetch_assoc() on boolean on line 73. I can run this successfully by using using query() function instead of prepared statement and with some slight modifications. But, the sql query then will be like "SELECT * FROM students WHERE class=".$class which has a risk of sql injection, thus I want to avoid it. I referred the answer of similar question here which advised to add get_result() function. I already added it in above code, but that too didn't work for me. What could be the problem? How can I achieve it with prepared statement? This is what I updated just now in above code:

$result = $conn->prepare("SELECT * FROM students WHERE class=?");
$result->bind_param("s",$class);
if($result->execute()){
    $result->get_result();
    if($result->num_rows){
        while($row = $result->fetch_assoc()){
            $rollno = $row['rollno'];
            $name = $row['name'];
            $image = $row['image'];
            $mobile = $row['mobile'];
            echo '<tr>
                    <td>'.$rollno.'</td>
                    <td>'.$name.'</td>
                    <td><img src="images/'.$image.'" class="img-thumbnail" width="8%" height="8%"></td>
                    <td>'.$mobile.'</td>
                    <td>
                        <a href="all_students.php?class='.$class.'&&rollno='.$rollno.'&&action=view"><i class="fa fa-eye" aria-hidden="true"></i></a>&nbsp;&nbsp;
                        <a href="all_students.php?class='.$class.'&&rollno='.$rollno.'&&action=trash"><i class="fa fa-trash" aria-hidden="true"></i></a>
                    </td>
                </tr>';
        }
        $result->free();
    } else {
        echo '<tr><td colspan="5">No Records Found</td></tr>';
    }
    $result->close();
}
hawk
  • 39
  • 5
  • you cannot use get result and store result at the same time – Your Common Sense Jun 05 '20 at 14:09
  • @YourCommonSense if i removed the `store_result()`, then the `num_rows` dont work – hawk Jun 05 '20 at 14:12
  • because you must call get result before num rows. – Your Common Sense Jun 05 '20 at 14:13
  • @YourCommonSense just tried it, but still `num_rows` not working. The loop is not running – hawk Jun 05 '20 at 14:16
  • Just make your variables consistent. you have result, res, what not. Just have ONE variable for the mysqli result and everything would work fine. Configure error repotrting and fix errors you introduced in your code – Your Common Sense Jun 05 '20 at 14:18
  • see a [proper example](https://phpdelusions.net/mysqli_examples/prepared_select#array) – Your Common Sense Jun 05 '20 at 14:24
  • @YourCommonSense Thanks for the resource, but after removing `store_result` and calling `get_result` before `num_rows`, not solving a problem, Could u see me updated code at bottom? – hawk Jun 05 '20 at 14:27
  • Yes I see that you didn't fix the names. Also, instead of "thanks" just actually *follow* the correct example. – Your Common Sense Jun 05 '20 at 14:28
  • @YourCommonSense I also want to use `num_rows`, but it requires `store_result`. I am not able to achieve both the things. – hawk Jun 05 '20 at 14:29
  • @YourCommonSense I have just updated the code and the `$result` is the consistent variable in it. Still, not a luck – hawk Jun 05 '20 at 14:32
  • Nope it is not consistent at all. Follow the correct example. – Your Common Sense Jun 05 '20 at 14:33
  • @YourCommonSense now i can say it is truely consistent, the same code is present at my side, bt still `num_rows` not working. Also, the example don't demonstrate the use of `num_rows` – hawk Jun 05 '20 at 14:37
  • nope it is not consistent because you are using the same variable for mysqli statement and mysqli result. And you don't actually need nub_rows if you think of it. the example I showed you works all right without it – Your Common Sense Jun 05 '20 at 14:39

0 Answers0