Possible to select "Random" results?

[Anon]

Hi there,

I need to select rows from a table at random. This is the same businesses are not showing up at the top of the list every time. Is there a SQL statement for this?

Thanks,

-dr

[Anon]

Depends what database you are using.

[Anon]

I am using PHP4 with MySQL.

[Anon]

The newest version of mysql can do this
sorry, i don't know or can be bothered to look up the code but it's something like random (?).

If you don't have the correct version of mysql you can cheat and use php. I won't go into detail but it goes like this:

1. run query

2. find the number of rows returned
   $rows = mysql_num_rows($res);

3. Get random number between 1 and $rows
   srand ((double) microtime() * 1000000);
   $rand = rand(1,$rows);

4. goto the random row
   mysql_data_seek($res,$rand);

this will move the pointer to a random place
so you can mysql_fetch_array($res);

echo ... (array elements)

Hope this helps

Nathan W

[Anon]

Oops, I forgot $res is the pointer returned from the query:

$query = "select * from atable";
$res = mysql_query($query, $fp);

// $fp = connecion pointer

Good luck

Nath

[Anon]

You can do this in an SQL statement. e.g.

SELECT fields, id*0+rand() as random
FROM table
ORDER BY random
LIMIT 3

Change id to a numeric field in your table. Rand() on its own won't work, which is why the calculation is in front. Change the limit number to how many records you require.

[Anon]

In version 3.23 you can do this:

SELECT * FROM your_table ORDER BY RAND();

---John Holmes...

[Trojan]

sorry how shall i change the id to a numeric field,

i am not 2 good wid php, please could u give me an example?

Tro...