0

With a PHP code, I wish to order some MySQL results in a specific way.

I have two tables:

First table, called "post" with the following non-exhaustive content:

  • id
  • released_date
  • published_date
  • ...

Second table, called "votes" with the following non-exhaustive content:

  • id
  • post_id
  • user_id
  • status

If a user votes for an existing post, a record is made in the "votes" table, inserting the post id as post_id and the user id as user_id. The status is 1 for an upvote and 2 for a downvote.

One important thing to keep in mind though: some posts might never receive any vote at all, and 0 occurrence would be found in the second table, for instance!

So, now on my first page I order my posts with their amount of votes (upvotes-downvotes) the following way, and it works fine:

$post_req = mysql_query('SELECT * FROM post
WHERE NOW() > released_date
ORDER BY ((SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 1)-(SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 2)) DESC, published_date DESC
LIMIT 0, 5');

But on the next page (i.e a new iteration), I need to continue where I left, in terms of votes. Before I do that, I save the votes amount of the last post of the first page to pass it to the next one, under the variable $last_post_votes.

Now, this where I struggle: I need to look for posts which have a lower amount of votes than this variable in a new request. As follows, my failing attempt:

$post_req_1 = mysql_query('SELECT * FROM post
WHERE NOW() > released_date
AND ((SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 1)-(SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 2)) < "'.$last_post_votes.'"
ORDER BY ((SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 1)-(SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 2)) DESC, published_date DESC
LIMIT 5');

And then I tried something like that, which failed too:

$post_req_1 = mysql_query('SELECT post.*,
((SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 1)-(SELECT COUNT(*) FROM votes WHERE votes.poste_id = post.id AND votes.status = 2)) AS all_votes
FROM post, votes
WHERE NOW() > post.released_date
AND all_votes < "'.$last_post_votes.'"
ORDER BY all_votes DESC, post.published_date DESC
LIMIT 5');

The problem is clearly the condition upon searching results from another table in the SELECT.

Any help is greatly appreciated!

Thanks a lot in advance! :-)

Lois

EDIT:

I managed to make it work as I wished, by using a condition directly in the ORDER BY itself. Not sure it's super proper, but it seems to work:

$post_req_1 = mysql_query('SELECT * FROM post
WHERE NOW() > released_date
ORDER BY ((SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 1)-(SELECT COUNT(*) FROM votes WHERE votes.post_id = post.id AND votes.status = 2)) < "'.$last_post_votes_passed.'" DESC, published_date DESC
LIMIT 5');
Lois
  • 77
  • 8
  • exactly how would `order by (select ...)` work? that'd come down to something like `order by 5`, which is rather pointless. – Marc B Oct 16 '13 at 20:44
  • Are you trying to do Paging, if so you can you use LIMIT for that, so second page would be LIMIT(5,5) so only thing u need to save is offset – Borik Oct 16 '13 at 21:06
  • [Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). – zessx Oct 16 '13 at 21:48
  • @Borik Yes, but I implemented some endless scrolling. So after several iterrations, eventually, the page jumps to the next one. And if I'm not mistaken, using this technique might eventually result in viewing some posts which were already from the previous page, if some new ones are posted in the meantime, while the user is still browing down the first one. Correct? Which I want to avoid – Lois Oct 17 '13 at 13:24
  • Yes there is a potential with any Partial Data Set that you will see same row again, as data changes. If you trying to avoid this case and your Full set is not to big, you can try loading full Data Set and implementing paging with jQuery. – Borik Oct 17 '13 at 13:57
  • Thanks. Yes that might be an idea! Thanks again. – Lois Oct 17 '13 at 13:59

2 Answers2

0

If your first statement works fine, why don't you continue with that?

Aou start with the LIMIT clause of LIMIT 0,5 which gives you the first 5 records of the resultset. If you increase the first parameter to LIMIt it will show you the next 5 records, and so on. No need to fiddle around with interim variables...

// first page
SELECT ... LIMIT 0,5

// seond page
SELECT ... LIMIT 5,5

// 10th page
SELECT ... LIMIT 45,5

See also http://dev.mysql.com/doc/refman/5.0/en/select.html

ErnestV
  • 117
  • 1
  • 6
  • Thank you. Yes I could very well use this technique. But I implemented some endless scrolling. So after several iterrations, eventually, the page jumps to the next one. And if I'm not mistaken, using this technique might eventually result in viewing some posts from the previous page, if some new ones are posted while the user is still browing down the first one. Correct? Which I want to avoid. – Lois Oct 17 '13 at 13:18
0

If i am not mistaken is this what you are looking for?

SELECT p.id, IFNULL(COUNT(v.id), 0) - IFNULL(COUNT(v2.id), 0) AS `Votes`
FROM
posts p 
LEFT JOIN votes v on v.post_id = p.id AND v.status = 1
LEFT JOIN votes v2 on v2.post_id = p.id and v.status = 2
WHERE NOW() > p.released_date
ORDER BY `Votes` DESC;
Sajuna Fernando
  • 1,364
  • 9
  • 16
  • Thank you. But I couldn't make this work somehow. But even if, then I would need to place a condition on `Votes` (lower than my variable $last_post_votes – Lois Oct 17 '13 at 13:16