Mysql GROUP BY to have an overview of voting system

Question

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

1. vote up
2. vote down
3. confirm
4. unconfirm

Each time a user takes an action a line is added with 1 in the right column. Blank elements are filled with 0.

user is the voter, user_id is the person 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: the last up or down, the last confirm or unconfirm from each user
- User 3 confirmation,
- User 3 Up,
- User 1 up,
- User 1 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

To get the latest action taken on a user, you can just pull all rows with that user_id, order by the date in descending order, and limit by 1. Try this:

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

ON ANOTHER NOTE

If I can comment on your table design here, I really think you can put the various actions into their own table, and have an action_id column in your table. In other words, it would look like this:

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 |

This would make your query a little bit different, but it definitely normalizes things a little better and helps to avoid insertion and update anomalies. If you're interested in this at all, please see this SQL Fiddle for an example of how the query would change.

EDIT

In regards to getting the latest action, see my answer here. It might help, as it discusses getting N cases from a group, which is kind of what you are trying to do here. You want to get the single latest case from each group.

So, I tried to do that in SQL Fiddle and took some trial and error, but I was able to get it working. Unfortunately, I was unable to find a way to solve this all in one in shot, but I was able to come up with this query that gets the latest up or down from each user:

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);

What the nested part does, in short, is get rows where user 2 is voted on, groups by the user who voted, filters for the largest date, and limits the result to 0 or 1 rows. I added the up and down conditions here so that it only pulls rows where the action is an up or down vote. Easily enough, you can write the opposite query for confirms/unconfirms, and UNION the two together, like this:

(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));

I am playing around with how to shorten this query, but at this given moment it is the best I have, and it is working, so hopefully this will get you moving forward again.

Answer 2

Assuming your id is an autoincrement column, you can get the most recent id for each user_id as follows.

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

You can then join that subquery to the original table to extract the data for the most recent entry.

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

You then can use that query with your php code, $data['up'] etc. That should give you a good way of presenting the most recent transaction.