Insert data into Multiple tables

[schoch]

Hi I want to insert data into a couple of tables.
I can insert into one table but am having trouble inserting into multiple. The code I have is as follows. Do I use an Inner Join?

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO mail_drops (weekending_id, md_1, md_2, md_3, md_4, md_5, md_6, md_7, md_total) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['weekending_date'], "int"),
                       GetSQLValueString($_POST['md1'], "text"),
                       GetSQLValueString($_POST['md2'], "text"),
                       GetSQLValueString($_POST['md3'], "text"),
                       GetSQLValueString($_POST['md4'], "text"),
                       GetSQLValueString($_POST['md5'], "text"),
                       GetSQLValueString($_POST['md6'], "text"),
                       GetSQLValueString($_POST['md7'], "text"),
                       GetSQLValueString($_POST['mdtotal'], "text"));

  mysql_select_db($database_voip, $voip);
  $Result1 = mysql_query($insertSQL, $voip) or die(mysql_error());

  $insertGoTo = "confirm.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

$colname_franchisee = "1";
if (isset($_GET['franchisee_id'])) {
  $colname_franchisee = (get_magic_quotes_gpc()) ? $_GET['franchisee_id'] : addslashes($_GET['franchisee_id']);
}
mysql_select_db($database_voip, $voip);
$query_franchisee = sprintf("SELECT * FROM franchisee WHERE franchisee_id = %s", $colname_franchisee);
$franchisee = mysql_query($query_franchisee, $voip) or die(mysql_error());
$row_franchisee = mysql_fetch_assoc($franchisee);
$totalRows_franchisee = mysql_num_rows($franchisee);
?>

 

[jpadie]

i have always had to maintain referential integrity like this by doing two database writes inside a commit/rollback test on a myisam db as the INSERT syntax in mysql does not support writing to multiple tables.

you cannot get around this limitation by using views either. see the quote below from the mysql manual:

28.7.6: Can you insert into views that are based on joins?

It is possible, provided that your INSERT statement has a column list that makes it clear there's only one table involved.

You cannot insert into multiple tables with a single insert on a view.