[Resolved] SELECT random rows from SQL

tomandhannah

I want to pull 2 random rows from my database and print them as links.

I am using the following statement to SELECT rows from my MySQL database:
SELECT title, id FROM articles

Now, I want to put this data into an array
Let's say this query returns the following from the database:
ID TITLE
2 Nowhere
3 Headed Home
4 Another One
5 Last Title

I want to put these into an array as follows:

$input = array(2 => "Nowhere", 3 => "Headed Home", 4 => "Another One", 5 => "Last Title");

I think that PHP has already put this data into an array, but I don't know how to get it out.

I ultimately want to pull 2 random rows from this:
srand ((float) microtime() * 10000000);
$input = array(2 => "Nowhere", 3 => "Headed Home", 4 => "Another One", 5 => "Last Title");
$rand_keys = array_rand ($input, 2);
print $input[$rand_keys[0]]."\n";
print $input[$rand_keys[1]]."\n";

And have the titles appear as a link, using the id in the <a href>

Am I even close? Anyone have any ideas?

laserlight

Well, if you want to do it that way, then 1 way of getting the result of the query into an array named $input would be:

$input = array();
while ($row = mysql_fetch_array($query)) {
	extract($row);
	$input[$id] = $title;
}

superwormy

Why not just pull two random records from MySQL?

SELECT * FROM table ORDERY BY RAND() LIMIT 2

Sxooter

The problem with order by rand() limit 2 is two fold, one is that MySQL's rand function isn't very rand() (Postgresql's is much more random, having tested them both.)

the other is that on a large dataset, and order by random may wind up taking a long time, even with a limit statement, if the dbms doesn't know to shorten the list to just the first two. This is the case for Postgresql (is uses random(), not rand())

I don't know for MySQL. Try it on a row with 100,000 rows and see how it runs.

My favorite way is to get a select count(*), then do a random (0,$count-1) in PHP and use

select from table limit 1 offset $rand
or the mySQL syntax:
select from table limit $rand,1;

superwormy

You could also run the SHOW TABLES query which will give you a row count for teh table, and then use PHP to generate a random number between 0 and the number of rows.

Then select with a LIMIT [the random number here], 2

OR for SQL compliant syntax:

LIMIT 2 OFFSET [the random number here]