We help IT Professionals succeed at work.

How do I insert into multiple tables and add new information?

Kevin Smith
Kevin Smith asked
on
I have an insert record form to insert a record into tbl_series.  I also have a tbl_races.  When I insert a series, I want the action to insert 8 records into the tbl_races.  Furthermore, I want it to add future dates (of each Monday).  For example: I create a Winter series and put in a start date of the series.  I want the code to create 8 races numbered 1 through 8, with race week 1 using the start date of the series, race week 2 seven days later, race 3 seven days after that, etc.

This is the Dreamweaver created code for basic entry...where do I go next?

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tbl_series (seriesID, seriesName, BeginDate, `Year`) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['seriesID'], "int"),
                       GetSQLValueString($_POST['seriesName'], "text"),
                       GetSQLValueString($_POST['BeginDate'], "date"),
                       GetSQLValueString($_POST['Year'], "int"));
Comment
Watch Question

Commented:
I'd never use DW to code anything... notepad rulz! :)

With one insert you cannot do this. You need further inserts on some way.

Anyhow, what could be done:
- if your DB server supports stored procedures / triggers you can put this logic there (e.g. create an SP creating the record in series, and also insert the records to races, or trigger an event to the insert of series, and add the 8 additional records to races from there)
- you can prepare & execute another SQL to create the records for you in races, as you can see in the code:
for($week=0; $week<8; $week++) {
  $race_date = strtotime($_POST['BeginDate']) + (7*24*60*60)*$week;
  $sql = sprintf(
        "INSERT INTO tbl_races (series_id, week_id, race_date) " .
        "VALUES (%s, %s, %s)",
               GetSQLValueString($_POST['seriesID'], "int"),
               GetSQLValueString($week+1, "int"),
               GetSQLValueString($race_date, "date"),
         );
}

Open in new window

Author

Commented:
It's only creating one week...

Author

Commented:
wait, it actually isn't creating anything

Author

Commented:
any new ideas on this?  it didn't create anything...
Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
There is nothing wrong with using dreamweaver to code. But if you want to do more complex things you just need to add your own secret sauce.  I'm not fluent in php but you will want to know a function to add dates http://www.brightcherry.co.uk/scribbles/2009/01/06/php-adding-and-subtracting-dates/

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1"))

RACE_NAME=0
varDATE=BeginDate
For LOOP =  0 to 7
DATE=
varDATE= Add LOOP to BeginDate (notice first round is 0 so you will  use your start date)
varYEAR = year(varDATE)
RACE_NAME=RACE_NAME+1
varSERIES_NAME = "Race "& RACE_NAME  (You end up with Race1, Race2 etc)  

{
  $insertSQL = sprintf("INSERT INTO tbl_series (seriesID, seriesName, BeginDate, `Year`) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['seriesID'], "int"),
                       GetSQLValueString(varSERIES_NAME, "text"),
                       GetSQLValueString(varDATE, "date"),
                       GetSQLValueString(varYEAR, "int"));

Next