Display SUM and all entries of a database

[Donald.]

Hey guys,

I've been trying to think about a solution to this problem without having to put a query within a while loop displaying the results of a query (querynception) but I guess my skills are not there yet. I'm sure there is a way to display the sum of the total points and each entry a user has after running a search.

 

Here's a small explanation of how the table is set up.

logs

+----------+------------+--------+

|  usr_id  |    event  | points |

+----------+------------+--------+

|        1 |    event 1 |      5 |

|        2 |    event 3 |    10 |

|        3 |    event 2 |      7 | 

|        1 |    event 4 |      3 | 

|        1 |    event 2 |      7 | 

|        1 |    event 1 |      5 | 

+----------+------------+--------+

 

I want have a search function that display the following when I search for usr_id=1.

1 has a total of 20 points.

 

event 1 -- 5 points

event 4 -- 3 points

event 2 -- 7 points

event 1 -- 5 points

 

I know I can use the SUM(points) as totalPoints to get the sum but how can I echo it? Am I missing something in the query and how can I echo it without having a query inside another?

mysql_query("SELECT  usr_id, event, points, SUM(points) as totalPoints FROM logs WHERE usr_id='{$usr_id}'") or die(mysql_error());

 

Thanks!

[Barand]

here's one way

SELECT user_id,
    SUM(points) as total,
    GROUP_CONCAT(event,' -- ', points,' points' SEPARATOR '<br>') as events
FROM logs
WHERE user_id = 1
GROUP BY user_id

[Donald.]

here's one way

SELECT user_id,
    SUM(points) as total,
    GROUP_CONCAT(event,' -- ', points,' points' SEPARATOR '<br>') as events
FROM logs
WHERE user_id = 1
GROUP BY user_id

Thanks, I had no idea this was possible, its has definitely helped me. However, I'm experiencing one little problem which I think might have something to do with the amount of data it can hold. Some queries are returning everything as they should but some get cut right in the middle. Do they have a character limit? Because some users have more than 80 events and generate quite a long list.

[Barand]

there is a 1024 char limit on the concatenated data length. I guess you'll need a "Plan B"

[Donald.]

there is a 1024 char limit on the concatenated data length. I guess you'll need a "Plan B"

I'll try to come up with an idea, but is there a way to limit the concatenated data ONLY to have the last 5 or 10 events displayed? The table has a date field so I could theoretically arrange it by date descending but I don't know how to only concatenate the last X terms.

[Barand]

Plan B

$user = 1;
$sql = "SELECT logs.event, logs.points, x.total
FROM logs
INNER JOIN
(
SELECT user_id, SUM(points) as total
FROM logs
GROUP BY user_id
) as x USING (user_id)
WHERE logs.user_id = $user";

$res = mysql_query($sql);
list($e, $p, $t) = mysql_fetch_row($res);

echo "User $user has $t points<br /><br />";

do {
    echo "$e -- $p points<br />";
} while (list($e, $p, $t) = mysql_fetch_row($res));

[Donald.]

Thanks, with some fine running of the query I managed to add some more info as well. You're amazing!