Using offset and limit by in PHP with prepared statements without bind parameter error

Question

I am having trouble with the following sql query in PHP:

$page = 0;
$rows = 10;
$stmt = $mysqli->prepare("SELECT equipment_id, equipment_type, equipment_name FROM equipment_database WHERE equipment_institute_id=? AND equipment_institute_branch_id=? AND equipment_status=? AND CONCAT (equipment_name, equipment_type) LIKE ? ORDER BY equipment_name OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;");
    $stmt->bind_param("ssssii", $institute_id, $institute_branch_id, $active_status, $search_term, $page, $rows);
    $stmt->execute();

I am trying to be able to select 10 rows at a time using a previous and next button. This I want to achieve using OFFSET ? ROWS FETCH NEXT ? ROWS ONLY however after running the code I keep getting a fatal error: atal error: Uncaught Error: Call to a member function bind_param() on bool in

I tried variations using ssssss instead of ssssii and variations of how the query is formulated. Therefore I suspect perhaps a more simple solution I am blind to. Any ideas?

[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Jun 20 '20 at 20:44
As far as I know MySQL doesn't have that syntax with `ROWS FETCH NEXT ? ROWS ONLY` — Dharman, Jun 20 '20 at 20:45
@Dharman You see, I thought is was something silly from my side. What would the correct syntax be? — Roelof Coertze, Jun 20 '20 at 20:46
Take a look here https://www.mysqltutorial.org/mysql-limit.aspx/ — Dharman, Jun 20 '20 at 20:48
@Dharman Thanks! Silly mistake but one worth showing to the world for newbies like me! — Roelof Coertze, Jun 20 '20 at 20:56

Using offset and limit by in PHP with prepared statements without bind parameter error

0 Answers0