how difference between time

[pucca]

i have a tabel as follow as:

Code: Select all

username   | time         
-----------|---------------
aq         |   05:55:07         
aq         |   05:55:09   
megi       |   05:55:37   
megi       |   05:55:40         
megi       |   05:55:45 

i have a script can difference between time as follow as:

Code: Select all

SET @waktu= null;
    SELECT username, tanggal, module, action, pagename,
      IFNULL(timediff(`waktu`,@waktu), 0) as selisih_waktu, @waktu := `waktu` as time
    FROM mdl_temporer_log2 WHERE rolename = 'Student'
    ORDER BY waktu;

But that script will show a tabel:

Code: Select all

 username   | selisih_waktu |   time         
    -----------|---------------|--------------
    aq         |     0         |     05:55:07         
    aq         |  00:00:02     |     05:55:09   
    megi       |   00:00:18    |     05:55:37   
    megi       |  00:00:03     |     05:55:40         
    megi       |  00:00:03     |     05:55:45

But i want show that output as follow as:

Code: Select all

    username   | selisih_waktu |   time         
    -----------|---------------|--------------
    aq         |     0         |     05:55:07         
    aq         |  00:00:02     |     05:55:09   
    megi       |   0           |     05:55:37   
    megi       |  00:00:03     |     05:55:40         
    megi       |  00:00:03     |     05:55:45   

how that script will show that output table?

[johnj]

Sorry, not able to make out what you are trying to achieve.

[pucca]

so, how to overcome these problems? or is there another solution to resolve it with the sql scripts or php? because I'm really confused

[johnj]

What is the problem that you are facing?

[pucca]

I can not reduce the time and based on each row display the username as follows:

Code: Select all

username   | difference_time |   time         
    -----------|---------------|--------------
    aq         |     0         |     05:55:07         
    aq         |  00:00:02     |     05:55:09   
    megi       |   null           |     05:55:37   
    megi       |  00:00:03     |     05:55:40         
    megi       |  00:00:03     |     05:55:45

I am confused to use php scripts or mysql script that generate output that table, please help me because The problem is the task of my thesis and I have to present it!

[johnj]

All you have to do is whenever there is a new username or change in username reset the time calculation.

[pucca]

I've tried to make the sql script to create a procedure, in mysql with input parameters and invoked only when using

Code: Select all

call selisih ('Megi')

, and I get confused when they want to call another username in php, so when displaying the username table together, what's the solution?

[johnj]

whenever there is a change in user name the time difference will be zero. keep that in mind and fix your script.

[pucca]

how to fix the script? please help!