storing "dynamic" values in MySQL?

[henricar]

Hey guys, I'm new here so hello... but I'll get right to the point:

 

I have a table named POSTS, where I store user posts. In this table, I have a row named "Popularity". Now I would like the value of "Popularity" to be a function of the number of Likes, Dislikes, Views and Date posted, all of which are stored in respective tables (linked by Primary and Secondary keys).

 

I was wondering if it actually is possible to have the value of "Popularity" adjust automatically when a change in "Likes", "Dislikes" or "Views" occurs. And if not, what alternatives I have.

 

The ultimate reason I need to have this value stored in a table and up to date is that I wish to sort posts by "Popularity".

 

Many thanks !!!

[gristoi]

If your using mysql > version 5 you can write a mysql trigger to listen to inputs on the other tables and adjust the value accordingly. Have a read of ;

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

[henricar]

hey thanks for the reply! but this method seems quite advanced and I'm kind of a php noob.. would you be able to suggest another way I could attain this goal? thanks

[jcbones]

There is no better way, and also nothing like learning a new skill.  Otherwise this will take multiple queries, and cause your pages to slow down.

 

In the off hand that you want to go with a slower route, you could write separate queries for the likes, dislikes, views, and date_posted tables to run after you insert query finishes.

 

If you wish to create triggers, it is a simple as running this in phpMyAdmin, or similar software.

CREATE TRIGGER likes AFTER INSERT ON likes FOR EACH ROW UPDATE `POSTS` SET Popularity = Popularity + 1;
CREATE TRIGGER dislikes AFTER INSERT ON dislikes FOR EACH ROW UPDATE `POSTS` SET Popularity = Popularity - 1;

 

ETC, until you have everything calculating how you want.  This way, all you have to worry about in the script, is inserting the data to the respective tables.

 

 

[henricar]

great, thanks! will give the triggers a thourough read, and you're right: learning new skill = good

[henricar]

seem to have run into a little problem though :/... the algorithm for popularity is P = (20*likes -20*dislikes + views)/(today's date - date posted). Is it possible to make this work? like have a trigger occur daily to calculate the difference between today's date and the date posted?

[jcbones]

So, now your trigger will look something like:

DELIMITER ;;
CREATE TRIGGER dislikes 
AFTER INSERT ON Dislikes
FOR EACH ROW
BEGIN
DECLARE disliked INTEGER;
DECLARE liked INTEGER;
DECLARE date INTEGER;

SELECT COUNT(*) INTO disliked FROM dislike WHERE linked_key = 'NEW.linked_key';
SELECT COUNT(*) INTO liked FROM likes WHERE linked_key = 'NEW.linked_key';
SELECT DATEDIFF(NOW(),date_posted) INTO date FROM date_posted WHERE linked_key = 'NEW.linked_key';

UPDATE POSTS SET Popularity = ((20*liked)-(20*disliked))/date WHERE linked_key = 'NEW.linked_key';

END;;
DELIMITER;

 

I could help you more, but I would need some table structures.  I have no clue what columns link the tables together.

[henricar]

hey here is the exact stucture of the 3 tables concerned.

 

[attachment deleted by admin]

[henricar]

like I understand the code you previously posted but it still we be updating "popularity" only when a post get liked, viewed or disliked. I still need to popularity to fluctuate on a dailay basis given the spread between current date and date posted is growing by the day. any solution to this?

[jcbones]

You could set a crontab (*nix server), or event through the task manager (IIS server).  This will update based on criteria you set.