How do I add pagination or a pagination limit

Question

I want to know how to add a pagination limit to my query.

The query without the limit.

if(!empty($sqlParts))
{
  $sql = "SELECT * FROM tablename WHERE " .implode( " AND " , $sqlParts);                        
}

The query with the limit.

if(!empty($sqlParts)) 
{
  $sql = "SELECT * FROM tablename WHERE " .implode( " AND " , $sqlParts) ' LIMIT ' $offset ',' $no_of_records_per_page;                        
}

The idea is to display a certain amount of data from my database before you can go to the next page to display the rest.

Parse error: syntax error, unexpected '' LIMIT '' - this is my error with the code above.

The syntax is wrong. You have to concatenate the string with ".". — HonzaOne, Jul 02 '20 at 14:09
```$sql = "SELECT * FROM properties WHERE ".implode(" AND ", $sqlParts)." LIMIT $offset,$no_of_records_per_page";``` - Thank you my syntax was completely wrong. I just missed that part. Although I have to work pagination into the code. — Dewaldt van Rensburg, Jul 02 '20 at 14:35

score -1 · Answer 1 · answered Jul 02 '20 at 14:16

-1

Please print the SQL before the execution I think you are missing $offset or $no_of_records_per_page value

answered Jul 02 '20 at 14:16

Gajanan Tagadpalle

95
1
7

This is the code before building the query array ``` if (isset($_GET['pageno'])) { $pageno = $_GET['pageno']; } else { $pageno = 1; } $no_of_records_per_page = 3; $offset = ($pageno-1) * $no_of_records_per_page; ``` This is the code to count and get the amount of data it should display ``` $total_pages_sql = "SELECT COUNT(*) FROM table"; $result = mysqli_query($conn,$total_pages_sql); $total_rows = mysqli_fetch_array($result)[0]; $total_pages = ceil($total_rows / $no_of_records_per_page); `` – Dewaldt van Rensburg Jul 02 '20 at 14:45

score -1 · Answer 2 · answered Jul 02 '20 at 14:49

-1

Try this, It would be easier to fix if you print and send the query

if (isset($_GET['pageno']) && $_GET['pageno'] > 0) {
    $pageno = $_GET['pageno'];
} else {
    $pageno = 1;
}
$no_of_records_per_page = 3;
$offset = ($pageno-1) * $no_of_records_per_page;

if(!empty($sqlParts) && is_array($sqlParts))
{
  $sql = "SELECT * FROM tablename WHERE " .implode( " AND " , $sqlParts) ' LIMIT ' $offset ',' $no_of_records_per_page; 
}

answered Jul 02 '20 at 14:49

Gajanan Tagadpalle

95
1
7

Thank you @GajananTagadpalle . Worked like a charm. Although How do I implement Pagination? So that it displays the **$no_of_records_per_page** and if I go to the next page it goes on from the last record? – Dewaldt van Rensburg Jul 02 '20 at 15:55

How do I add pagination or a pagination limit

2 Answers2