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.
--
Larry Garfield AIM: LOLG42
larry@xxxxxxxxxxxxxxxx ICQ: 6817012
"If nature has made any one thing less susceptible than all others of
exclusive property, it is the action of the thinking power called an idea,
which an individual may exclusively possess as long as he keeps it to
himself; but the moment it is divulged, it forces itself into the possession
of every one, and the receiver cannot dispossess himself of it." -- Thomas
Jefferson
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
