Converting PHP date range to MYSQL individual dates

Question

I have an availability calendar in which I am currently adding in dates one by one, and using a mysql query to determine if there exists a row with a certain date and changing the class of the day to "booked" (Red).

I would like to enter in a range into my form, and process it through php (or mysql) into multiple, individual dates. My date format is M/D/YYYY, or MM/DD/YYYY, both are accepted. Unfortunately, when I built my calendar, I did not use the date format in sql for entries, but used varchar.

Is there a way to enter into my form for example 1/1/2014-1/3/2014 and have php convert that to 1/1/2014, 1/2/2014, 1/3/2014, and then have a mysql INSERT query to insert multiple values at once?

if (empty($_POST) === false && empty($errors) === true) {
$adcp_data = array(
'date'      => $_POST['date'],
'customer'  => $_POST['customer'],
'notes'     => $_POST['notes'],
            );
insert_adcp($adcp_data);
header('Location: adcp.php?success');
exit();

the insert_adcp function looks like this:

function insert_adcp ($adcp_data) {
    array_walk($adcp_data, 'array_sanitize');
    $fields = '`' . implode('`, `', array_keys($adcp_data)) . '`';
    $data = '\'' . implode('\', \'', $adcp_data) . '\'';

    mysql_query("INSERT INTO `adcp` ($fields) VALUES ($data)");

}

My workaround and last resort will be to add multiple text inputs and just add multiple dates manually so I only have to submit once. But a range is so much faster!

As a last note, if I could have those multiple entries keep the "customer" and "notes" values for each date in the range that would be amazing. I am prepared to lose those fields though to make this work. Thanks

Sure, PHP's a programming language. It'll do whatever you want it to. Doing a date range is trivial. As for your DB, stop working on code and fix the date fields NOW. Convert them to a REAL native date/datetime type. Leaving it as varchar will just cause you massive suffering in the long run. — Marc B
– Marc B, Commented Nov 28, 2013 at 16:03

Brad · Accepted Answer · 2013-11-28 16:52:36Z

4

Something like:

$day = new DateTime($_POST['range_start']);
$end = new DateTime($_POST['range_end']);

$all_dates = array();

while ($day <= $end){
  $all_dates[] = $day;
  $day->add(new DateInterval('P1D'));
}

That will give you an array of DateTime objects each of which represents a day in your range. You can get each object back into a string by calling DateTime::format() and passing 'm/d/Y' as the format string.

As for getting multiple entries into MySQL, the INSERT syntax allows INSERT INTO table (column) VALUES (row1), (row2), ... (rowN)

(this is clearly not not tested or the final code you would use -- just written into this web form from memory ... you'll have to write it out properly with input sanitation and range checking and whatnot.)

edited Nov 28, 2013 at 16:52

answered Nov 28, 2013 at 16:14

Brad

2292 silver badges10 bronze badges

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

3 Comments

V-Light Over a year ago

...looks good! The correct php-way would be: $day->add(new DateInterval('P1D')) You can test it here

idelest Over a year ago

This is great thanks Brad. Its very close, what it is producing is the final date a number of times equaling the number of days in the range. Troubleshooting, if I echo $day in the while loop, it seems to be working fine. Something is happening while I am appending the dates to the array

idelest Over a year ago

Ok I've solved it like you said it required some tinkering but you put me on the right track... thanks! I just kind of suck mysql queries I always have syntax errors. The problem was that I could not insert a datetime object into the table as a string. So I just created a new variable called $sqlday and used that for my injection and used $day as the variable to use in the while loop. Perfect! Maybe not the best way but its what I was aiming for.

Babblo · Accepted Answer · 2013-11-28 16:21:12Z

0

Check if the value from the input match your range format, capture the parts and generate the from and to dates.

if (preg_match('%\A(?<fromMonth>\d{1,2})/(?<fromDay>\d{1,2})/(?<fromYear>\d{4})-(?<toMonth>\d{1,2})/(?<toDay>\d{1,2})/(?<toYear>\d{4})\Z%', $str, $res)) {
    $dates['from'] = mktime(0, 0, 0, $res['fromMonth'], $res['fromDay'], $res['fromYear']);
    $dates['to']   = mktime(0, 0, 0, $res['toMonth'], $res['toDay'], $res['toYear']);
}

Generate the range between from and to dates.

for ($date = $dates['from']; $date <= $dates['to']; $date = strtotime('+1 day', $date) ){
    $dates['range'][] = date('m-d-Y', $date);
}

answered Nov 28, 2013 at 16:21

community wiki

Babblo

Comments

Victor Perov · Accepted Answer · 2013-11-28 17:14:40Z

0

I think, strtotime is more usable for your case. You can found definition at php.net site: http://php.net/manual/en/function.strtotime.php

answered Nov 28, 2013 at 17:14

Victor Perov

1,78419 silver badges38 bronze badges

Collectives™ on Stack Overflow

Converting PHP date range to MYSQL individual dates

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related