tip_id, challenge_id, user_id, ip_address, tip_date, tip_text
The tips can be voted up or down, I store vote data in another table, which looks like this:
tip_id, ip_address, vote_date, vote
The similar column between the two is tip_id. Vote is a bool, 0 means a vote down, 1 means a vote up.
My page will initially display one tip per challenge, but there will be a page that will show all tips for the specified challenge. In an effort to reduce my query count, I devised this query:
Expand|Select|Wrap|Line Numbers
- SELECT
- V.vote AS vote,
- T.tip_id AS tip_id,
- T.tip_date AS tip_date,
- T.tip_text AS tip_text
- FROM votes V
- LEFT JOIN tips T
- ON V.tip_id = T.tip_id
- WHERE T.challenge_id = 1
- ORDER BY T.tip_id ASC
a: 3 up, 2 down (diff: 1)
b: 4 up, 1 down (diff: 3)
Then b would come first and a would come second, since b has a higher overall score.
I really don't care how complicated the query is, I just can't think of how I could do it in PHP.
Oh, and in addition to the overall rating I also need the individual up and down values. Using the above example for b, I'd like this result:
Expand|Select|Wrap|Line Numbers
- up | down | rating | tip_id | tip_date | user_id | tip_text
- 4 | 1 | 3 | 1 | 12345678 | 1 | "This is some text"
Edit: As a side note here's the query I'm currently using to get the up / down counts for a specified tip:
Expand|Select|Wrap|Line Numbers
- SELECT
- count(CASE WHEN vote = 1 THEN 'up' ELSE NULL END) AS up,
- count(CASE WHEN vote = 0 THEN 'down' ELSE NULL END) AS down
- FROM votes
- WHERE tip_id = 1
Expand|Select|Wrap|Line Numbers
- SELECT
- count(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
- count(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
- T.*
- FROM votes V, tips T
- WHERE V.tip_id = 1
Edit 2: I should also note that if a challenge ID has more than one tip associated with it, then the query should return each tip in it's own row. A problem I'm having right now is that I'm getting a total count of up and down votes for all tips for the specified challenge, in a single row. The problem with the two queries I posted in the first edit is that they perform the query based on the value of the tip_id, which means I can only get 1 tip at a time. I need to return all tips for the challenge, so I need to perform the query using the value of challenge_id.