I have a MySQL database that has a lot of data in it and my client would like to be able to see more than 200 values. My idea was to use OFFSET in MySQL to dynamically show a different set of 200 rows. I am however having trouble getting the OFFSET to work in bubble, even when it is static.

Right now I have this:

SELECT *, 6371 * 2 * ASIN(SQRT(
POWER(SIN((LAT - abs(?)) * pi()/180 / 2),
2) + COS(LAT * pi()/180 ) * COS(abs(?) *
pi()/180) * POWER(SIN((LNG - ?) *
pi()/180 / 2), 2) )) AS distance
FROM fullData
WHERE COMPANY = ? AND License_Line LIKE ?
ORDER BY distance
LIMIT 200
OFFSET ?;

When I take out the line with OFFSET, this works perfectly. But if I do it this way, bubble tells me there is a MySQL error despite other MySQL Syntax checkers showing that it is functional. I get the same error when I use a static value and I get the â€śplease limit to 200â€ť error when I try to use the â€śLimit ?, 200â€ť syntax. If anyone has any advice, thoughts, or questions, please let me know. Thank you in advance for any help.

I am hoping this brings the topic back up. I sent a bug request and was told it isnâ€™t a bubble side error and that they canâ€™t help me. If anyone has any thoughts on this I could really use some help.

Another way to get chunks of 200 rows at a time is to add a â€ś> primary-keyâ€ť condition to the WHERE clause. This works well if you have a primary key set to auto-increment. If you donâ€™t have that in your MySql DB, it would be simple to add. So for Benâ€™s SQL above, letâ€™s assume an auto-incrementing primary key called â€śidFullDataâ€ť. The SQL connector code would then look like this (only change is to the WHERE clause):

SELECT *, 6371 * 2 * ASIN(SQRT(
POWER(SIN((LAT - abs(?)) * pi()/180 / 2),
2) + COS(LAT * pi()/180 ) * COS(abs(?) *
pi()/180) * POWER(SIN((LNG - ?) *
pi()/180 / 2), 2) )) AS distance
FROM fullData
WHERE idFullData > ? AND COMPANY = ? AND License_Line LIKE ?
ORDER BY distance
LIMIT 200
OFFSET ?;

Each time you want to get another 200, you use the same connector query and pass the last idFullData (just sort the list in Bubble desc on idFullData and grab the 1st value) as a parameter.