Dreamweaver has some built-in tools for doing database inserts and updates, but they only handle basic inserts/updates of one record. What if you want to insert multiple records at a time, or update multiple records? Dreamweaver is not much help in this case, but using PHP the process is simple. The first part of this tutorial showed two often-used methods for doing multiple inserts. Part 2 will show how to do a multiple update using these two techniques. The tutorial contains two files: testupload.php and testupload2.php.
To use the files, create a MySQL database table using the following:
CREATE TABLE mytable (
myIDField int auto_increment primary key NOT NULL,
firstname varchar (50) NULL ,
lastname varchar (50) NULL ,
address varchar (50) NULL ,
active int NULL
)
If you followed part one of this tutorial, you will have this table already, and you should have some data in it already. You will need some sample data in the table in order to use the update process shown in this article.
When creating an update form, you also need to supply the form fields on your page with initial values from the database. We'll do that using a standard Dreamweaver recordset using the following SQL:
SELECT myIDField, firstname, lastname, address, active
FROM mytable
ORDER BY myIDField
With that in place, you will have the following server code on the page:
<?php require_once('../Connections/testconnection.php'); ?>
<?php
if (!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;
}
}
mysql_select_db($database_testconnection, $testconnection);
$query_rs = "SELECT myIDField, firstname, lastname, address, active
FROM mytable
ORDER BY myIDField
";
$rs = mysql_query($query_rs, $testconnection) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);
$totalRows_rs = mysql_num_rows($rs);
?>
If you are using Dreamweaver 8.0.1 or earlier, you will not have the GetSQLValueString() function, but you can add it manually for the update statements we will add later.
If you followed Part 1, you will have a form created for the insert page similar to the update form below, with three major differences: in the form below there is only one set of fields in a loop; the form fields now will have a value assigned to them instead of being blank; and the primary key will be added to the form as a hidden field. We will name like fields the same, taking care to give each like field a different ID to keep the html DOM and JavaScript happy and keep the html valid:
<form name="form1" method="post">
<?php $counter=1;
do { ?>
Customer<?php echo($counter);?>: <br />
Firstname:
<input type="text" name="firstname[]" id="firstname<?php echo($counter);?>"
value="<?php echo $row_rs['firstname']; ?>" />
<br />
Lastname:
<input type="text" name="lastname[]" id="lastname<?php echo($counter);?>"
value="<?php echo $row_rs['lastname']; ?>" />
<br />
Address:
<input type="text" name="address[]" id="address<?php echo($counter++);?>"
value="<?php echo $row_rs['address']; ?>" />
<input type="hidden" name="id[]"
value="<?php echo $row_rs['myIDFIeld']; ?>" />
<br />
<br />
<?php } while ($row_rs = mysql_fetch_assoc($rs)); ?>
<input name="submit" value="submit" type="submit" />
</form>
You can copy the PHP code into the field values or simply drag/drop the fields from the Bindings panel out of the recordset onto the text fields. This is a shortcut in Dreamweaver for supplying dynamic values to form fields.
When you browse the page, the like fields are named the same, but the ID is different because of the $counter variable tacked on to the end of the ID attribute. The $counter is incremented the last time we use it so that the next time through the loop the fields have different IDs.
The fields are named with array notation using brackets. This allows you to retrieve the fields using server side code -- the like fields will be submitted as an array. You then merely have to loop through the array, and update each row separately. The following is the PHP database update code for the form:
<?php
if(isset($_POST["firstname"])) {
for($i=0; $i < count($_POST["firstname"]); $i++) {
$updateSQL = sprintf("UPDATE mytable
SET firstname = '%s',
lastname = '%s',
address = '%s'
WHERE myIDField = %d",
GetSQLValueString($_POST["firstname"][$i], "text"),
GetSQLValueString($_POST["lastname"][$i], "text"),
GetSQLValueString($_POST["address"][$i], "text"),
GetSQLValueString($_POST["id"][$i], "int"));
mysql_select_db($database_testconnection, $testconnection);
$Result1 = mysql_query($updateSQL, $testconnection) or die(mysql_error());
}
}
?>
Again, just as in the code shown for the database insert, this technique can work well in some situations, but the field must not be a checkbox or radio button. Checkboxes and radio buttons do not exist to the server if they are not checked, so no array element is created for non-checked items, so when an item is checked the array does not reflect the correct values. However, unlike the insert code we used in part one, you can create a separate update statement for the checkboxes in the form. Let's add a checkbox named "active" to the form:
Active:
<input type="checkbox" name="active[]" value="<?php echo $row_rs['myIDField']; ?>"
<?php if($row_rs['active'] == 1) echo('checked="checked"');?>/>
Next, we'll write an update statement directly after the other update statement that will only update the active field in the table. We can update all rows in the database with one statement, since the field will not exist if the form field is not checked. The database field for a checkbox contains a 0 or a 1. We'll update the unchecked items to 0 and update the checked items to 1.
if(isset($_POST["active"])) {
$active = implode(",",$_POST["active"]);
$updateSQL = sprintf("UPDATE mytable
SET active = 1
WHERE myIDField IN (%s)", $active);
mysql_select_db($database_testconnection, $testconnection);
$Result1 = mysql_query($updateSQL, $testconnection) or die(mysql_error());
}else{
$active = '0';
}
$updateSQL = sprintf("UPDATE mytable
SET active = 0
WHERE myIDField NOT IN (%s)", $active);
mysql_select_db($database_testconnection, $testconnection);
$Result1 = mysql_query($updateSQL, $testconnection) or die(mysql_error());
Now, when we submit the form, the first update statement will update most of the fields. The next two update statements will take care of the checkbox field.
Again, this technique is limited and probably not a good option in some cases, due to the limitation with checkboxes and radio buttons.
As shown in part 1, PHP allows you to evaluate $_POST, $_GET, and other variables on the fly, as the array members can be expressed as a string key:
$_POST["firstname"]
This leads us to using a counter variable in each field:
$_POST["firstname$i"]
The variable $i would be the counter from 1 to however many rows you have.
That puts a whole new wrinkle on the multiple insert/update. Instead of naming the form fields with the same name and use the array, you would append a counter to the end of the fieldname:
<form name="form1" method="post">
<?php $counter=1;
do { ?>
Customer<?php echo($counter);?>: <br />
Firstname:
<input type="text" name="firstname<?php echo($counter);?>" id="firstname<?php echo($counter);?>" value="<?php echo $row_rs['firstname']; ?>" />
<br />
Lastname:
<input type="text" name="lastname<?php echo($counter);?>" id="lastname<?php echo($counter);?>" value="<?php echo $row_rs['lastname']; ?>" />
<br />
Address:
<input type="text" name="address<?php echo($counter);?>" id="address<?php echo($counter);?>" value="<?php echo $row_rs['address']; ?>" />
<input type="hidden" name="id<?php echo($counter);?>" value="<?php echo $row_rs['myIDField']; ?>" />
<br />
Active:
<input type="checkbox" name="active<?php echo($counter++);?>" value="1" <?php if($row_rs['active'] == 1) echo('checked="checked"');?>/>
<br />
<br />
<?php } while ($row_rs = mysql_fetch_assoc($rs)); ?>
<input name="totalrows" value="<?php echo ($counter-1);?> " type="hidden" />
<input name="submit" value="submit" type="submit" />
</form>
Now, you can use this information on the next page to do the multiple insert/update:
if(isset($_POST["totalrows"])) {
for($i=1; $i <= $_POST["totalrows"]; $i++) {
if(!isset($_POST["active$i"])) $_POST["active$i"] = 0;
$updateSQL = sprintf("UPDATE mytable
SET firstname = %s,
lastname = %s,
address = %s,
active = %s
WHERE myIDField = %d",
GetSQLValueString($_POST["firstname$i"], "text"),
GetSQLValueString($_POST["lastname$i"], "text"),
GetSQLValueString($_POST["address$i"], "text"),
GetSQLValueString($_POST["active$i"], "text"),
GetSQLValueString($_POST["id$i"], "int"));
mysql_select_db($database_testconnection, $testconnection);
$Result1 = mysql_query($updateSQL, $testconnection) or die(mysql_error());
}
}
This article showed two ways to do a multiple update. Both methods have their uses. The first method is easier to use and easier to program, however has problems when using checkboxes or radio buttons. The second method requires an index field to be appended to each field on your page, but works well with all types of form fields.