Inserting elapsed time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Inserting elapsed time

Post by laknal »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,

I am not where I going wrong. 

I have a table, in which I am trying to capture elapsed time between start time and end time. Please see below for information:

[syntax="sql"]CREATE TABLE `article_art` (
  `id_art` int(11) NOT NULL auto_increment,
  `title_art` varchar(255) NOT NULL default '',
  `content_art` text NOT NULL,
  `createdon_art` datetime NOT NULL default '0000-00-00 00:00:00',
  `modifiedon_art` datetime NOT NULL default '0000-00-00 00:00:00',
  `time_submit` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `time_diff` varchar(15) default NULL,
  PRIMARY KEY  (`id_art`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;


INSERT INTO `article_art` VALUES (24, 'test', 'test', '2006-10-18 17:21:48', '0000-00-00 00:00:00', '2006-10-18 17:21:53', NULL);


// trigger:
create trigger
location.test
AFTER
INSERT
on
location.article_art
for each row UPDATE article_art set time_differ=time_submit-createdon_art
[/syntax]

Code: Select all

Error:  
SQL error: Can't update table 'article_art' in stored function/trigger because it is already used by statement which invoked this stored function/trigger..
SQL error: INSERT INTO article_art (title_art, content_art, createdon_art) values ('test', 'test', '2006-10-18 17:21:48'). (SQL_ERROR)

Any help is appreciated.

Thanks.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm not seeing what this has to do with PHP. Can you explain?

I think you want the trigger to fire before the insert. The ones I've built involve NEW.fieldName.
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Elapsed time in php

Post by laknal »

Thanks for reply.

Since I have to capture end_time of the order, I created timstamp for end_time.

I could not figure out how to do in php before actual insert into database for end time.

I thought, to get elapsed time between start time of order and end time order, trigger is only option.

Should I create separate table for elapsed time?


For example:

When user opens order form, I am capturing start time in hidden field (NOW())

Then in database , I have end time column with timestamp. It captures end time on insert into the database.

But how do I calculate/capture, elapsed time or total time it took user to fill out a form?

I have diplay user , start time, end time and total time on screen.

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I, personally, would store (in a session variable) the first time the opened the form and not update (remove) that until after the submission is processed. I would use time(). After the processing is complete and I'm read to insert the data, I'd run time() again. The difference in the two times is the number of seconds it took for them to fill the form. You can store that in multiple ways, although keeping it in seconds seems most logical to myself.
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Elaspsed time

Post by laknal »

Can you provide examples for capturing elasped time?

I am not sure how to capture data in session.


Thanks,
Lakshmi
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I told you how to do it: time() at the beginning, time() at the end, find the difference (subtract).
User avatar
Cameri
Forum Commoner
Posts: 87
Joined: Tue Apr 12, 2005 4:12 pm
Location: Santo Domingo, Dominican Republic

Post by Cameri »

Code: Select all

session_start(); //assuming you started it somewhere at the beginning of your code...

//now store the first timestamp on $_SESSION global
$_SESSION['first_timestamp'] = time();
Then on the other page, when the order is completed:

Code: Select all

session_start(); //assuming you started it somewhere at the beginning of your code...

$elapsed_time = time() - $_SESSION['first_timestamp'];
echo "It took you " . $elapsed_time . " seconds to complete your order...";
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

<?php
$time_start = time();

// Do some processing

$time_stop = time();

$time_difference = $time_stop - $time_start;
?>
laknal
Forum Newbie
Posts: 21
Joined: Wed Oct 11, 2006 7:38 pm

Elapsed time in php

Post by laknal »

I think, the I will get partial desired results. I don't have confirmation page. I was planning on capturing on onSubmit time in the database(timestamp).

And also, assuming that user has confirmation page, and captuing the second page time/NOW() may not be accurate. User has to click on OK/submit button to complete transaction.

In other words, transaction is considered complete when user confirms it by clicking on OK/SUbmit button.

Is it possible to get end timestamp from database and substract the start time (NOW() from PHP) and insert that value in the third column elapsed time in the database?

Or do elapsed time compute on display on details page:

Example:

1. col1: start time
2. col2: end time
3. col3: elapsed time

The elapsed time is not stored in the database but computed on display.

Thanks.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

As it relates to times, you can do anything with the times from both PHP and the database as long as they are talking and as long as you know what logic you want to follow. Bear in mind that times may not be accurate to the millisecond (or to a very precise point) because of processing times, db connection times, etc. even moreso if there is approrpiate error trapping.
Post Reply