Updating records displayed in Dynamic Tables

Report · Oct 17, 2011

I am currently building a web based system that handels orders. There are various pages that all use dynamic tables (repeat tables) to display different details about the orders and all pages use the same "orders" database. I have created a recordset on each page to display the data and everything works corrdectly. In the repeat table I added an "update" column and for each row there is an "update" button in that column. My goal is for the user to be able to edit the fields that are editable and click the button "update" and have it update the record in the database. I have used the update record function in dreamweaver and all works correctally when there is only one row dispalyed however when more than one row is displayed it does not update liek planned... The form is wrapped around the repead table. Any advice on how to fix this would be great. Thanks so much.

Report · Oct 17, 2011

The only record that will update is the last record of the repeat table. All the records before when submitted dont do anything

Report · Oct 17, 2011

I guess we'd have to see your code to know why it's not working.

Report · Oct 17, 2011

AFAIK, DW does not handle multiple row updates. You will have to use a loop to update multiple rows. What scripting language are you using?

Report · Oct 17, 2011

Thats what I figured, I'm using PHP with a MySQL database... Any help is appreciated. Thanks

Report · Oct 17, 2011

DW can handle such a thing, but you would have to hack the code it writes.

Report · Oct 17, 2011

THat would be great here a copy of one of my pages code....

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

<?php

//initialize the session

if (!isset($_SESSION)) {

session_start();

}

// ** Logout the current user. **

$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";

if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){

$logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){

//to fully log out a visitor we need to clear the session varialbles

$_SESSION['MM_Username'] = NULL;

$_SESSION['MM_UserGroup'] = NULL;

$_SESSION['PrevUrl'] = NULL;

unset($_SESSION['MM_Username']);

unset($_SESSION['MM_UserGroup']);

unset($_SESSION['PrevUrl']);

$logoutGoTo = "../../Admin%20Login.php";

if ($logoutGoTo) {

header("Location: $logoutGoTo");

exit;

}

?>

<?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 orders SET status=%s WHERE orderID=%s",

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

GetSQLValueString($_POST['orderid'], "int"));

mysql_select_db($database_production, $production);

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

$updateGoTo = "finishline1.php";

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

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

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

}

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

}

$totalRows_Recordset1 = mysql_num_rows($Recordset1);mysql_select_db($database_production, $production);

$query_Recordset1 = "SELECT * FROM orders WHERE finishline = 'Finish Line 1' AND orders.status <> 'Ready to Ship (D)' AND orders.status <> 'Shipped' ORDER BY dealername ASC";

$Recordset1 = mysql_query($query_Recordset1, $production) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

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

<head>

<title>Charmac Trailers | Production Section</title>

<!--

body {

background-image: url(../../../../Images/back-1.jpg);

background-repeat: repeat-x;

}

-->

</style>

<!--

function MM_preloadImages() { //v3.0

var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();

var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)

if (a.indexOf("#")!=0){ d.MM_p=new Image; d.MM_p[j++].src=a;}}

}

//-->

</script>

</head>

<body>

<div id="title">Finish Line #1</div>

<div id="idbar">Welcome, <?php echo $_SESSION['firstadmin']; ?> <?php echo $_SESSION['lastadmin']; ?></div>

</div>

<tr>

<td>Dealer</td>

<td>Model</td>

<td>Order Date</td>

<td>Rolled Date</td>

<td>Status</td>

<td>Color</td>

<td>Tire/Wheel</td>

<td>Notes</td>

<td>Update</td>

</tr>

<?php do { ?>

<tr>

<input name="orderid" type="text" id="orderid" value="<?php echo $row_Recordset1['orderID']; ?>" size="5" readonly="readonly" /></td>

<option value=<?php echo $row_Recordset1['status']?> selected="selected"><?php echo $row_Recordset1['status']?></option>

<option value="Welding Frame">Welding Frame</option>

<option value="Painting Frame">Painting Frame</option>

<option value="Rolled & Painted">Rolled & Painted</option>

<option value="Sheeting">Sheeting</option>

<option value="Roofing">Roofing</option>

<option value="Electrical (3)">Electrical (3)</option>

<option value="Flooring/Walls (4)">Flooring/Walls (4)</option>

<option value="Doors/Windows (5)">Doors/Windows (5)</option>

<option value="Quality Control">Quality Control</option>

<option value="Final Wash">Final Wash</option>

<option value="Ready To Ship (D)">Ready To Ship (D)</option>

<option value="Shipped">Shipped</option>

</select></td>

</tr>

<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

</table>

</form>

</div>

</map>

</div>

</div>

</div>

</body>

</html>

<?php

mysql_free_result($Recordset1);

?>

Report · Oct 18, 2011

For testing purposes, change this -

$updateSQL = sprintf("UPDATE orders SET status=%s WHERE orderID=%s",

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

GetSQLValueString($_POST['orderid'], "int"));

to this -

$updateSQL = sprintf("UPDATE orders SET status=%s WHERE orderID=%s",

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

GetSQLValueString($_POST['orderid'], "int"));

exit($updateSQL);

and let us know what you get when you select various rows to update. Let's see what that SQL Update string looks like....

Report · Oct 18, 2011

I edited that code and tested the web page... When I tried to update the status for a row the following was displayed in a white screen: "UPDATE orders SET status='Roofing' WHERE orderID=23" not sure if that is what was suppose to happen when I changed the code or not.

Report · Oct 18, 2011

However when I try other update other rows The text displayed in the screen still says "orderID=23" which is the order ID of the bottom row in the dynamic table. So it is still only trying to update oirderID=23 I assume instead of the selected order...

Report · Oct 18, 2011

If I instead created a repeating div for each order to be displayed would that possibly make it so it recognizes which order to update?

Report · Oct 18, 2011

Don't you want a separate form for each row? Otherwise there is no association between the ID values and the submit button. Try including the form tags within the loop.

Report · Oct 18, 2011

OK - I see what is happening. We will need to add a few more fields, and a bit of javascript magic to make this work.

Change this -

to this -

<input type="hidden" name="change_id<?php echo $row_Recordset1['orderID']; ?>" id="change_id<?php echo $row_Recordset1['orderID']; ?>" value="<?php echo $row_Recordset1['orderID']; ?>" />

<input type="hidden" name="change_status<?php echo $row_Recordset1['orderID']; ?>" id="change_status<?php echo $row_Recordset1['orderID']; ?>" value="<?php echo $row_Recordset1['status']; ?>" />

And change this -

to this -

<select name="status" id="status" onchange="document.getElementByID('change_status<?php echo $row_Recordset1['orderID']; ?>').value='this.value'">

If I have done this correctly, each row will now also contain two additional hidden fields containing the orderID of the record in that row, and the new status for that item.

Finally, you need to change the SELECT statement to take its parameters from these new fields:

$updateSQL = sprintf("UPDATE orders SET status=%s WHERE orderID=%s",

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

GetSQLValueString($_POST['change_id'], "int"));

Now try again. You should see a new string dumped to an otherwise blank page. Try this for several different rows and let's see each string, please.

I hope this works as I have limited ways to test it....

Report · Oct 18, 2011

Just edited the code as shown and have tested many rows... there is the same result for each row: "UPDATE orders SET status=NULL WHERE orderID=NULL" and it doesnt appear to update anything, Is there a way that I could change the form so that it repeats itself as well for each row instead of containting the entire repeat table? Thanks agian for all this help.

Report · Oct 18, 2011

I just restarted and built the table with all my content as before but without a repeat behavior, then I selected the row and wrapped a form just around the single row in dreamweaver and added the update record behavior that I needed. Next I selected the whole row again and applied the repeat behavior to it and it is working perfectly now... Seems pretty simple I just hadn't thought of trying to repeat the form with the record. Thanks again for all your help.

Report · Oct 18, 2011

I'll let you know if I run into any problems

Report · Oct 18, 2011

>Seems pretty simple I just hadn't thought of trying to repeat the form with the record.

Yes, this is what I mentioned earlier.

>>Don't you want a separate form for each row?

>>Otherwise there is no association between the ID values

>>and the submit button. Try including the form tags within the loop.

Report · Oct 18, 2011

Yes thank you I was trying some other solutions and must have looked over that... Thanks again for your help.

Report · Oct 19, 2011

Must admit that is certainly a simpler approach than my 4-armed monster.

Report · Oct 18, 2011

>DW can handle such a thing, but you would have to hack the code it writes.

Ah, I misunderstood the OP. I thought he wanted to update multiple row simultaneously. I see now he has a separate update button for each row.