paginate search results

**styxlawyer** · 02-07-2016, 12:24 PM

You have set a limit in your search query on line 42:

Code:

        $result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, $per_page");

The value of '$per_page' is set to 5 in line 19 so that query only returns five results! If you want to return all the matching results starting from '$start_from' then you must change LIMIT to OFFSET. Your query should look like this:

Code:

        $result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id OFFSET $start_from");

See here for the usage of LIMIT and OFFSET in MYSQL.

**ianhaney** · 02-07-2016, 02:19 PM

I just modified the query but gave the error below when I clicked search

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 0' at line 1' in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php:45 Stack trace: #0 /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php(45): PDOStatement->execute() #1 {main} thrown in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 45

line 45 is

Code:

$exec = $result->execute();

shall I paste the whole code again?

**styxlawyer** · 02-07-2016, 02:24 PM

Hmmm, it doesn't seem to like an OFFSET of 0. Try this in your query:

Code:

OFFSET $start_from+1

**ianhaney** · 02-07-2016, 02:51 PM

Sorry I modified the query and now got this error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 0+0' at line 2' in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php:46 Stack trace: #0 /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php(46): PDOStatement->execute() #1 {main} thrown in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 46

On line 46 is

Code:

$exec = $result->execute();

the query looks like

Code:

$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id 
		OFFSET $start_from+0");

**styxlawyer** · 02-07-2016, 03:09 PM

First try:

Code:

$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id OFFSET $start_from+1");

and if that doesn't work try:

Code:

$startxx = $start_from+1;
$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id OFFSET $startxx");

**ianhaney** · 02-07-2016, 03:16 PM

I tried the first way and got the following error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 0+1' at line 1' in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php:45 Stack trace: #0 /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php(45): PDOStatement->execute() #1 {main} thrown in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 45

So then tried the second way and got the following error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 1' at line 1' in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php:46 Stack trace: #0 /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php(46): PDOStatement->execute() #1 {main} thrown in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 46

**styxlawyer** · 02-07-2016, 03:46 PM

I've had a scour through Google and it looks as though OFFSET on its own is invalid. The only way to get all the records from a particular start point will be to set an impossibly large value for number of records retrieved. Like this:

Code:

$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, 1000000");

**ianhaney** · 02-07-2016, 04:19 PM

Originally Posted by styxlawyer

I've had a scour through Google and it looks as though OFFSET on its own is invalid. The only way to get all the records from a particular start point will be to set an impossibly large value for number of records retrieved. Like this:

Code:

$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, 1000000");

This worked but lost all the results, it does not paginate them to 5 per page for example?

**styxlawyer** · 02-07-2016, 04:32 PM

Without access to your live page, I can only guess what's going on.

**ianhaney** · 02-07-2016, 04:41 PM

Sorry I did not mean lost all the records, I meant it displays all the records on one page insteading of paginate them to 5 per page, sorry I can't give access to the live page as has customer data on and our own business info, sorry