2

I'm a designing a website that orders results on its votes and ages.

I found the reddit algorithm and I think that is the best to use. However, I do not know how to implement this into php. I have searched google on how to do this but I cannot find any results. I don't know if it is just mean because I do not know exactly what I should be searching.

I know basic PHP however, is there a way of doing this in a simple way.

Is it possible to do it like this:

"SELECT * FROM table ORDER BY algorithm_here DESC";

The reddit algorithm is as follows:

Log10(Z) + ((Y*Ts)/45000) = rank

A = time posted

B = 00:00:001 am 1/1/2010

U = Up votes

D = Down votes



Ts = A-B


X = U-D


Y =

1 if x>0

0 if x=0

-1 if x<0


z = max(abs(x),1)
Improve this question

2 Answers 2

Reset to default
1

Well if its me, i will write an UDF in MySQL called may be reddit_algo or something and use it like

SELECT
    *,
    reddit_algo() as rating
FROM
    `table`
ORDER BY
    `rating`
LIMIT 30;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I know how to create functions in PHP e.g function test(){... However, would this work for the query? function rankingalgorithm($a, $u, $d) { $ts = $a - 1262304001; $x = $u - $d; if($x>0) $y = 1; elseif($x=0) $y = 0; elseif($x<0) $y = -1; $z = max(abs($x),1); $rank = log10($z)+(($y*$ts)/45000); print($rank); }
no creating a function in php is not at all advisable. Because if u do so, the only way to make it work properly is to load all the rows and pass each one thru the fn to generate the rank and then re-order the list using the ranks and then slice it to the needed page. this will work if u have only less no of rows like a 1000 rows or so in total. bt if u have more than 1 lakh rows and all. its impossible to run because php will run out of memory on loading all the rows itself. So the best way to do this is thru mysql. But u dont nessary have to go with UDF's as its considerably advanced.
so u can do either rite the funtion their itself or write a stored function. But UDF's are very much faster thn bth.
I'm not an advanced PHP user or MYSQL user so I do not know how to write a UDF for Mysql so is there an easier way or is that the easiest. P.S. Are you Indian because I noticed you used Lakh instead of 100,000.
check my next answer. i have wrote an stored function for you. how ever UDF functions are much more advanced and is hard to install and get it up, if are new to it. UDF's is not the easiest way to do it, but its definitely the best and optimum way to do it. PS: yes. I'm an Indian. :)
0

If you want the stored function then here is it. i just wrote it fast and didnt get time to check it fully. I definitely hope this works. :)

DELIMITER &&

DROP FUNCTION IF EXISTS reddit_rank &&

CREATE FUNCTION reddit_rank(time_posted TIMESTAMP, up_votes INT, down_votes INT) RETURNS NUMERIC(10,6)
    DETERMINISTIC

BEGIN
    DECLARE start_time TIMESTAMP;
    DECLARE Ts INT;
    DECLARE vote_diff INT;
    DECLARE y TINYINT;
    DECLARE z1 INT;
    DECLARE z INT;
    DECLARE rank NUMERIC(10,6);

    SET start_time = "2010-01-01 00:00:01";
    SET Ts = TIMESTAMPDIFF(SECOND,start_time, time_posted);

    SET vote_diff = up_votes - down_votes;

    IF vote_diff > 0 THEN
        SET y = 1;
    ELSEIF vote_diff < 0 THEN
        SET y = -1;
    ELSE
        SET y = 0;
    END IF;

    SET z1 = ABS(vote_diff);

    IF z1 >= 1 THEN
        SET z = z1;
    ELSE    
        SET z = 1;
    END IF; 

    SET rank = LOG10(z) + ( (y*Ts)/45000 ); 

   RETURN(rank);

END &&

DELIMITER ;

SELECT
    *,
    reddit_rank(`time_added`, `up_votes`, `down_votes`) as rank
FROM
    `table`
ORDER BY
    rank;

Hope this helps. :) .. if u have any doughts on how to use a stored function and all try google searching.

Then again. I still want to say it again, it is not adivisable to use a stored function or any of such thing if you have a large database. So i strongly recommend to write a UDF. If you don't know how to do it; then for the time being adjust with this function and when the load increases and you start making lots of revenue get someone to write a UDF function for you. ;) ..

Improve this answer

3 Comments

I'll try this out and see if it works. If it doesn't, I don't mind that much, I'll find a way. Anyway, thank you for all your help it is has helped my alot
you are welcome. :) .. by the way if it didn't work please dont mind getting back 2 me. :) .. im more than happy to offer any help i can :)
If it doesn't work, I will use a simple Bayseian algorithm instead of this "complex" algorithm.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.