Joining recent database rows in PHP loop

Question

Let's assume I have a MySQL structure like this...

+--------------+--------------------+-------------------+
| tdate        | tuser              | tvalue            |
+--------------+--------------------+-------------------+
| 11:16:48     | John               | 10                |
+--------------+--------------------+-------------------+
| 11:16:38     | John               | 40                |
+--------------+--------------------+-------------------+
| 11:16:28     | Lisa               | 50                |
+--------------+--------------------+-------------------+
| 10:16:48     | Lisa               | 20                |
+--------------+--------------------+-------------------+

and a php query like this:

<?php
$username =  'test';
$sql=mysqli_query($db,"SELECT * FROM t ORDER BY tid DESC LIMIT 20");
while($row=mysqli_fetch_array($sql))
{
    $tuser= $row['tuser'];
    $tvalue= $row['tvalue'];
    $tdate= $row['tdate'];
    ?>
    <div id="<?php echo $tvalue; ?>" class="message_box">
        <?php echo $tdate; ?> -
        <?php echo $tvalue; ?> -
        <?php echo $tuser; ?>
    </div>
    <?php
} 
?>

This currently lists the mysql table by each row like this:

2016-02-22 11:16:48 - 10 - John
2016-02-22 11:16:38 - 40 - John
2016-02-22 11:16:28 - 50 - Lisa
2016-02-22 10:16:48 - 20 - Lisa

I would like to see it like this:

2016-02-22 11:16:48 - 50 - John
2016-02-22 11:16:28 - 50 - Lisa
2016-02-22 10:16:48 - 20 - Lisa

Meaning that loop would have to sum up values in cases where the new row was created within 60 seconds (for the same user).. Lisa has two rows, but since there is one hour timestamp difference it would have to be listed as usual... It would need it to update automaticaly, meaning that it would show value of 40 for John initially, but after 10 seconds when the new row would be added it would have to update to 50 accordingly. There is also the issue of MYSQL QUERY LIMIT of 20 which could break up rows that were inserted within 60 seconds and give false total value calculation.. I know it's a hard one, but does anyone has a simple solution for this?

Thanks to Asur I currently have this:

SELECT ROUND(UNIX_TIMESTAMP(tdate) DIV 60) AS time,tuser,SUM(tvalue) 
FROM t 
GROUP BY EXTRACT(DAY_HOUR FROM tdate),tuser 
ORDER BY tid DESC 
LIMIT 20;

but the problem is that if the last database row is JOHN, he will not be listed out last if there is already one John row above that in the same hour..

IF I HAVE: 
JOHN 10 
GEORGE 10 
GEORGE 10 
JOHN 10 

It will list: 
JOHN 20 
GEORGE 20 

And it should be the other way arround, where the last row counts: 
GEORGE 20 
JOHN 20

Asur · Accepted Answer · 2016-02-22 12:23:12Z

1

I would recommend you to do it directly in the sql query as follows:

SELECT ROUND(UNIX_TIMESTAMP(tdate) DIV 60) AS time,tuser,SUM(tvalue)       
FROM t
GROUP BY time,tuser
ORDER BY tid DESC 
LIMIT 20;

This is long way better in terms of optimization and spent time.

edited Feb 22, 2016 at 12:23

answered Feb 22, 2016 at 11:39

Asur

3873 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

120382833 Over a year ago

Thanks for your effort :)... It successfully groups rows, but the time element does not work.. This solution sums up tvalue for the user in all rows.. I would like it to group it only for the timeframe of 60 seconds for example... That would mean that the same user would be listed out multiple times if there are other users in between time-wise..

Asur Over a year ago

Ok,I haven't seen that part, as guidance I would tell you to check MYSQL intervals

120382833 Over a year ago

mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

Asur Over a year ago

Yes sorry forgot to change it :) Is it completly functional?

120382833 Over a year ago

It is a nice work arround.. But still not all rows within 60 seconds are grouped together.. It returns me two entries for the same user, even though all entries are less than 60 seconds apart (from the first timestamp for that user)..

|

Gouda Elalfy · Accepted Answer · 2016-02-22 11:42:14Z

0

replace this query:

SELECT * FROM t ORDER BY tid DESC LIMIT 20

with this:

SELECT tdate , sum(tvalue), tuser FROM t 
group by tuser, DATE_FORMAT(tdate , '%Y-%m-%d %h:%i') 
ORDER BY tid DESC LIMIT 20

edited Feb 22, 2016 at 11:42

answered Feb 22, 2016 at 11:35

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

1 Comment

120382833 Over a year ago

I appreciate this.. It works for me, but would you know how do I specify 60 seconds or a different timeframe if I want to change it?

Collectives™ on Stack Overflow

Joining recent database rows in PHP loop

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related