update form for cms won't work. PLEASE HELP

[Guest]

Hello,

I am using DWCS3 with MAMP and MBP Leopard 10.5.6.

I am working through David Powers Book Essential Guide to Dreamweaver CS3, and I am having alot of trouble with the update_user form (which I have renamed update_customer) for the content management system. I have started this particular page a few times, but I can't seem to sort it out. It is copied from the customer_registration page which does work and populates the customer table on my database.

I have gone through the instructions slowly step by step, a number of times, and I have checked the code over and over.

In my site definition I have selected: Links relative to Document: below is the code for my connection;

<?php require_once('Connections/connAdmin.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{

The code below keeps displaying in the property inspector with a big ? to the left of the window along with the word server.

f (!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']);
}

$colname_getCustomer = "-1";
if (isset($_GET['customer_id'])) {
  $colname_getCustomer = $_GET['customer_id'];
}
mysql_select_db($database_connAdmin, $connAdmin);
$query_getCustomer = sprintf("SELECT * FROM customer WHERE customer_id = %s", GetSQLValueString($colname_getCustomer, "int"));
$getCustomer = mysql_query($query_getCustomer, $connAdmin) or die(mysql_error());
$row_getCustomer = mysql_fetch_assoc($getCustomer);
$totalRows_getCustomer = mysql_num_rows($getCustomer);

// *** Check if username exists
$error = array();
$MM_flag="MM_update";
if (isset($_POST[$MM_flag])) {
  // check name
  if (empty($_POST['first_name']) || empty($_POST['family_name'])) {
      $error['name'] = 'Please enter both first name and family name';
    }
  // set a flag that assumes the password is OK
  $pwdOK = true;
  // trim leading and trailing white space
  $_POST['pwd'] = trim($_POST['pwd']);
  // if password field is empty, use existing password
  if (empty($_POST['pwd'])) {
      $_POST['pwd'] = $row_getCustomer['pwd'];
    }
  //otherwise conduct normal checks
  // if less than 6 characters, create alert and set flag to false
  if (strlen($_POST['pwd']) < 6) {
    $error['pwd_length'] = 'Your password must be at least 6 characters';
    $pwdOK = false;
    }
  // if no match, create alert and set flag to false
  if ($_POST['pwd'] != trim($_POST['conf_pwd'])) {
    $error['pwd'] = 'Your passwords don\'t match';
    $pwdOK = false;
    }
  // if password OK, encrypt it
  if ($pwdOK) {
    $_POST['pwd'] = sha1($_POST['pwd']);
    }
   }
  // regex to identify illegal characters in email address
  $checkEmail = '/^[^@]+@[^\s\r\n\'";,@%]+$/';
  if (!preg_match($checkEmail, trim($_POST['email']))) {
    $error['email'] = 'Please enter a valid email address';
    }
  // check username
  $_POST['username'] = trim($_POST['username']);
  $loginUsername = $_POST['username'];
  if (strlen($loginUsername) < 6) {
    $error['length'] = 'Please select a username that contains at least 6 characters';
    }
  $LoginRS__query = sprintf("SELECT username FROM customer WHERE username=%s AND customer_id !=".$_POST['customer_id'], GetSQLValueString($loginUsername, "text"));
  mysql_select_db($database_connAdmin, $connAdmin);
  $LoginRS=mysql_query($LoginRS__query, $connAdmin) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);

  //if there is a row in the database, the username was found - can not add the requested username
  if($loginFoundUser){
  $error['username'] = "$loginUsername is already in use. Please choose a different username.";
  }
}

if (!$error) {
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE customer SET first_name=%s, family_name=%s, door_number=%s, street_name=%s, town=%s, county=%s, postcode=%s, telephone=%s, email=%s, username=%s, pwd=%s, admin_priv=%s WHERE customer_id=%s",
                       GetSQLValueString($_POST['first_name'], "text"),
                       GetSQLValueString($_POST['family_name'], "text"),
                       GetSQLValueString($_POST['door_number'], "text"),
                       GetSQLValueString($_POST['street_name'], "text"),
                       GetSQLValueString($_POST['town'], "text"),
                       GetSQLValueString($_POST['county'], "text"),
                       GetSQLValueString($_POST['postcode'], "text"),
                       GetSQLValueString($_POST['telephone'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['username'], "text"),
                       GetSQLValueString($_POST['pwd'], "text"),
                       GetSQLValueString($_POST['admin_priv'], "text"),
                       GetSQLValueString($_POST['customer_id'], "int"));

  mysql_select_db($database_connAdmin, $connAdmin);
  $Result1 = mysql_query($updateSQL, $connAdmin) or die(mysql_error());

  $updateGoTo = "list_customer.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}
// if the record has been inserted, clear the $_POST array
$_POST = array();
}

In my php error logs

[02-Sep-2009 15:41:03] PHP Notice:  Undefined index:  email in /Applications/MAMP/htdocs/HelenLovell/update_customer.php on line 83
[02-Sep-2009 15:41:03] PHP Notice:  Undefined index:  username in /Applications/MAMP/htdocs/HelenLovell/update_customer.php on line 87
[02-Sep-2009 15:41:03] PHP Notice:  Undefined index:  customer_id in /Applications/MAMP/htdocs/HelenLovell/update_customer.php on line 92

These are all validations that are working in the original register_customer form but don't work in the update_customer form.

Can someone please help.

[David_Powers]

I'm not sure what your question is, but the last of the error messages says this: Undefined index:  customer_id. That means that $_POST['customer_id'] does not exist.

You need to store the value of customer_id in a hidden field for the Update Record server behavior to work.

[Guest]

Hi David,

sorry for being unclear.

My problem is after I've gone through the instructions, upload the page to the browser, enter the info into the form and click the update button, I get a message saying:

You have an error in your SQL syntax check the manual that corresponds to your MySQL server version for the right syntax to use near " at line 1.

and the code posted above is in the property inspector, with a qusetion mark next to it.

So how do I sort that out?

Thanks.

[David_Powers]

I don't know why it's appearing in the Property inspector, but the SQL error is almost certainly caused by exactly the same thing as I mentioned before. You need to store the customer_id in a hidden field.

If you do have a hidden field, the error is being caused by customer_id not being sent to the update page correctly in the query string of the URL (see Figure 15-4 on page 494).

[Guest]

Thanks David for getting back to me,

I have got a hidden field, and I thought I had passed the primary key (customer_id) to the query string and stored it in  the hidden field when I turned the EDIT text into a link for the update page and clicked the parameters button along side the URL field. I typed customer_id in the name field, then clicked the lightening bolt icon and selected customer_id in the Dynamic data dialog box.

If the the customer_id primary key was not passed to the query string, how do I sort that out can I go back through the code and it by hand? If so how?

Or should I start the process again? I have already done this a few times.

Thanks.

[Guest]

Hi,

I'm still stuck on the problem of passing the customer_id (primary key) to the URL query string, and storing it in a hidden field.

Dreamweaver doesn't seem to want to pass the primary key to the URL query string, properly for some reason.

How can I get around this?

Can anyone help?

Thanks.

[David_Powers]

slam38 wrote:

Dreamweaver doesn't seem to want to pass the primary key to the URL query string, properly for some reason.

It's not Dreamweaver that passes the primary key, but the PHP code that you create with Dreamweaver. The first thing to check is that the link you are using to pass the primary key to the update page is correctly formed.

Since you're using my CS3 book, the relevant pages are 462-464. Although that section deals with displaying individual records in an ordinary page, the principle is identical. The only difference with an update form is that the record details are displayed inside a form ready for editing.

Make sure that the query string is being added correctly to the end of the URL (see Figure 14-10). Also check the format of the code at the bottom of page 464.