Insert in table using two date fields

Insert in table using two date fields

Postby jonathanlemaire » Mon Jan 04, 2010 11:46 pm

First of all great script! well done!
I want to insert a date selection at once using two date fields, for example: datefield1: 04-01-2010 datefield2: 08-01-2010 now i need to insert al the dates from 04-01-2010 till 08-01-2010 into the table (04-01-2010, 05-01-2010, 06-01-2010, 07-01-2010, 08-01-2010)
I don't have a clue how to build something like this and was wondering if somebody could help me with setting up something like this.

So far i can put just single date's into the table, the problem is how to put a selection into the table.
jonathanlemaire
 
Posts: 3
Joined: Mon Jan 04, 2010 11:27 pm

Re: Insert in table using two date fields

Postby jonathanlemaire » Wed Jan 12, 2011 4:19 pm

after a while i am trying to solve this problem again, and i found some code wich might be usefull on how to insert multiple records using a date range.
The problem is i cant get the values in the database, and i dont get any errors.
is there anybody who can help me and get this working? if this works we can update the status of more then one day at a time.
thanks in advance
Code: Select all
<?php
$db = array (
    'host' => 'localhost',
    'user' => 'test',
    'pass' => 'test',
    'dbname' => 'calendar'
);

if(!mysql_connect($db['host'], $db['user'], $db['pass']))
{
    trigger_error('Connexion error: '.mysql_error());
}
elseif(!mysql_select_db($db['dbname']))
{
    trigger_error('Error on selecting database '.mysql_error());
}
else
{
    $sql = "SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'";
    if(!mysql_query($sql))
    {
        trigger_error('MySQL in ANSI niet mogelijk');
    }
}

// sample data from user input:
$start_date = ('2008-06-01');
$end_date = ('2008-06-05');
$rate = 99;
$company_id = 11;
$car_id = 22;

// create values for each date:
$startTime = strtotime($start_date);
$endTime = strtotime($end_date);
$values = array();
for($time = $startTime; $time <= $endTime; $time = strtotime('+1 day', $time))
{
   $thisDate = date('Y-m-d', $time);
   $values[] = "($company_id, $car_id, $rate, '$thisDate')";
}

// build the actual query:
$query = sprintf(
   "INSERT INTO rates (companyID, carID, rate, date) VALUES\n%s",
   implode(",\n", $values)
);

// show what query would look like:
echo "<pre>$query</pre>";

?>

jonathanlemaire
 
Posts: 3
Joined: Mon Jan 04, 2010 11:27 pm

Re: Insert in table using two date fields

Postby jonathanlemaire » Wed Jan 12, 2011 5:14 pm

got it working:
dont know what i did wrong but will post the final code here maybe somebody can use it:
Code: Select all
<?php
$db = array (
    'host' => 'localhost',
    'user' => 'test',
    'pass' => 'test',
    'dbname' => 'calendar'
);

if(!mysql_connect($db['host'], $db['user'], $db['pass']))
{
    trigger_error('Connexion error: '.mysql_error());
}
elseif(!mysql_select_db($db['dbname']))
{
    trigger_error('Error on selecting database '.mysql_error());
}
else
{
    $sql = "SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'";
    if(!mysql_query($sql))
    {
        trigger_error('MySQL in ANSI niet mogelijk');
    }
}

// sample data from user input:
$start_date = ('2008-06-01');
$end_date = ('2008-06-30');


// create values for each date:
$startTime = strtotime($start_date);
$endTime = strtotime($end_date);
$values = array();
for($time = $startTime; $time <= $endTime; $time = strtotime('+1 day', $time))
{
   $thisDate = date('Y-m-d', $time);
   $values[] = "('$thisDate')";
}

// build the actual query:
$sql = sprintf(
   "INSERT INTO calbookings (the_date) VALUES\n%s",
   implode(",\n", $values)
);

// show what query would look like:
echo "<pre>$sql</pre>";


if(!$res = mysql_query($sql))
{
    trigger_error(mysql_error().'<br />In query: '.$sql);
}
else
{
    $id = mysql_insert_id();
}

?>

jonathanlemaire
 
Posts: 3
Joined: Mon Jan 04, 2010 11:27 pm

Re: Insert in table using two date fields

Postby JakeyL » Wed Jan 19, 2011 12:46 pm

Script works great when I use it. Thank you!
JakeyL
 
Posts: 5
Joined: Wed Jan 19, 2011 12:43 pm


Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron