Forum Documentation Showcase Pricing Learn more

Problems with offsets in SQL plugin


#1

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.


#2

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.