Page 1 of 1

Inserting elapsed time

Posted: Wed Oct 18, 2006 9:04 pm
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]

Posted: Wed Oct 18, 2006 9:09 pm
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.

Elapsed time in php

Posted: Wed Oct 18, 2006 9:54 pm
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

Posted: Wed Oct 18, 2006 10:00 pm
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.

Elaspsed time

Posted: Thu Oct 19, 2006 7:04 am
by laknal
Can you provide examples for capturing elasped time?

I am not sure how to capture data in session.


Thanks,
Lakshmi

Posted: Thu Oct 19, 2006 7:12 am
by feyd
I told you how to do it: time() at the beginning, time() at the end, find the difference (subtract).

Posted: Thu Oct 19, 2006 7:16 am
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...";

Posted: Thu Oct 19, 2006 10:46 am
by RobertGonzalez

Code: Select all

<?php
$time_start = time();

// Do some processing

$time_stop = time();

$time_difference = $time_stop - $time_start;
?>

Elapsed time in php

Posted: Fri Oct 20, 2006 6:46 am
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.

Posted: Fri Oct 20, 2006 11:00 am
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.