At 2:49 PM -0600 3/4/07, Larry Garfield <larry@xxxxxxxxxxxxxxxx> wrote:
On Sunday 04 March 2007 1:15 pm, Mike Shanley wrote:
Stut wrote:
>> I have a sidebar on my site that gets a few random articles from that
>> table, prints the titles, small blurbs, and a link. The link goes to
>> the main article. I get the random IDs outside of mysql because I've
>> made it more likely for newer articles to be chosen than older
>> ones... This, accomplished via ID, because it's much easier to SELECT
>> count(*) and slant my randomization to the last 25% of ID numbers
>> than any other way I can think of...
>>
>> Of course, this means that having holes results in empty sidebar
>> boxes... And that's not too good lookin...
>
> How are you selecting random entries? A common way to do this is to
> use the MySQL rand() function, but that is exceedingly slow.
>
> I would suggest that you get a list of (or a subset of) the IDs in the
> table, use PHP to randomly select however many you need and then get
> the full data for those. You can't rely on the IDs, and the work
> involved in resetting all the IDs such that they're sequential without
> gaps is not worth it.
I use mt_rand(0,$max_rows) to get each of my values and send the call.
Getting an array of IDs sounds alright for now, but also sounds like it
gets increasingly slower and the table is expanded. I'll give it a try
though.
As Stut said, "holes" in the sequence are not a problem, they're a
feature. :-) They are guaranteed only to be a unique id. You have no other
guarantee about them, including the order in which they exist in the
database. It is perfectly legal in SQL to add 5 records in order to a table,
get an auto_increment added for each, then select the whole table and get
them in a non-numeric order unless you explicitly order them. The ID is for
reference purposes only, not for ordering or anything else.
What I've done in the past for selecting "three random items from the last 10"
(which sounds close to what you're doing) is something like this:
$result = mysql_query("SELECT * FROM foo ORDER BY timefield DESC LIMIT 10");
$records = array();
while ($record = mysql_fetch_object($result)) {
$records[] = $record;
}
array_shuffle($records);
for ($i=0; $i < 3; ++$i) {
$use[] = $records[$i];
}
Now you have an array, $use, that is 3 random entries from the last 10,
ordered by a time field. The unique ID is irrelevant to that, as it should
be.
This is getting to be more of a MySQL question, but to emphasize
again...the autonumbered sequence # is *only* to provide a unique ID.
It is normally used as a 'surrogate key' in the database, and is used
to link other tables to this one. If you renumber this table, that
means you would have to update all tables this refers to.
This set of queries should do the trick, though (untested; need to
doublecheck edge cases of max/min id, 0 & 0.999999.... random #'s):
select max(id) from your_table into @max_id
select * from your_table where id > ceil(rand() * @max_id) limit 1
For more info, see:
http://dev.mysql.com/doc/refman/4.1/en/user-variables.html
http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html
-steve
PS. If you *really* wanted to renumber the database, you should be
able to drop and then re-add the auto_increment column, but that's a
bad idea (tm) and don't tell anyone I told you how to do it :)
--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center sbedberg@xxxxxxxxxxx |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
