How would I do this insert query?

I've got a calendar event that spans three days. Here's the way looks it my database:

id: 9
short_name: three day storm
date: 2015-07-19
end_date: 2015-07-21

What I want to do is take that event and insert it into another table so it looks like this:

row #1
short_name: three day storm
date: 2015-07-19
end_date: 2015-07-19

row #2
short_name: three day storm
date: 2015-07-20
end_date: 2015-07-19

row #3
short_name: three day storm
date: 2015-07-21
end_date: 2015-07-19

How would I do that?
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
$data=json_decode('{"id": 9, "short_name": "three day storm", "date": "2015-07-19", "end_date":"2015-07-21"}', TRUE);

// inspect the data
//print_r($data);

// sample statement
$sql="INSERT INTO `shortname`, `date`, `end_date` VALUES( '%s', '%s', '%s')";

// remove the hyphens from the dates, so that you can use them as lower and upper bounds
// since they increment sequentially - ex:
// 20150719 => 20150720 => 20150721
$i=str_replace('-','',$data['date']);
$limit=str_replace('-','',$data['end_date']); 

while( $i <= $limit )
{
    // format the numeric sequences (20150719) to proper date format(2015-07-19)
    $date = date('Y-m-d', strtotime($i++));

    // replace the %s with the corresponding values
    // note: Don't execute it as given here.  This is just for illustrational purposes.
    // you should be using bound parameters in your production queries.  The
    // values for your bound parameters would be in $data['short_name'] and $date
    $sql = sprintf( $sql, $data['short_name'], $date, $date );

    //inspect the sql statement
    echo $sql . PHP_EOL;
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
In the standard design pattern for a calendar, there is only one row per event.  An event is the object that owns the date/time start and end values.  If you invert this design and make the date/time values own the events, then you are stuck with a design that has, potentially, 86,400 data points per day (one for every second).  If you simplify, perhaps by omitting seconds and saying that all events will start on the minute, you're dealing with 1,440 possible start and end values.  If you further simplify, you start to lose schedule flexibility.  Nobody wants to deal with the complexity and performance issues that arise from a design like that.  So we have events at the top of the object food chain, and derive our display data from each event.  Events can span minutes, hours, days or weeks, or longer.

As a practical matter, the three rows above would never be inserted into a table - they would be generated by a query and a bit of programming.  (Also, I think the data in your test examples may be incorrect, but that's a separate issue).

It's infeasible to teach the computer science theory behind object-oriented design in calendar applications in an online forum like E-E, but we have some articles here with code examples that deal with the issues.
http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html

Please see Practical Application #6, #6a, #9, and #12.  If I get a chance, I'll try to show you the programming we might use for something like this.
0
Ray PaseurCommented:
See if this makes sense:
http://iconoun.com/demo/temp_brucegust.php
<?php // demo/temp_brucegust.php
/**
 * http://www.experts-exchange.com/questions/28698312/How-would-I-do-this-insert-query.html
 *
 * id: 9
 * short_name: three day storm
 * date: 2015-07-19
 * end_date: 2015-07-21
 */
error_reporting(E_ALL);
echo '<pre>';

// A SINGLE EVENT REPRESENTATION
Class Event
{
    public    $id = 9;
    public $name  = 'three day storm';
    public $alpha = '2015-07-19';
    public $omega = '2015-07-21';

    public function __toString()
    {
        return
         "short_name: $this->name "
       . PHP_EOL
       . "date: $this->alpha"
       . PHP_EOL
       . "end_date: $this->omega"
       . PHP_EOL
       ;
    }
}

// GENERATE ONE "ROW" FOR EACH DAY OF THE EVENT
$storm = new Event;
$row   = 0;
while ($storm->alpha <= $storm->omega)
{
    $row++;
    echo '<b>' . "row #$row" . '</b>' . PHP_EOL;
    echo $storm;
    $storm->alpha = date('Y-m-d', strtotime($storm->alpha . ' + 1 DAY'));
}

Open in new window

0
brucegustPHP DeveloperAuthor Commented:
More than enough to get started. Thank you, gentlemen!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.