Skip to main content
N
NewbieQ
Participant
August 7, 2011
Question

Update and Insert into MySQL database

  • August 7, 2011
  • 2 replies
  • 1009 views

I have an "Add note page" that is supposed to be able to add a note for a customer and update any relevant information, so the user will type a note in and use drop downs to make any changes, and click save and have the database update 2 different tables, but its not working too well. FYI the notes are in a one to many relationship with the customers information table. I am using the dreamweaver wizards for the php code, and javascript to adjust the customer information table while the user is typing the note. I also use javascript to submit both forms at the same time, which should then reload that persons main page... What am I doing wrong here?

I'm including an abbreveated version of the code, i am using dreamweaver php update and insert wizards...Here is the relevant code:

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

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

  $insertSQL = sprintf("INSERT INTO custnotes (NoteField1, NoteField2, NoteField3, NoteField4) VALUES (%s, %s, %s, %s)",

                       GetSQLValueString($_POST['NoteField1'], "int"),

                       GetSQLValueString($_POST['NoteField2'], "text"),

                       GetSQLValueString($_POST['NoteField3'], "date"),

                       GetSQLValueString($_POST['NoteField4'], "text"));

  mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);

  $Result1 = mysql_query($insertSQL, $CustInfo_mySQL) or die(mysql_error());

  $insertGoTo = "CustHome.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

    $insertGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $insertGoTo));

}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "UpdateCustInfo")) {

  $updateSQL = sprintf("UPDATE custinfo SET LastName=%s, FirstName=%s, Address=%s, Address2=%s, City=%s, WHERE EnrollNumber=%s",

                       GetSQLValueString($_POST['LastName'], "text"),

                       GetSQLValueString($_POST['FirstName'], "text"),

                       GetSQLValueString($_POST['Address'], "text"),

                       GetSQLValueString($_POST['Address2'], "text"),

                       GetSQLValueString($_POST['City'], "text"),

                       GetSQLValueString($_POST['EnrollNumber'], "text"));

  mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);

  $Result1 = mysql_query($updateSQL, $CustInfo_mySQL) or die(mysql_error());

  $updateGoTo = "CustHome.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";

    $updateGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $updateGoTo));

}

$colname_CustInfo = "-1";

if (isset($_GET['EnrollNumber'])) {

  $colname_CustInfo = $_GET['EnrollNumber'];

}

mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);

$query_CustInfo = sprintf("SELECT * FROM custinfo WHERE EnrollNumber = %s", GetSQLValueString($colname_CustInfo, "text"));

$CustInfo = mysql_query($query_CustInfo, $CustInfo_mySQL) or die(mysql_error());

$row_CustInfo = mysql_fetch_assoc($CustInfo);

$totalRows_CustInfo = mysql_num_rows($CustInfo);

$colname_CustNotes = "-1";

if (isset($_GET['EnrollNumber'])) {

  $colname_CustNotes = $_GET['EnrollNumber'];

}

mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);

$query_CustNotes = sprintf("SELECT * FROM custnotes WHERE FKEnrollNumber = %s", GetSQLValueString($colname_CustNotes, "text"));

$CustNotes = mysql_query($query_CustNotes, $CustInfo_mySQL) or die(mysql_error());

$row_CustNotes = mysql_fetch_assoc($CustNotes);

$totalRows_CustNotes = mysql_num_rows($CustNotes);

?>

<html>

<head>

<title>Add Note</title>

<style>

.CustInfo{

     display: none;

}

</style>

<script type="text/javascript">

     function SaveAndClose();{

          document.UpdateCustInfo.submit();

          document.AddNoteForm.submit();

     }

</script>

</head>

<body><!--Continue showing relevant information-->

<div class="CustInfo">

<form action="<?php echo $editFormAction; ?>" method="post" name="UpdateCustInfo" id="UpdateCustInfo">

<table align="center">

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">EnrollNumber:</td>

      <td> </td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">LastName:</td>

      <td><input type="text" name="LastName" id="LastName" value="<?php echo htmlentities($row_CustInfo['LastName'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">FirstName:</td>

      <td><input type="text" name="FirstName" id="FirstName" value="<?php echo htmlentities($row_CustInfo['FirstName'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">Address:</td>

      <td><input type="text" name="Address" id="Address" value="<?php echo htmlentities($row_CustInfo['Address'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">Address2:</td>

      <td><input type="text" name="Address2" id="Address2" value="<?php echo htmlentities($row_CustInfo['Address2'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">City:</td>

      <td><input type="text" name="City" id="City" value="<?php echo htmlentities($row_CustInfo['City'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>

    </tr>

  <input type="hidden" name="MM_update" value="UpdateCustInfo" />

  <input type="hidden" name="EnrollNumber" value="<?php echo $row_CustInfo['EnrollNumber']; ?>" />

</form>

</div>

<form action="<?php echo $editFormAction; ?>" method="post" name="AddNoteForm" id="AddNoteForm">

  <table>

  <tr valign="baseline">

      <td nowrap="nowrap" align="right">Field1:</td>

      <td><input type="text" name="Field1" id="Field1" value="<?php echo $row_CustInfo['Field1']; ?>" size="32" /></td>

    </tr>   

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">Field2:</td>

      <td><input type="text" name="Field2" id="Field2" value="<?php echo $row_CustInfo['Field2']; ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">Field3:</td>

      <td><input type="text" name="Field3" id="Field3" value="<?php echo $row_CustInfo['Field3']; ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">Field4:</td>

      <td><input type="text" name="Field4" id="Field4" value="<?php echo $row_CustInfo['Field4']; ?>" size="32" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right">Field5:</td>

      <td><input type="text" name="Field5" id="Field5" value="<?php echo $row_CustInfo['Field5']; ?>" size="32" /></td>

    </tr>

    <tr><td><input type="button" name="ValidateNote" value="Add Note" onclick="SaveAndClose();" /></td></tr>

  </table>

  <input type="hidden" name="MM_insert" value="AddNoteForm" />

</form>

</body>

</html>

<?php

mysql_free_result($CustInfo);

mysql_free_result($CustNotes);

?>

Any help would be appreciated, ive been working on this for 4 days now...

This topic has been closed for replies.

2 replies

N
NewbieQAuthor
Participant
August 8, 2011

Here is my update.

So I created a new php document, and used only an update query on the form, and created a secondary form that posts information for the insert command on a second page. The problem is that the update doesn't work on this form. Interestingly i've created a third page that just updates information and that works fine. Its when I use javascript to submit the form that it seems to fail altogether, its the only thing I can think of...

The code to the problem page.

<?php require_once('Connections/CustInfo_mySQL.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $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']);

}

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

  $updateSQL = sprintf("UPDATE custinfo SET LastName=%s, FirstName=%s, Address=%s, Address2=%s, City=%s, WHERE EnrollNumber=%s",

                       GetSQLValueString($_POST['LastName'], "text"),

                       GetSQLValueString($_POST['FirstName'], "text"),

                       GetSQLValueString($_POST['Address'], "text"),

                       GetSQLValueString($_POST['Address2'], "text"),

                       GetSQLValueString($_POST['City'], "text"),

                       GetSQLValueString($_POST['EnrollNumber'], "text"));

  mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);

  $Result1 = mysql_query($updateSQL, $CustInfo_mySQL) or die(mysql_error());

  $updateGoTo = "AddFinalNote.php"; // If I change this to $updateGoTo = ""; then the Update will save

  if (isset($_SERVER['QUERY_STRING'])) {

    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";

    $updateGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $updateGoTo));

}

$colname_CustInfo = "-1";

if (isset($_GET['recordID'])) {

  $colname_CustInfo = $_GET['recordID'];

}

mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);

$query_CustInfo = sprintf("SELECT * FROM custinfo WHERE EnrollNumber = %s", GetSQLValueString($colname_CustInfo, "text"));

$CustInfo = mysql_query($query_CustInfo, $CustInfo_mySQL) or die(mysql_error());

$row_CustInfo = mysql_fetch_assoc($CustInfo);

$totalRows_CustInfo = mysql_num_rows($CustInfo);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

<style>

.UpdateCust{

position:absolute;

left:0%;

top:75%;

}

.EditCust{

position:absolute;

left:33%;

top:75%;

}

.OrigCust{

position:absolute;

left:66%;

top: 75%;

}

</style>

<script src="DataBase_Scripts/Add_Note/WIPAN.js"></script>

</head>

<body>

<body>

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

<!--The forms are generic to the dreamweaver update query wizard-->

<!--Cutting unneeded code...the following is the end of the generic form code-->

  <input type="hidden" name="MM_update" value="form1" />

  <input type="hidden" name="EnrollNumber" value="<?php echo $row_CustInfo['EnrollNumber']; ?>" />

</form>

<!--I create my own form-->
<form method="post" id="AddNoteForm" name="AddNoteForm" action="<?php echo "AddFinalNote.php"; ?>">
<!--once again cutting unneeded code, the ending of this form is the following-->
<input type="button" onClick="ValidateNoteForm();" name="addnote" id="addnote" value="Save Note" >
</form>
<!--This points to a script that has the following-->
<script type="text/javascript">
function ValidateNoteForm();{
     document.form1.submit(); //This is the generic update form that dreamweaver created, no changes were made to this form.
     document.AddNoteForm.submit(); //This is my form that I created, assuming this works, it should submit both forms, and then direct the post to another //form that includes only the notes and then a final submission to go back to custhome.php
}
</script>
Now here are the errors i'm getting, running through the form with $UpdateGoTo pointing to the AddFinalNote.php, the page will "submit" with no errors, the AddFinalNote.php will load with my second forms post and then I click on submit and it will load to CustHome.php. The CustHome.php will show the new note added, but won't save the update information. If I change $UpdateGoTo=""; then the page will reload, and will save the Update, but won't allow me to complete my note for obvious reasons.
Here is the code to the stand alone update page that works perfectly, once again using the wizard from dreamweaver...
<?php require_once('Connections/CustInfo_mySQL.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  if (PHP_VERSION < 6) {
    $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']);
}
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE custinfo SET LastName=%s, FirstName=%s, Address=%s, Address2=%s, City=%s, `State`=%s, ZipCode=%s, WHERE EnrollNumber=%s",
                       GetSQLValueString($_POST['LastName'], "text"),
                       GetSQLValueString($_POST['FirstName'], "text"),
                       GetSQLValueString($_POST['Address'], "text"),
                       GetSQLValueString($_POST['Address2'], "text"),
                       GetSQLValueString($_POST['City'], "text"),
                       GetSQLValueString($_POST['State'], "text"),
                       GetSQLValueString($_POST['ZipCode'], "text"),
                       GetSQLValueString($_POST['EnrollNumber'], "text"));
  mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);
  $Result1 = mysql_query($updateSQL, $CustInfo_mySQL) or die(mysql_error());
  $updateGoTo = "ss.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}
$colname_CustInfo = "-1";
if (isset($_GET['recordID'])) {
  $colname_CustInfo = $_GET['recordID'];
}
mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);
$query_CustInfo = sprintf("SELECT * FROM custinfo WHERE EnrollNumber = %s", GetSQLValueString($colname_CustInfo, "text"));
$CustInfo = mysql_query($query_CustInfo, $CustInfo_mySQL) or die(mysql_error());
$row_CustInfo = mysql_fetch_assoc($CustInfo);
$totalRows_CustInfo = mysql_num_rows($CustInfo);
$colname_EditCust = "-1";
if (isset($_GET['recordID'])) {
  $colname_EditCust = $_GET['recordID'];
}
mysql_select_db($database_CustInfo_mySQL, $CustInfo_mySQL);
$query_EditCust = sprintf("SELECT * FROM custinfo WHERE EnrollNumber = %s", GetSQLValueString($colname_EditCust, "text"));
$EditCust = mysql_query($query_EditCust, $CustInfo_mySQL) or die(mysql_error());
$row_EditCust = mysql_fetch_assoc($EditCust);
$totalRows_EditCust = mysql_num_rows($EditCust);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit Customer</title>
<style>
.CustEdit{
position:absolute;
width: 50%;
left: 25%;
}
</style>
</head>
<body>
<div class="CustEdit">
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<!--Cutting unneccessary code once again -->
  <table width="50%">
  <tr valign="baseline">
    <td nowrap="nowrap" align="center"><input type="submit" value="Update record" /></td>
  </tr>
        </table>
      </tr>
    </table>   
  </table>
  <input type="hidden" name="EnrollNumber" value="<?php echo $row_EditCust['EnrollNumber']; ?>" />
</form>
</div>
</body>
</html>
<?php
mysql_free_result($CustInfo);
mysql_free_result($EditCust);
?>
Now the only thing I can think of that would cause the error is the javascript that is requesting the form submission. Is it possible that when javascript requests multiple submissions the only thing that is being done is the Post variables are being passed but the request to communicate with the server is failing, and therefore not being updated? Through my testing it seems like only one (update or insert) will happen, but refuses to do both. Is there anyway I can clarify the scripting for submission? The reason i'm using javascript is so that way the client can choose the type of note and the program will decide the appropriate changes to the customers data.
On a side note another failure is that when the Post variables do pass to the AddFinalNote.php, if I do a body onload submit form, the page will ignore inserting the data and load up CustHome.php and won't show ANY changes at all, no note, and no data change. Please help! I'm at my wits end.

A
Anonymous
August 7, 2011

Your note being very descriptive when you say "but its not working  too well".  Perhaps more detail on the results you are getting.

Also, you have

$Result1 = mysql_query($insertSQL, $CustInfo_mySQL) or die(mysql_error());

and

$Result1 = mysql_query($updateSQL, $CustInfo_mySQL) or die(mysql_error());

Renaming one of the $Result1  might clear you up.

Gary

N
NewbieQAuthor
Participant
August 7, 2011

I renamed both result1 line but it still isn't working. Here is the problem on submit it will only accept the update, not the insert. I'm not completely sure if it is the javascript code behind it, or the server is losing the posting to one of the forms to make the changes to the table. I've even tried creating a second form that echos only the posting of the note log and then have that page auto submit after 1 second, but its just not adding to the note table. The note table does work properly I have added notes successfully from a stand alone insert page, but its when these two things are trying to run in sync that it seems to not fail.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices