MySQL Forums
Forum List  »  PHP

Re: Calculation with row value
Posted by: Rick James
Date: December 10, 2014 05:17PM

Is this the goal?
Set the "end time" of each item to be the "start time" of the 'next' item?

Such a task is best done in the app language, PHP.

SELECT id, event, begin, place ORDER BY start_time
Put that into a PHP associative array.
for ($j = 1; $j < count($rows); $j++)
{
$end = $row[$j]['begin'];
$row[$j-1]['end'] = $end;
$row[$j-1]['duration'] = ... // time subtraction
}

Furthermore, since the end time is calculable in this way, there is no need to have the endtime in the table.

Yes it is possible to do all that in SQL, but it is quite ugly. (Hint: It will involve @variables.)

Think of the database as storing the raw data; think of your application (written in PHP) as the vehicle with which to enhance, embelish, and format the data.

A "good" database does not have redundant data -- such as the endtime of one event always equaling the starttime of the next, and such as the duration, which is easily computed.

Options: ReplyQuote


Subject
Written By
Posted
December 09, 2014 01:44PM
December 09, 2014 05:57PM
December 10, 2014 12:55PM
Re: Calculation with row value
December 10, 2014 05:17PM
December 11, 2014 11:13AM
December 15, 2014 04:56PM
January 14, 2015 01:15PM
December 10, 2014 09:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.