Monday, April 29, 2013

SQL?

Looking through the functions for the like/dislike plugin I've found I don't know nearly as much SQL as I should know.  I have an O'Reilly book for beginning SQL but I never cracked it because I thought I could just figure how it works and code it without having to read anything.

Well here's the original query from the GetLikeCount function:
"SELECT SUM(value) FROM {$wpdb->prefix}memepls_like_dislike WHERE post_id = '$post_id' AND value >= 0"

Now that I've typed it out I understand it for some reason even though I couldn't before even after looking through the http://techonthenet.com/sql/ site.  The SELECT SUM(value), sums up all the numbers from the value column (which is either 1 or -1).  The FROM {$wpdb->prefix}memepls_like_dislike, just points to the database you're pulling the data from.  And the WHERE post_id = '$post_id' AND value >= 0, makes sure that it's pulling all the values from that certain post ($post_id is defined in the functions parameters) and makes sure that it only pulls all the 1s and not the -1s.

For the GetLevelCount, GetExpCount, and GetPointsCount functions, I simply need to select the value for each of these columns and make sure it's from the correct user ($user_id).  So that means for grabbing the current level I would do:
SELECT cur_lvl FROM {$wpdb->prefix}memepls_level_points WHERE user_id = '$user_id'

Alright, now that I got that I got the queries to pull things from the db I need to make some queries to update the row for each user's points column when:
  • The user levels up.
  • The user gets a like on his post.
    • User get's 20 for each level up, and 3 for each like.
I need to update the exp column when:
  • The user gets a like on his post.
  • The user gets a dislike on his post.  (You can lose exp for posting gay shit unlike with points)
  • The user posts something.
    • User get's 10 exp per post, 3 for each like, -3 for each dislike.
I need to update the level column when:
  • A certain amount of user exp is achieved.  So I'd want to use the getExpCount function right after updating the experience column.  Then if that number of exp is over a certain amount determined by the leveling algorithm it will update the level to a higher amount.
    • Leveling algorithm is simple and works like so: you multiply the amount of exp it takes to achieve the previous level by 1.5 and strip any decimals.
    • For example, if it takes 10 exp to get to level 1, it takes 22 to level 2 and 33 to level 3.
Now that I've got all these thoughts I had sorted I can finally put them into code.  I'm keeping this post just for my SQL though (I know, it's surprising I'm even bothering with organization), so I'm just going to write out the queries I'll use for updating.  I'm also going to write the queries to create a row if there isn't already one for that specific user.  The php will come either tonight or tomorrow.

INSERT INTO {$wpdb->prefix}memepls_level_points SET user_id = '$user_id', cur_level = '$cur_level', exp ='$exp', points = '$points'
This will be used in a separate function that's called if the users stats aren't in the db already.

UPDATE {$wpdb->prefix}memepls_level_points SET user_id = '$user_id', cur_level = cur_level + '$cur_level', exp = exp + '$exp', points = points + '$points'
Again, this will be used in a separate function to update the cur level, exp and points.  Both of these functions will be in the functions.php.

A separate php file will need to be made to use these functions and to point the javascript file too.  I'm too tired to focus on this any longer so the php will come tomorrow.

Oh and lastly, this is the $wpdb var explained: http://codex.wordpress.org/Class_Reference/wpdb

No comments:

Post a Comment