Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

PHP and MySQL: having trouble updating database after form submit

I'm creating my first tool in apache, MySql, php with Dreamweaver CS5 to make updating a webpage easier for someone.  In my table I have: record_id, body, image1, image2, image3, section.  My select statements works fine, I tried outputting the values of them right before the form submit and they are all correct.  The redirect specified while using Dreamweaver's update record tool takes affect properly no matter what I set it too.  But, My table doesn't update.  I found information online about someone having similar behavior suggesting to take out any and all spaces, capitals or special characters but I didn't find any.  One thing I don't understand in my Dreamweaver generated code is that all variables in my queries are as follows:  WHERE tablename.section = %s  I don't know where it is getting the %s from.  Also in Dreamweaver CS5 when defining an update record using the server behavior tab it doesn't ask for you to specify which exact record you want to update with a WHERE clause.  It's been doing this automatically with the %s variable.  I wanted to also mention that in case it was relevant.

Thanks, Dan  
0
andrewaiello
Asked:
andrewaiello
  • 3
  • 2
2 Solutions
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Can you post the final query and the php code used to do the update?
0
 
andrewaielloAuthor Commented:
This is the code I had:

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"] == "Mainform")) {
  $updateSQL = sprintf("UPDATE bouncehouses SET bouncehouses_body=%s, bouncehouses_image1=%s, bouncehouses_image2=%s, bouncehouses_image3=%s, bouncehouses_sections=%s WHERE record_id=%s",
                       GetSQLValueString($_POST['editor1'], "text"),
                       GetSQLValueString($_POST['image1'], "text"),
                       GetSQLValueString($_POST['image2'], "text"),
                       GetSQLValueString($_POST['image3'], "text"),
                       GetSQLValueString($_POST['section'], "text"),
                       GetSQLValueString($_POST['id'], "int"));

  mysql_select_db($database_inline1, $inline1);
  $Result1 = mysql_query($updateSQL, $inline1) or die(mysql_error());
}
?>

But after realizing the %s probably meant string and it was defined just after the query I the query to this:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "Mainform")) {
  $updateSQL = sprintf("UPDATE bouncehouses SET bouncehouses_body=%s, bouncehouses_image1=%s, bouncehouses_image2=%s, bouncehouses_image3=%s WHERE bouncehouses_sections=%s",
                       GetSQLValueString($_POST['editor1'], "text"),
                       GetSQLValueString($_POST['image1'], "text"),
                       GetSQLValueString($_POST['image2'], "text"),
                       GetSQLValueString($_POST['image3'], "text"),
                       GetSQLValueString($_POST['section'], "text"));

  mysql_select_db($database_inline1, $inline1);
  $Result1 = mysql_query($updateSQL, $inline1) or die(mysql_error());
}

I have to make a few changes before I can test this change however.  So I don't know if this works yet, but I believe this change makes sense though.

0
 
andrewaielloAuthor Commented:
The changed I made don't seem to work.  The definitions of the variables for the update query match up but it still isn't updating.  This is my current php code about the doctype:

<?php require_once('Connections/inline1.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"] == "Mainform")) {
  $updateSQL = sprintf("UPDATE bouncehouses SET bouncehouses_body=%s, bouncehouses_image1=%s, bouncehouses_image2=%s, bouncehouses_image3=%s WHERE bouncehouses_sections=%s",
                       GetSQLValueString($_POST['editor1'], "text"),
                       GetSQLValueString($_POST['image1'], "text"),
                       GetSQLValueString($_POST['image2'], "text"),
                       GetSQLValueString($_POST['image3'], "text"),
                       GetSQLValueString($_POST['section'], "text"));

  mysql_select_db($database_inline1, $inline1);
  $Result1 = mysql_query($updateSQL, $inline1) or die(mysql_error());
}

$section_rsMain = "Bounce_Houses";
if (isset($_GET['section'])) {
  $section_rsMain = $_GET['section'];
}
mysql_select_db($database_inline1, $inline1);
$query_rsMain = sprintf("SELECT * FROM bouncehouses WHERE bouncehouses.bouncehouses_sections = %s", GetSQLValueString($section_rsMain, "text"));
$rsMain = mysql_query($query_rsMain, $inline1) or die(mysql_error());
$row_rsMain = mysql_fetch_assoc($rsMain);
$totalRows_rsMain = mysql_num_rows($rsMain);
?>
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Print out the query ($updateSQL) before it sends it to the database.

Verify in a query window that the query does what you want it to do.

0
 
andrewaielloAuthor Commented:
Thank you for your suggestion, It led me to find out what I was doing wrong.  I ran MySQL_Query to see what the output was and it came out as true.  So I saw that the update was running just getting the wrong value.  After tracing it back I realized in my hidden form field I left out the "echo" in "echo $GET_['section'];".  Thank you again.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now