Closed Thread Icon

Topic awaiting preservation: MySQL PHP - weighting random row selections (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12779" title="Pages that link to Topic awaiting preservation: MySQL PHP - weighting random row selections (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: MySQL PHP - weighting random row selections <span class="small">(Page 1 of 1)</span>\

 
smonkey
Paranoid (IV) Inmate

From: Northumberland, England
Insane since: Apr 2003

posted posted 06-10-2003 16:10

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

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 06-10-2003 16:54

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

smonkey
Paranoid (IV) Inmate

From: Northumberland, England
Insane since: Apr 2003

posted posted 06-10-2003 17:02

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

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 06-10-2003 18:14
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.

quisja
Paranoid (IV) Inmate

From: everywhere
Insane since: Jun 2002

posted posted 06-10-2003 19:47

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...

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 06-10-2003 20:48

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

« BackwardsOnwards »

Show Forum Drop Down Menu