CODEWITHSUNDEEP
mysql
John
John

Reputation: 1751

MySQL calculating only first row

I have table that have stored date time...i need using mysql to calculate duration in 0d 0h 0m 0s format to show it in datatables:

+----+---------------------+
| id | playtime            |
+----+---------------------+
|  2 | 08:58:24 20/06/2017 |
|  1 | 08:57:33 20/06/2017 |
+----+---------------------+

This is my query:

SELECT (SELECT GROUP_CONCAT(                          
     TIMESTAMPDIFF(day, live.playtime, NOW()) , 'd ',                                

     MOD(TIMESTAMPDIFF(hour, live.playtime, NOW()), 24), 'h ',                           

     MOD(TIMESTAMPDIFF(minute, live.playtime, NOW()), 60), 'm ',                          

     MOD(TIMESTAMPDIFF(second, live.playtime, NOW()), 60), 's'                       
) FROM live) AS duration;

The problem is that only first id is calculated and others ids are ignored....how can i return multiple result for each id calulated time?

I need to return this:

+-----------------------------+
| duration                    |
+-----------------------------+
| 0d 0h 13m 50s,0d 0h 12m 59s |
+-----------------------------+
| 0d 0h 11m 12s,0d 0h 6m 9s   |
+-----------------------------+

UPDATED FULL QUERY:

SELECT SQL_CALC_FOUND_ROWS live.id, live.user, live.player, 
SUBSTRING_INDEX(streams.channel, '_', -1) AS channel, bouquets.bouquet, 
DATE_FORMAT(live.playtime, '%H:%i:%s %d/%m/%Y') AS playtime,                
    (SELECT CONCAT(                          
            TIMESTAMPDIFF(day, live.playtime, NOW()) , 'd ',                                

            MOD(TIMESTAMPDIFF(hour, live.playtime, NOW()), 24), 'h ',                           

            MOD(TIMESTAMPDIFF(minute, live.playtime, NOW()), 60), 'm ',                          

            MOD(TIMESTAMPDIFF(second, live.playtime, NOW()), 60), 's'                       
    ) FROM live GROUP BY live.id LIMIT 1) AS duration, 
resellers.nick, live.ip, servers.server, live.id, live.id             

FROM live LEFT JOIN streams ON live.stream=streams.id LEFT JOIN bouquets ON 
live.bouquet=bouquets.id LEFT JOIN resellers ON live.reseller=resellers.id 
LEFT JOIN servers ON live.server=servers.id        
ORDER BY live.id desc LIMIT 0, 10;

Upvotes: 1

Views: 116

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

Assuming there is only one record per id, could you try adding a WHERE clause in nested SELECT query, e.g.:

SELECT SQL_CALC_FOUND_ROWS live.id, live.user, live.player, 
SUBSTRING_INDEX(streams.channel, '_', -1) AS channel, bouquets.bouquet, 
DATE_FORMAT(live.playtime, '%H:%i:%s %d/%m/%Y') AS playtime,                
    (SELECT CONCAT(                          
            TIMESTAMPDIFF(day, l.playtime, NOW()) , 'd ',                                

            MOD(TIMESTAMPDIFF(hour, l.playtime, NOW()), 24), 'h ',                           

            MOD(TIMESTAMPDIFF(minute, l.playtime, NOW()), 60), 'm ',                          

            MOD(TIMESTAMPDIFF(second, l.playtime, NOW()), 60), 's'                       
    ) FROM live l WHERE l.id = live.id) AS duration, 
resellers.nick, live.ip, servers.server, live.id, live.id             

FROM live LEFT JOIN streams ON live.stream=streams.id LEFT JOIN bouquets ON 
live.bouquet=bouquets.id LEFT JOIN resellers ON live.reseller=resellers.id 
LEFT JOIN servers ON live.server=servers.id        
ORDER BY live.id desc LIMIT 0, 10;

Upvotes: 1

Related Questions