A question about limit and param (MySQL)

Wappler General Need Help
1

Hello everyone, I'm a little confused and would like to know something about how queries work when it comes to MySQL and params..

For what I know:
LIMIT :P1 is not an option, clearly understand why not.
But, how is this working?
image
In this case I can set the limit of the query
So:

image
image511×532 9.05 KB

image
image582×619 12.2 KB

Is there a workaround somewhere I'm missing?

I would like to use something like:

ORDER BY 
RAND(:P1) 
LIMIT :P2;

Thanks

2

In a custom query?

3

Thanks @Apple

It's not possible to use a param like :P1 on limit when using MySQL:

But also I can't set the RAND() on the paged query, all dissapear when a paged query turns into a custom one.

4

You can directly inject an expression in the custom query

{{ hello }}

You need to ensure "hello" variable is a number, else it's a security vulnerability (SQL Injection). Ensureness can be achieved by using the toNumber (paraphrased) formatter, or by using relevant validation beforehand

1 Like
5

I am totally surprised. I haven't even given it a chance!
More confused than before hahaha

Of course, it's working, and that's all that matters to me. Thanks once again @Apple

6

Do you know what are "prepared statements"?

Search about MySQL PDO, that's what Wappler uses behind the hood

Happy to help @franse!

1 Like
7

If it is any help, i had a similar issue in the very early days of Wappler (my first project actually) long before custom extensions. I needed to change records displayed every hour or so (it was property rentals featured properties)

As RAND if not very effective regarding optimisation and at that time RAND could not be used in standard queries, i added a field to the appropriate table called order.

I then added a schedule (actually a cron job as this was PHP, before node was even thought about) which wrote a random number (between bounds obviously) to the order field or each record effectively changing the order.

Then i could just use SORT on the order field to effectively randomise the records each hour.

Bit of a workaround but it actually worked very effectively

4 Likes