djgjohng
Posts: 28
|
Posted: 10/27/2006, 1:14 AM |
|
Here's something I seriously struggled with - not being a programmer. I searched the web for solutions (found virtually nothing) and received some ideas from a PHP guy. Luckily I can afford to spend countless hours figuring this stuff out.
Let's say you have two dates in a form that you are inserting into one table. You then want to split that range up into individual dates to insert into another table:
(Set up an 'After Execute Insert' event on the form)
global $DBConnection1;
global $[YOURFORM];
$last_insertedID = CCDLookUp("max(recID)", "table1", "", $DBConnection1);
$first_date = CCDLookUp("datefrom","table1","recID=". $DBConnection1->ToSQL($last_insertedID, ccsInteger), $DBConnection1);
$last_date = CCDLookUp("dateto","table1","recID=". $DBConnection1->ToSQL($last_insertedID, ccsInteger), $DBConnection1);
$db = new clsDBConnection1();
$basedate = $first_date;
do {
$SQL = "INSERT INTO table2(recID, datefield) ".
"VALUES (". $db->ToSQL($last_insertedID,ccsInteger) .",". $db->ToSQL($basedate,ccsDate).")";
$db->query($SQL);
list($year, $month, $day) = split("-", $basedate);
$basedate= date("Y-m-d", mktime(0,0,0, $month, $day+1, $year));
} while ($basedate<= $last_date);
$db->close();
Has many applications.
Enjoy!
|
|
|
|