How can i insert the id of updated data into different table?

Question

i want to ask.. how can i insert the id of updated data into different table?..

this is the codes that i had made, i'm using $inserted_id= mysql_inserted_id() but it's not working.. the id still not insert into "updatetrail" table, hope u can help me =) :

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

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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']);
}

$count=0;

if ((isset($_POST["MM_update"]))) {
foreach ($_FILES['file']['name'] as $certificate) {

$allowedExts = array("gif", "jpeg", "jpg", "png", "txt", "pdf");
  if ($_FILES['file']['error'][$count]!=4 && $_FILES["file"]["error"][$count] > 0)
    {
        echo "Return Code: " . $_FILES["file"]["error"][$count] . "<br>";
    }
  else
    {
  if ($_FILES['file']['error'][$count]!=4) {
    if (file_exists("documents/" . $_FILES["file"]["name"][$count]))
          {
          print '<script type="text/javascript">'; 
          print 'alert("The certificate '. $_FILES["file"]["name"][$count].' is already exists, try rename the file and try again. =) ")';      print '</script>';

        echo "<script language=\"JavaScript\">{                                                                                         
        location.href=\"update(power).php\";                                                                                               
        }</script>";
          }
        else
          {
          move_uploaded_file($_FILES["file"]["tmp_name"][$count],
          "documents/" . $_FILES["file"]["name"][$count]);
          }

        $file=fopen("documents/". $_FILES["file"]["name"][$count],"r") or exit("Unable to open file!");

        $j=0;
        while(!feof($file))
        {

        $thetoken[$j]=fgets($file, 40);
        $j++;

        }
    }

  $updateSQL = sprintf("UPDATE department SET d_name=%s, d_staff_name=%s, d_position=%s, d_noic_nopassport=%s, d_type_training=%s, d_date_training=%s, d_date_expired=%s, d_sijil=%s WHERE d_no=%s",
                       GetSQLValueString($_POST['d_name'], "text"),
                       GetSQLValueString($_POST['d_staff_name'], "text"),
                       GetSQLValueString($_POST['d_position'], "text"),
                       GetSQLValueString($_POST['d_noic_nopassport'], "int"),
                       GetSQLValueString($_POST['d_type_training'], "text"),
                       GetSQLValueString($_POST['d_date_training'], "text"),
                       GetSQLValueString($_POST['d_date_expired'], "date"),
                       GetSQLValueString(addslashes($_FILES['file']['name'][$count]), "text"),
                       GetSQLValueString($_POST['departmentID'], "int"));

 mysql_select_db($database_dbconn, $dbconn);
$Result1 = mysql_query($updateSQL, $dbconn) or die(mysql_error());      

$inserted_id=$_POST['departmentID'];                       
$inserted_id = mysql_insert_id();

$sql2 = mysql_query("INSERT INTO updatetrail (d_no,d_name, d_position, d_staff_name, d_noic_nopassport, d_type_training, d_date_training, d_date_expired, d_sijil)
VALUES
($inserted_id,'$_POST[d_name]','$_POST[d_position]','$_POST[d_staff_name]','$_POST[d_noic_nopassport]','$_POST[d_type_training]','$_POST[d_date_training]','$_POST[d_date_expired]','".addslashes($_FILES['file']['name'][$count])."')") or die("Error: " . mysql_error());

insertAuditTrail(date('Y-m-d H:i:s'),"Module:Update User Details" ,"User Details successfully edited",$_SESSION['userID'],$inserted_id); 
  $Result1 = mysql_query($updateSQL, $dbconn) or die(mysql_error());

    $count=$count + 1;


    }   
      echo "<script language=\"JavaScript\">{                                                                                         
    location.href=\"update(power).php\";                                                                                               
    }</script>";
    if (isset($_SERVER['QUERY_STRING'])) {
   $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
   header(sprintf("Location: %s", $updateGoTo));    
}

$inserted_id=$_POST['departmentID']; $inserted_id = mysql_insert_id(); What is this for? Same variable changed in mate rows — bksi, Nov 07 '13 at 02:45
$inserted_id = mysql_insert_id() is to get the last id for the updated data. but it still not get the id.. so i try put $inserted_id=$_POST['departmentID'].. but it's not working.. =_=" — user2963035, Nov 07 '13 at 02:52

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

You do understand that your code has unspeakably massive security flaws, right? Also that you're using a deprecated function?

But disregarding those for the moment, your query would work using the built-in MySQL function LAST_INSERT_ID() that can go right into your query and will be interpreted by the server without you needing to fetch it:

$sql2 = mysql_query("INSERT INTO updatetrail (d_no,d_name, d_position, ...
VALUES (LAST_INSERT_ID(),'$_POST[d_name]', ...

Actually... um, wait... in your code... you aren't actually doing an insert prior to this. There is no mysql_insert_id() in php or LAST_INSERT_ID() available when you didn't do an insert.

If you want the id from the row you updated (not inserted) in the prior query, you have to SELECT it.

edited May 23 '17 at 10:24

Community

1
1

answered Nov 07 '13 at 02:48

Michael - sqlbot

169,571
25
353
427

i've try to use $sql2 = mysql_query("INSERT INTO updatetrail (d_no,d_name, d_position, ... VALUES (LAST_INSERT_ID(),'$_POST[d_name]', ... but it's still not save the id of updated data into the "updatetrail" table =_=" – user2963035 Nov 07 '13 at 02:59
Silly me, I assumed you were actually doing an `INSERT` before trying to get the id of the "inserted" row. – Michael - sqlbot Nov 07 '13 at 03:10
Do you mean like this? i try but not working.. =') $sql2 = mysql_query("INSERT INTO updatetrail SELECT * FROM department WHERE d_no = $inserted_id; ") or die("Error: " . mysql_error()); – user2963035 Nov 07 '13 at 03:33
No, not like that. If you want the id (?) from department, then by itself, you need to `SELECT id FROM department WHERE...` you cannot do what you are wanting to do in or after an update query, only after an `INSERT`. So, you have to select it separately, as a separate query. – Michael - sqlbot Nov 07 '13 at 03:48
sorry i'm not understand, can you do me a favor to show me the code? \(n.n)/ – user2963035 Nov 07 '13 at 03:56

How can i insert the id of updated data into different table?

1 Answers1