Mysql Limit Offset not working

Question

Here is my query, which i am using for pagination

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing   
FROM Data_TLS_builders as email_list 
LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN    
LIMIT 120 OFFSET 150

Rather than starting at 120 and finishing at 150, which should display 30 results, mysql is returning 120 results and ignoring the OFFSET. I have tried LIMIT 120,150 and still the same?

Any idea how to solve?

Answer 1

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing FROM Data_TLS_builders as email_list LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN LIMIT 30 OFFSET 120

Limit specifies number of records.

OFFSET specifies upto how many records it should skip.

The above query returns 30 records from 121.

Answer 2

You have to switch the limit parameters.

Solution 1:

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing FROM Data_TLS_builders as email_list LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN LIMIT 30 OFFSET 120

Solution 2:

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing FROM Data_TLS_builders as email_list LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN LIMIT 120, 30

You can also have a look into the documentation: http://www.w3schools.com/php/php_mysql_select_limit.asp.