PHP form to Update MYSQL DB

[oxidetones]

Hi folks,

I cant work out whats wrong with this... Ive built forms before to insert/update/delete, but this one just wont work and Im tearing my hair out on it....

SQL:

$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 invoice SET Status=%s WHERE ID=%s",
   GetSQLValueString($_POST['Status'], "text"),
   GetSQLValueString($_POST['ID'], "int"));
 mysql_select_db($database_dataconnect, $dataconnect);
 $Result1 = mysql_query($updateSQL, $dataconnect) or die(mysql_error());
 $updateGoTo = "index3.php";
 if (isset($_SERVER['QUERY_STRING'])) {
 $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
 $updateGoTo .= $_SERVER['QUERY_STRING'];
 }

The form:

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
 
 <?php do { ?>
 <?php echo $row_rsStatus['ID']; ?> :: 
 <input type="text" name="Status" value="<?php echo htmlentities($row_rsStatus['Status'], ENT_COMPAT, ''); ?>" size="2" /> :: 
 <?php echo htmlentities($row_rsStatus['Name'], ENT_COMPAT, ''); ?>
 <br />
<?php } while ($row_rsStatus = mysql_fetch_assoc($rsStatus)); ?>
<input type="submit" value="Update record" />
 
 <input type="hidden" name="MM_update" value="form1" />
 <input type="hidden" name="ID" value="<?php echo $row_rsStatus['ID']; ?>" />
 </form>

Any pointers would be greatly appreciated

[oxidetones]

I shouldve said what the issue is.... I can change the text in the Status textbox and when I click submit the page loads fine, however the new data isnt recorded in the DB

[jatar_k]

try echoing your query and then test the query via the command line or a manager like phpmyadmin

see if it makes the change or gives an error

this code written by dreamweaver? some weird bits in there.

[oxidetones]

Yes, this is DW code...

I changed the SQL from:

UPDATE invoice SET Status=%s WHERE ID=%s

To:

UPDATE invoice SET Status='R' WHERE ID=1039

and ran it in mysqladmin, it worked fine.

echoing the data points (instead of using htmlentities) works fine in the browser... I have no idea why this isnt working....

[jatar_k]

try echoing $updateSQL and see if it is creating the query properly

[oxidetones]

<?php echo $updateSQL; ?> doesnt print anything

[jatar_k]

I meant in the script, the logic being that the query works when written explicitly but not when created via the script, causing me to think something is wrong here

$updateSQL = sprintf("UPDATE invoice SET Status=%s WHERE ID=%s",
GetSQLValueString($_POST['Status'], "text"),
GetSQLValueString($_POST['ID'], "int"));

so add the echo line right after that and see what it shows

[oxidetones]

Hi Jatar, Im not sure if tis is what you mean but when I have the following, nothing has changed:

$updateSQL = sprintf("UPDATE invoice SET Status=%s WHERE ID=%s",
GetSQLValueString($_POST['Status'], "text"),
GetSQLValueString($_POST['ID'], "int"));
echo $updateSQL;

[oxidetones]

Just a thought... I have the SQL and the HTML in a php file with no head, body tags etc, as I am calling the file as an include. Could that be causing the issue?

[jatar_k]

no that shouldn't be an issue

what did that code echo?

[oxidetones]

echong the $ directly after the SQL didnt produce anything....