Inserting timestamp array in MySQL DateTime

Question

I'm trying to insert an array of dates in database but the program does not behave. I'm doing something wrong.

This insert NULL:

// initialize the array with first date
$dates = array(strtotime("+17 days", strtotime($period_startdate)));
// now loop 26 times to get the next 26 dates
for ($i = 1; $i <= 26; $i++) {
    // add 14 days to previous date in the array
    $dates[] = strtotime("+14 days", $dates[$i-1]);
}
// echo the results
foreach ($dates as $date) {
    // prepare and bind for paydates
    $insertpaydates= $db->prepare("INSERT INTO paydates (payroll_secret_id,paydate) VALUES (?,?)");
    $insertpaydates->bind_param("ss",$secret_id,$dates[$date]);
}

This insert only one row and wrong date (1970-01-01)

// initialize the array with first date
$dates = array(strtotime("+17 days", strtotime($period_startdate)));
// now loop 26 times to get the next 26 dates
for ($i = 1; $i <= 26; $i++) {
    // add 14 days to previous date in the array
    $dates[] = strtotime("+14 days", $dates[$i-1]);
}
// echo the results
foreach ($dates as $date) {
    $Item_Date[$date] = date("Y-m-d", strtotime($Item_Date[$date]));
    // prepare and bind for paydates
    $insertpaydates= $db->prepare("INSERT INTO paydates (payroll_secret_id,paydate) VALUES (?,?)");
    $insertpaydates->bind_param("ss",$secret_id,$Item_Date[$date] );
}

@JohnConde: $period_startdate is the value extracted from jquery ui calendar datepicker. It is post above the for loop $period_startdate = $_POST['period_startdate']; — Sebastian Farham
– Sebastian Farham, Commented Apr 3, 2017 at 1:54
@JohnConde: a random jquery date picked by the user format is 2016-04-02 it's inserted in a Mysql date column. — Sebastian Farham
– Sebastian Farham, Commented Apr 3, 2017 at 2:05

Marcus Lind · Accepted Answer · 2017-04-03 04:06:44Z

1

Wrong use of Foreach

The problem on the first code snippet is inside the foreach loop. When you loop through an array, the second parameter will be the actual entry of the array, not just the key. So instead of using $dates[$date] you should simply use $date inside your loop.

Execute Query Inside Loop

You also have to execute the query for each time you bind new parameters to the statement, or else you will just override the old parameters and do a single execution after the loop. So you should add the execute to the foreach-loop so that it writes on each iteration.

Prepare Statements Outside Loops

A third thing you should do to improve the performance of your code is to move the SQL Prepare statement to outside the loop. Instead of preparing the statement 26 times you can only do it once, and then execute it with different values 26 times. This will make a big difference in the execution time.

Timestamps != DateTime

In the comments below to this post it seems like you are using a bad combination of data and data types in the MySQL database. You're trying to write an integer timestamp to a DateTime field. You should convert it into a valid date format before writing it.

So final code would be:

// Convert our $_POST value to a timestamp.
$period_startdate = strtotime($period_startdate);

// We init the first date value.
$dates = array(
    date('Y-m-d H:i:s', strtotime("+17 days", $period_startdate)), 
);

for ($i = 1; $i <= 26; $i++) {
    // Add 14 days to previous date in the array.
    // Notice we have to convert the previous date in the array to a timestamp using strtotime().
    $dates[] = date('Y-m-d H:i:s', strtotime("+14 days", strtotime($dates[$i-1])));
}

// We prepare our statement before the loop to avoid preparing it 
// multiple times. This saves performance.
$insertpaydates = $db->prepare("INSERT INTO paydates (payroll_secret_id,paydate) VALUES (?,?)");

foreach ($dates as $date) {
    // Here we change from $dates[$date] to simply $date
    $insertpaydates->bind_param("ss", $secret_id, $date);
    // Here we execute the query for each date.
    $insertpaydates->execute();
}

edited Apr 3, 2017 at 4:06

answered Apr 3, 2017 at 1:58

Marcus Lind

11.7k9 gold badges66 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Marcus Lind Over a year ago

I added execute() to the loop.

Marcus Lind Over a year ago

Did you add the execute() statement that I added to the edit just now?

Sebastian Farham Over a year ago

Yes I did. It excutes and insert the row but the date returns zeros

Marcus Lind Over a year ago

@SebastianFarham Do a var_dump($dates); before the foreach and make sure it contains 26 dates.

Sebastian Farham Over a year ago

array(27) { [0]=> int(1492639200) [1]=> int(1493848800) [2]=> int(1495058400) [3]=> int(1496268000) [4]=> int(1497477600) [5]=> int(1498687200) [6]=> int(1499896800) [7]=> int(1501106400) [8]=> int(1502316000) [9]=>... }

|

Collectives™ on Stack Overflow

Inserting timestamp array in MySQL DateTime

1 Answer 1

Wrong use of Foreach

Execute Query Inside Loop

Prepare Statements Outside Loops

Timestamps != DateTime

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Wrong use of Foreach

Execute Query Inside Loop

Prepare Statements Outside Loops

Timestamps != DateTime

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related