PHPirate PHPirate - 4 months ago 5x
PHP Question

Calculating with times in a database

I have a database with colums

[id, Boat, Stop, Time_D]
in which
is type of boat,
is number of the stop, and
is departure time of the boat.
So far all went very smooth with the database, I inserted a couple of sample times and boats for stop 1.

But I now want to use those times to insert the times for stop 2, given that the difference of
Time_D (stop2)
is 10 minutes.

I don't of course want to have to manually type the (lots of) departure times of stop 1 again, and I think I can do that smarter.

Those new times can go in the same table, below the times of stop 1, or in a seperate table (then I don't need the column
anymore of course), I don't really care how it works.

Until now I worked with MySQLi and PHP, I hope that will work for this too.


I figured it out myself, see comments in the code.

$time_difference=600; //time difference in departure times between the two stops
$stop_old = "1"; //stop from which to use the times to calculate the next times
$stop_new = "2"; //the new stop

//get times from stop in array
$result = mysqli_query($con,"SELECT `Boat`, `Time_D` from `Schedule` where `Stop` = '".$stop_old."' ");
//get database result
while ($row = mysqli_fetch_array($result) ) {                         
$rows[] = $row['Time_D']; //store all the times in one array
$boats[] = $row['Boat']; // store all the boats in one array
//print_r ($rows); //used for debugging
//Iterate over the array to change all the elements
for($i = 0; $i < sizeof($rows); $i++){
$rows[$i] = ( strtotime($rows[$i]) ) + ($time_difference); //with timestamps, because, well, php and times...
$rows[$i] = date('H:i:s', ($rows[$i]) ); // back from timestamp to time

for($i = 0; $i < sizeof($rows); $i++){
    mysqli_query($con, "INSERT INTO `Schedule`(`Boat`, `stop`, `Time_D`) VALUES ('$boats[$i]', '".$stop_new."', '$rows[$i]')");