Re: Random PostreSQL
| From: | Doug Semig | Date: | Wed, 02 Aug 2000 22:08:39 +0000 |
| Subject: | Re: Random PostreSQL | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-9834@lists.php.net to get a copy of this message | ||
I think that the explanation of the "10" in the call to oidrand() was a
hint that you have to calculate that parameter.
It just seems to me that when someone says that an example parameter of 10
means that each row will have approximately a 1 in 10 chance of being
returned...they are implying that you need to figure out what number you
need there to return approximately the number of rows you need.
For example, if your table has 750 rows in it and you want 6 rows out of
it, you have to give each row a 6 out of 750 chance of being returned.
This is an easy formula to figure out. You divide the number of rows by
the number of rows you want returned. Since 750 divided by 6 is 125, 125
is the number you should be using in the call to oidrand(). Remember, 750
is just an example...I have no way of knowing how many rows are in your table.
So in PHP, you'd do something like this (obviously this is just an untested
mock-up...you have to replace the things within angle brackets and
customize for your own variable names, etc.):
$result = pg_exec($dbconn, "SELECT COUNT(oid) FROM table WHERE <your
complex where clause>");
$data = pg_fetchrow($result);
$numrecs = $data[0];
pg_freeresult($result);
$seed = (double)microtime()*1000000;
$result = pg_exec($dbconn, "SELECT oidsrand($seed)");
pg_freeresult($result);
$myfactor = (int)($numrecs / 6);
$result = pg_exec($dbconn,
"<your SELECT ... WHERE ... AND oidrand(oid, $myfactor)");
and go from there.
I did some testing on a table with just over 750 rows in it, and it looks
like you have to request more rows than you really want and use LIMIT. I'd
probably do that AND put in some processing to requery if the number of
rows returned was less than the number of rows required. So I would modify
the above PHP-esque code to be something like this:
$result = pg_exec($dbconn, "SELECT COUNT(oid) FROM table WHERE <your
complex where clause>");
$data = pg_fetch_row($result);
$numrecs = $data[0];
pg_freeresult($result);
$seed = (double)microtime()*1000000;
$result = pg_exec($dbconn, "SELECT oidsrand($seed)");
pg_freeresult($result);
$rowsrequired = 6;
$rowsrequested = (int)($rowsrequired * 1.75);
$myfactor = (int)($numrecs / $rowsrequested);
$rowsreturned = 0;
$attempt = 0;
while ($rowsreturned != $rowsrequired AND attempt < 5) {
$attempt++;
pg_freeresult($result);
$result = pg_exec($dbconn,
"<your SELECT ... WHERE ... AND oidrand(oid, $myfactor)
LIMIT $rowsrequired");
$rowsreturned = pg_numrows($result);
}
if ($rowsreturned == $rowsrequired) {
/* display the stuff */
}
else {
/* display an error message about not being able
to get the stuff out of the database */
}
Well, of course you'd make it more robust with error checking and all...
But I think this is more like what probably needs to be done, anyway. Hope
this helps.
Doug
joe@jWebMedia.com was heard at 10:59 AM 8/2/00 -0500 to say:
>I tested the oidrand(oid, 10) and it did work. It did give me random
>results, however, i was unable to use it because I need 6 results
>always. It was giving me a random number of results as well as a random
>selection of results. Maybe someone knows how to get exactly six results
>- I'm new to PostgreSQL...
>
>Joe
>