Random record issue, SQLite and MySQL incompatibility

Wappler General How To
#1

Anyone any ideas on this one?

I use a simple custom query to select a random record but differences between SQLite and MySQL are causing issues.

For SQLIte you have to use random()

Select id from tablename order by random()

But for MySQL i have to user rand()

Select id from tablename order by rand()

I have tried setting $_ENV variables like this:

image

and

image

and modifying the query to

image
image713×605 11.3 KB

But this always returns the first record suggesting the order by is being ignored
So i have to manually set the query when in development then remember to change the query before deploying to Production then change back after

Anyone any ideas how to get around this?

#2

You can’t use dynamic column names or sorting order based on parameters in your custom queries.
Same topic discussed here: Creating Custom SQL Queries

#3

OK, that explains it,
also tried placing 2 different server connect’s inside a conditional regions on page, didn’t work either

#4

Added API action to exclude list in system settings, guess that will have to be the solution