Mulitiple Table insert

[chillininvt]

Ok I have a table that when you add a new department it will add the name to the table and assign the row and id based on an auto_increment column. From there you need to get the value of the last insert id. SO what I did was use the msql_insert_is() function. and assigned it to a variable. I then used that variable in my next query because this vaule is a forigen key in another table. Here is my code it should be exectued if you where to post this page with the value of $department_id = "" then the section of script in question will execture. Essentially need it to grab this id number and then place it into 2 more insert statements that should execute immediately after the first insert statement. Then only insert id that I would need is the one produced in the first insert statement what an auto increment value is assigned to the same table as the department_name

Code:

<?php
// validates on the pageID of the Calendar page
require( "../utils/auth.php" );
$auth = mysql_fetch_array(mysql_query("SELECT pageID FROM PageLocations WHERE location = 'contact.php'"));
$pID = $auth['pageID'];
require( "../access/access_via_passed_pageID.php" );

// to flag that a user has visited the page toolset (but not necessarily changed anything)
include("../inc/after_user_verify.php");

// CITY DIRECTORY PAGES UPDATE DIRECTLY TO THE LIVE DATABASE and skip the three-tier system
require( "../inc/conn_live.php" );

require_once( "../utils/string_utils.php" );

if( isset($_POST['btnSubmit']) ) {
   $department_id = $_GET['department_id'];
   
   if( $department_id == "" ) {
      // ADD NEW
    mysql_query("INSERT INTO CityHallDepartment (department_name)
                   VALUES ('" . $_POST['department_name'] . "');");

echo "This is the department id".$departmentid."";
$departmentname = $_POST['department_name'];
if($departmentname != NULL){

$departmentid = mysql_insert_id();
      mysql_query("INSERT INTO PageGeneric (pid, bannerid, title, content, metadesc, layoutID) VALUES ('0', '$departmentname', 'Comming Soon', '$departmentname', '3');");
   echo"The script executed into while loop";
	$page = "generalpage.php?page=".$departmentid."";
  mysql_query("INSERT INTO PageLocations (parentID, location, title, display, displayNavCMS, departmentID) VALUES ('0', '$page', '$departmentname', '1', '1', '0', $departmentid);");
 }else{
 echo"The script when to the else sections";
 }
 } else {
      // UPDATE EXISTING
      mysql_query("UPDATE CityHallDepartment 
                   SET department_name = '" . $_POST['department_name'] . "' 
                   WHERE id = '" . $department_id . "'");
      
      if( isset($_POST['main_contact']) ) {
         // clear the current main contact flag ...
         mysql_query("UPDATE CityHallDirectory 
                      SET department_head = 0 
                      WHERE department_id = '" . $department_id . "'");
                      
         // ... then set the new main contact flag
         mysql_query("UPDATE CityHallDirectory 
                      SET department_head = 1 
                      WHERE department_id = '" . $department_id . "'
                         AND id = '" . $_POST['main_contact'] . "'");
      }
   }
   header("Location: contact.php");
   exit;
} else {
   if( isset($_POST['department_id']) ) {
      // EDIT
      $department_id = $_POST['department_id'];
   } else {
      // ADD
      $department_id = "";
   }

   $r_dept = mysql_query("SELECT * 
                          FROM CityHallDepartment 
                          WHERE id = '" . $department_id . "'");

   $m_dept = mysql_fetch_array( $r_dept );
   $department_name = $m_dept['department_name'];
}

if ( isset($_POST['btnDelete']) ) {
   $department_id = $_GET['department_id'];
   mysql_query("DELETE FROM CityHallDepartment 
                WHERE id = '" . $department_id . "'");
   header("Location: contact.php");
   exit;
}

if ( isset($_POST['btnCancel']) ) {
   header("Location: contact.php");
   exit;
}
?>

[Wildbug]

So what's your question?

 

(You can also use MySQL's function, LAST_INSERT_ID(), in your following query.)

[chillininvt]

Thats what I did if you look at the code. The thing is why is it only exectuting one sql statement and not the other 2

[Wildbug]

a) I don't see you checking for any errors.  Why not?

 

$query = "blah blah blah";
mysql_query($query);
if (mysql_errno()) die(sprintf('Error: %s<br>Query: %s<br>',mysql_error(),$query));

 

b) Why the extra empty string at the end?

 

echo "This is the department id".$departmentid."";

 

c) You use $departmentid and $department_id.  Do you mean for those to be the same?

 

d) You're not using MySQL's LAST_INSERT_ID() functions; you're using PHP's.  You'll get the same answer either way; I was just suggesting a way to do it within the query without extra PHP code.

 

e) I see you putting numbers in single quotes in your queries.  Are the column types numeric or [VAR]CHAR?  If they're numeric; don't use quotes.

 

f) "Comming Soon" should be "Coming Soon."

 

g) You're mixing $_GET and $_POST.  Why?

$department_id = $_GET['department_id'];
   
   if( $department_id == "" ) {
      // ADD NEW
    mysql_query("INSERT INTO CityHallDepartment (department_name) VALUES ('" . $_POST['department_name'] . "');");

 

h) Don't use semi-colons in PHP MySQL queries.

 

 

Add some error checking and post again if errors are produced.  Include the error and the query that caused it.