Help choosing a random row from a table

[Anon]

I found this function at php.net under the quick reference from a visitor:

function randomrec($table, &$row, $db)
{

$sql = "SELECT FROM $table";
$result = mysql_query($sql, $db);
$numrecs = mysql_num_rows($result);
srand((double)microtime()1000000);
$recnum = rand(0, $numrecs-1);
$sql = "SELECT * FROM $table LIMIT $recnum, 1";
$result = mysql_query($sql, $db);
$row = mysql_fetch_array($result);

}

I've got a dilemna that the above posted solution won't solve. I've got a table, with some rows marked as "active" and others as "in-active".

I want to randomly select an ACTIVE row.

Above methods people have posted do not do this. They assume that all rows can be chosen.

To further complicate matters, I'd like to add a column that specifies a "probability to be chosen" (for example range 1-5). Basically I want some rows to have a higher probability to be chosen than others.

I've been wrapping my brain around this for days with no luck..can anyone help?

[Anon]

You can try to select rows from db by adding more constraints:
$sql = "select * from tb where row_name = \"actice\";";

For the prob., I have a stupid idea. You can generate one more random num (0-100)beforehand, then:

if ($ramdom < 5)
$sql = "select * from tb where row_name = \"actice\" and prob = \"1\";";
.....

elseif ($random <20)
$sql = "select * from tb where row_name = \"actice\" and prob = \"1\";";
.....

........

[Anon]

You can try to select rows from db by adding more constraints:
$sql = "select * from tb where row_name = \"actice\";";

For the prob., I have a stupid idea. You can generate one more random num (0-100)beforehand, then:

if ($ramdom < 5)
$sql = "select * from tb where row_name = \"actice\" and prob = \"1\";";
.....

elseif ($random <20)
$sql = "select * from tb where row_name = \"actice\" and prob = \"2\";";
.....

elseif ($random <50)
$sql = "select * from tb where row_name = \"actice\" and prob = \"3\";";
.....

........

Hope this can help you.

P.S. pls ignore the previous one

[Anon]

Doesn't anybody SEARCH THE FORUM anymore?

This has been asked at least two times, so there are plenty solutions around.

Anyway, if you are using MySQL 3.23, simply use:

SELECT * FROM table ORDER BY rand() limit 1;

[Anon]

To further complicate matters, I'd like to add a column that specifies a
"probability to be chosen" (for example range 1-5). Basically I want some rows
to have a higher probability to be chosen than others.

read the table into an array.

traverse the array, writing the elements into a second array a number of times equal to their probability.

select a random element from that second array.

voila.

--
rad

[Anon]

I would not read the intire table into the aray, just the row-ids.
Some tables can be huge....

Otherwise, damn fine way of doing it.

[Anon]

As the table gets larger and larger how efficient will this be?

[Anon]

I'm getting errors from MYSQL:

---

SELECT * FROM table ORDER BY rand() LIMIT 1

MySQL said: You have an error in your SQL syntax near 'rand() LIMIT 1' at line 1

[Anon]

Are you running version 3.23 of MySQL?

[Anon]

Yes