I have created a custom table which stores 'post likes' in WordPress.
$sql[] = "CREATE TABLE IF NOT EXISTS {$table_prefix}like (
id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
post_id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL,
KEY recommenders (post_id, user_id)
) {$charset_collate};";
When a user clicks on a 'like' button on a WordPress post, a row gets added to the table which captures the user's ID and the post ID.
To get an array of all user IDs that have 'liked' a particular post I can do this:
$result = $wpdb->get_col( $wpdb->prepare( "SELECT user_id FROM {$table_prefix}like WHERE post_id = %d", $post_id ) );
I'd now like to do 2 things:
How can modify my query so that most recent rows added to the table are returned first in the $result array?
How can I do exactly the same as question 1 but return 5 rows only?
For 1: You can order them by your id in descending order by adding ORDER BY `id` DESC to the query.
For 2: Add LIMIT 0, 5 aswell:
$result = $wpdb->get_col( $wpdb->prepare( "SELECT `user_id` FROM `{$table_prefix}like` WHERE `post_id` = %d ORDER BY `id` DESC LIMIT 0, 5", $post_id ) );
ORDER BY as ccKep alluded to I would add a LikeDate Column to your Table as a DateTime(TimeStamp)and then use the ORDER BY on the LikeDate Column. This ensures that you count the return visitors, who may REALLY LIKE the post.