Mysql GROUP BY to get an overview of the voting system

Question

I want user_id 2 to have an overview of the last action taken by another user in his account, any of

• vote
• vote down
• confirm
• unconfirm

Each time the user takes an action, the row is added using 1 in the right column. Empty items are filled with 0.

The user is a voter, user_id is the person who voted on

| id | user | user_id | up | down | confirm | unconfirm |    date    |
+----+------+---------+----+------+---------+-----------+------------+
| 1  |  1   |    2    | 1  |      |         |           | 2014-11-03 |
| 2  |  1   |    2    | 1  |      |         |           | 2014-11-03 |
| 3  |  1   |    2    |    |  1   |         |           | 2014-11-03 |
| 4  |  1   |    2    |    |  1   |         |           | 2014-11-03 |
| 5  |  1   |    2    |    |  1   |         |           | 2014-11-03 |
| 6  |  1   |    2    | 1  |      |         |           | 2014-11-03 |
| 7  |  1   |    2    |    |      |    1    |           | 2014-11-03 |
| 8  |  1   |    2    |    |      |         |     1     | 2014-11-03 |
| 9  |  1   |    2    |    |      |    1    |           | 2014-11-03 | //THIS
| 10 |  1   |    2    | 1  |      |         |           | 2014-11-03 | // THIS
| 11 |  3   |    2    |    |  1   |         |           | 2014-11-03 |
| 12 |  3   |    2    | 1  |      |         |           | 2014-11-03 | //THIS
| 13 |  3   |    2    |    |      |    1    |           | 2014-11-03 | // THIS
+----+------+---------+----+------+---------+-----------+------------+

OUTPUT: last or last, last confirm or cancel from each user
- user confirmation 3,
- User 3 Up,
- User 1,
- User 1 will confirm.

SELECT * FROM table WHERE user_id = 2 GROUP BY .. ORDER BY id DESC

if($data['up'] == '1')
{
 echo "You have been voted up ... You've earned.."; 
}

else if($data['down'] == '1')
{
 echo "You have been voted down ... now you need to.."; 
}

...

Answer 1

, user_id, 1. :

SELECT *
FROM myTable
WHERE user_id = 2
ORDER BY date DESC
LIMIT 1;

, , action_id . , :

action_values:

| id |   action  |
+----+-----------+
| 1  | up        |
| 2  | down      |
| 3  | confirm   |
| 4  | unconfirm |

actions:

| id | user | user_id | actionID |    date    |
+----+------+---------+----------+------------+
| 1  |  1   |    2    |     1    | 2014-11-03 |
| 2  |  1   |    2    |     1    | 2014-11-03 |
| 3  |  1   |    2    |     2    | 2014-11-03 |
| 4  |  1   |    2    |     2    | 2014-11-03 |
| 5  |  1   |    2    |     2    | 2014-11-03 |
| 6  |  1   |    2    |     1    | 2014-11-03 |
| 7  |  1   |    2    |     3    | 2014-11-03 |
| 8  |  1   |    2    |     4    | 2014-11-03 |
| 9  |  1   |    2    |     3    | 2014-11-03 |
| 10 |  1   |    2    |     1    | 2014-11-03 |
| 11 |  3   |    2    |     2    | 2014-11-03 |
| 12 |  3   |    2    |     1    | 2014-11-03 |
| 13 |  3   |    2    |     3    | 2014-11-03 |

, . , . SQL .

, . . , N , , . .

, SQL Fiddle , . , , , :

SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mt
  WHERE mt.user_id = 2
    AND m.user_id = 2
    AND mt.user = m.user
    AND mt.date >= m.date
    AND (mt.up = 1 OR mt.down = 1)
) <= 1
AND (m.up = 1 OR m.down = 1);

, , , , 2, , , 0 1 . , , - . , /unconfirms, UNION - :

(SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mt
  WHERE mt.user_id = 2
    AND m.user_id = 2
    AND mt.user = m.user
    AND mt.date >= m.date
    AND (mt.up = 1 OR mt.down = 1)
) <= 1
AND (m.up = 1 OR m.down = 1))
UNION
(SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mt
  WHERE mt.user_id = 2
    AND m.user_id = 2
    AND mt.user = m.user
    AND mt.date >= m.date
    AND (mt.confirm = 1 OR mt.confirm = 1)
) <= 1
AND (m.confirm = 1 OR m.confirm = 1));

, , , , , , , .

Answer 2

, id , user_id .

SELECT MAX(id) AS id
  FROM table
GROUP BY user_id

, .

SELECT a.id, a.user, a.user_id, a.up, a.down, a.confirm, a.unconfirm, a.date
  FROM table AS a
  JOIN (
        SELECT MAX(id) AS id
          FROM table
         GROUP BY user_id
       ) AS b ON a.id = b.id
 WHERE a.user_id = 2

php-, $data['up'] .. .