Topic awaiting preservation: MySQL PHP - weighting random row selections (Page 1 of 1)

I'm looking for a way to randomly select rows from a MySQL database, giving preference to more recent additions in the database.

Apparently MySQL has some sort of random row function built in - but I need it weighted. Also it must never duplicate, but i'm guessing that is a simple (if blah = any of blah then choose again type thing).

Help and advice would be great - if you have something that can do this already that would be great too, I'm kinda pushed for time.

Thanks

smonkey: You want it random but with some kind of weighting towards more recent entries?!

I have no idea how to do it but you'd probably need to grab the results from the database into an array then randomise the array and then....... urm......

Why?

___________________
Emps

FAQs: Emperor

Why? well because on a site i'm working on there is supposed to be four random images chosen from a database of past projects - obviously random is good, but if the randomness is weighted slighty towards the more recent projects then the more current and therefore better stuff will be shown more often - makes perfect sense

visit my CryoKinesis Online Gallery

quote:

---

makes perfect sense

---

Nope. Not at all

Basically, if there's is work that's not good enough for you to want to show it, then it shouldn't be in there at all.

However, that aside, what you could do is this: grab the X most recent entries with MySQL, and then randomize them with PHP.

Give the most recent entries a points score in a multidimensional php array, for example:
Latest - 0.10pt
One before that - 0.09pts
One before the one before the latest - 0.08pts
....
9th most recent - 0.01pts

Then give each entry a random number in another dimension of the array. Add this to the points score and the most recent entries have a higher chance of being the biggest number. Article with biggest number is displayed. There will probably a more efficient way of doing this, but off the top of my head...

yeah... usual strategy for this is
a)
select id, fieldsToWeightBy from myTable
<?php
$weightedIds = array()
foreach ($aRow)
{
$weightedIds[$aRow['id']] = calculate_value_based_on_fieldsToWeightBy($aRow['fieldsToWeighBy']);
}
asort($weightedIds);
$counter = 0;
foreach ($weightedIds)
{
print "<img ...">;
if ($counter == 4) break;
}

so long,

Tyberius Prime