I'm writing a web app where people can add and vote on ideas. When outputting the ideas I want them to be ordered by their total vote count or time added, but always have a rank based on the vote count.
This is what I have now:
function get_ideas($status, $sortby, $count, $page){
$offset = ($page - 1) * $count;
$dbh = db_connect();
if($sortby === 'popular'){
$stmt = $dbh->prepare("
SELECT i.idea_id, i.idea_datetime, i.user_id, i.idea_title, i.idea_text,
i.idea_vote_count, u.user_name, @curRank := @curRank + 1 AS rank
FROM ideas i
JOIN (SELECT @curRank := :rankoffset) AS q
JOIN users u ON i.user_id = u.user_id
WHERE idea_status = :idea_status
ORDER BY idea_vote_count DESC
LIMIT :count
OFFSET :offset;");
} else {
$stmt = $dbh->prepare("HOW DO I DO THIS???");
}
$stmt->bindParam(':idea_status', $status, PDO::PARAM_STR);
$stmt->bindParam(':rankoffset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':count', $count, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = NULL;
$dbh = NULL;
return $result;
}
The code in the "if" block works as intended - it returns an array ordered by "idea_vote_count" with correct ranks.
However, I have no idea what the second statement should be. Ordering by time added would be achieved easily enough by just changing "idea_vote_count" in the "ORDER BY" clause to "idea_id". How do I get the ranks, though? I couldn't think of nor find a solution which didn't involve storing the ranks in the table itself.
Hopefully I've explained my problem clearly, but just in case:
How do I get a table like this:
idea_id | idea_vote_count
1 | 20
2 | 40
3 | 30
4 | 5
To produce output like this:
rank | idea_id | idea_vote_count
4 | 4 | 5
2 | 3 | 30
1 | 2 | 40
3 | 1 | 20
Also, I'm kind of new to PHP and MySQL, so if you spot any other problems, please, point them out.
I look forward to your advice. Thanks :)
EDIT: For Strawberry:
My ideas table:
CREATE TABLE `ideas`(
`idea_id` int NOT NULL AUTO_INCREMENT,
`idea_datetime` datetime NOT NULL,
`user_id` int NOT NULL,
`idea_title` varchar(48) NOT NULL,
`idea_text` text NOT NULL,
`idea_vote_count` int NOT NULL DEFAULT 0,
`idea_status` varchar(16) NOT NULL DEFAULT 'active',
PRIMARY KEY(`idea_id`),
FOREIGN KEY(`user_id`) REFERENCES users(`user_id`))
ENGINE=INNODB;
The sample ideas have been generated by following script. I have then manually changed the idea_vote_count of row 100 to 5.
$i = 1;
set_time_limit(150);
while (i<101) {
$datetime = date('Y-m-d h:m:s');
$dbh->exec(INSERT INTO `ideas` (`idea_datetime`, `user_id`, `idea_title`, `idea_text`, `idea_vote_count`, `idea_status`)
VALUES ('{$datetime}', $i, 'Title{$i}', 'Text{$i}', $i, 'active');
$i++
sleep(1);
}
This is what I ended up with after incorporating Strawberry's SQL into my function:
function get_ideas($status, $sortby, $count, $page){
$offset = ($page - 1) * $count;
$dbh = db_connect();
if($sortby === 'popular'){
$stmt = $dbh->prepare("
SELECT i.idea_id, i.idea_datetime, i.user_id, i.idea_title, i.idea_text, i.idea_vote_count, u.user_name, @curRank := @curRank + 1 AS rank
FROM ideas i
JOIN (SELECT @curRank := :rankoffset) AS q
JOIN users u
ON i.user_id = u.user_id
WHERE idea_status = :idea_status
ORDER BY idea_vote_count DESC
LIMIT :count
OFFSET :offset;");
} else {
$stmt = $dbh->prepare("
SELECT n.*
FROM (
SELECT i.idea_id, i.idea_datetime, i.user_id, i.idea_title, i.idea_text, i.idea_vote_count, u.user_name, @curRank := @curRank + 1 AS rank
FROM ideas i
JOIN (SELECT @curRank := :rankoffset) AS q
JOIN users u
ON i.user_id = u.user_id
WHERE idea_status = :idea_status
ORDER BY idea_vote_count DESC
LIMIT :count
OFFSET :offset) n
ORDER BY idea_id DESC;");
}
$stmt->bindParam(':idea_status', $status, PDO::PARAM_STR);
$stmt->bindParam(':rankoffset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':count', $count, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = NULL;
$dbh = NULL;
return $result;
}
As you can see, the function takes $count and $page as arguments (usually these have value of $_REQUEST['count/page']) and calculates the offset and limit based on them. This is very important, because I don't want to show all the ideas to users at the same time, I want to split them into several pages. However, this messes with the select/ranking SQL in the following way:
When $page = 1 and $count = 100 you get LIMIT 100 OFFSET 0 and the script works as intended - it shows the most recent row (row 100) as the first one ranked 96 (only rows 1, 2, 3, 4 have lower vote count), followed by the other recent rows ranked 1, 2, 3 and so on.
However, when $page = 1 and $count = 10 you get LIMIT 10 OFFSET 0 and the script outputs row 99 first, because it's the highest rated one, but not the most recent. Row 100 becomes the first result in the result set when $page = 10 (the lowest rated and OLDEST rows, despite the fact that row 100 is the most recent).
I could technically select the entire table and then handle the pagination in PHP, but I fear what the performance impact would be.
EDIT2: I have moved OFFSET and LIMIT into the outer SELECT and now everything works as it's supposed to. The side effect of this is, that the inner SELECT selects the entire table, but hopefully it won't grow too big for the server to handle. This is the solution I'm sticking with for the time being. It's based on Strawberry's SQL, so I'll mark that as the answer. Thanks, Strawberry :)
