Posted by J.O. Aho on 01/08/06 17:36

Joe wrote:

> I need some help doing a easy php/sql solution to a random selection with
> rated stuff.
> I have a table like:
>
> Name Rated
> --------------
> Thompsen 500
> Milla 10
> Jensen 1
>
> Means that the name Thompsen appears 50 times as often as Milla and 500
> times as often Jensen.
>
> I now need some scripting that counts the total ratefactor like
> SELECT sum(Rated) FROM Table
> In this case 511
> And then make a random value between 1 and 511 and then print out the name,
> which in this case will be Thompsen 500 of 511 times (in avg)
>
> I need a quick way to find a name based on the Rated factors, any help?


Much depends on what sql server you are using and what version, if you have a
sql server that supports procedures (mysql 5), then you can create a procedure
that will fetch the result for you.

I have made a procedure called randrow which will check whats the total of all
Rated_Sum in the table and then use the RAND() to make a random value. In your
original table you had SUM(Rated_Sum)=511, take that times the RAND() and then
CEIL() the result, you should get a value between 1 and 511 (if you want 0 to
511 then use ROUND() instead).

After this we will loop through the table until the added values of Rated_Sum
are equal or the same as the random value we generated and we will return this
users name.

-- Next line is first in the procedure
DROP PROCEDURE IF EXISTS randrow;
delimiter //
CREATE PROCEDURE randrow(OUT result CHAR(20))
BEGIN
DECLARE valueadd INT DEFAULT 0;
DECLARE rowsno INT DEFAULT 0;
DECLARE temp INT DEFAULT 0;
DECLARE curs1 CURSOR FOR SELECT Rated_Sum,Name FROM RateTable;
SELECT @randvalue:=CEIL(RAND(NOW())*SUM(Rated_Sum)) AS RandNo FROM RateTable;
OPEN curs1;
REPEAT
FETCH curs1 INTO temp,result;
SET valueadd := valueadd + temp;
UNTIL (@randvalue <= valueadd) END REPEAT;
CLOSE curs1;
END
//
delimiter ;
-- previous line was the last line

This we can do once directly in the database where we want to use this procedure.

From the PHP we will now need to call two SQL statements, the first to call
the procedure we made and store the return value in a variable which we will fetch

CALL randrow(@a);
SELECT @a;

I guess this will be one of the faster ways to do the whole thing, as if you
do it on the php side, you will have to fetch the whole table and if the web
server where you run the script is another one than the one that runs the
database, then you may notice slowness as the whole table has to be
transported over the network or in worst case over internet.
With the procedure method, only the result would be transported, something
that maybe is as much as 80 bytes long compared with 5000 row with each upto
85 bytes, which would be around 415kb in worst case.



//Aho

[Back to original message]