Random SQL function

[ceilingfish]

Hi,
I was contemplating pulling a randomised dataset from the database for a module I am working on, but this does rely on some clever cross platform sql randomisation code. I know the following works on Postgres:

Code: Select all

SELECT * FROM table ORDER BY random()

and I think this works on mySQL

Code: Select all

SELECT * FROM table ORDER BY rand()

but does anyone know how I could either differentiate between the two using adodb or some different syntax that'll work in both.

Any ideas anyone?

Tom

[ceilingfish]

I think I solved my problem.

Assuming that the config line value 'dbms' is generated from the dropdown in the installer there are three possible options {'postgres7','mysql','mysqli'}.

I checked on the above sql and it was correct so all I have to do is write a function that checks whether this string contains either 'postgres' (in which case the random function is 'random()') or 'mysql' (which means to randomise is 'rand()'). So something like this:

Code: Select all

        function RandomisationSql()
        {
                $dbms = $this->cms->config['dbms'];
                        
                if(strpos($dbms,'mysql') !== false)
                        return 'rand()';
                elseif(strpos($dbms,'postgres') !== false)
                        return 'random()';
        }