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 no help in this case, but using PHP the process is simple. This tutorial will show two often-used methods for doing multiple inserts and multiple update. The files testinsert.php and testinsert2.php are included in the download package showing the final code for both methods. Part 1 of this series will show how to do inserts and part 2 will show how to do a multiple update using these two techniques.
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
)
Form fields are generally named differently in your form. For example, you might have "firstname", "lastname", "address", etc. What if you are displaying multiple records, such as an address list? In this case, you could 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 your html valid:
<input type="text" name="firstname[]" id="firstname1" />
<input type="text" name="lastname[]" id="lastname1" />
<input type="text" name="address[]" id="address1" />
<input type="text" name="firstname[]" id="firstname2" />
<input type="text" name="lastname[]" id="lastname2" />
<input type="text" name="address[]" id="address2" />
<input type="text" name="firstname[]" id="firstname3" />
<input type="text" name="lastname[]" id="lastname3" />
<input type="text" name="address[]" id="address3" />
In this case, you have three sets of fields -- the like fields are named the same, but the ID is different. 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 insert each row separately:
<?php
if(isset($_POST["firstname"])) {
$testconnect = mysql_pconnect("localhost", "myusername", "mypass")
or trigger_error(mysql_error(),E_USER_ERROR);
for($i=0; $i < count($_POST["firstname"]); $i++) {
$insertSQL = sprintf("INSERT mytable
(firstname, lastname, address)
VALUES
('%s','%s','%s')",
$_POST["firstname"][$i],
$_POST["lastname"][$i],
$_POST["address"][$i]);
mysql_select_db("mydatabase", $testconnect);
$Result1 = mysql_query($insertSQL, $testconnect) or die(mysql_error());
}
}
?>
This technique can work well in some situations, but has a caveat: 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 is created for non-checked items, and when the item is checked the array does not reflect the correct values. For example, if only the checkbox in row 3 is checked, the checkbox array would only contain one item -- array item [0] -- and it would correspond to the third set of fields -- array item [2]. This clearly won't work if you have checkboxes. Also, because there is no key field in many cases in a database insert until after the insert takes place, there is no reliable way to match the checkbox with the correct record.
Obviously, this technique is limited and probably not a good option in many cases.
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 hidden 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:
<input type="text" name="firstname1" id="firstname1" />
<input type="text" name="lastname1" id="lastname1" />
<input type="text" name="address1" id="address1" />
<input type="text" name="firstname2" id="firstname2" />
<input type="text" name="lastname2" id="lastname2" />
<input type="text" name="address2" id="address2" />
<input type="text" name="firstname3" id="firstname3" />
<input type="text" name="lastname3" id="lastname3" />
<input type="text" name="address3" id="address3" />
There is one caveat to this method, however -- you must know how many elements exist. You should keep track of how many fields you have, and put the value into a hidden form field:
<input name="totalrows" value="3" type="hidden" />
Now, you can use this information on the next page to do the multiple insert/update:
<?php
if(isset($_POST["totalrows"])) {
$testconnect = mysql_pconnect("localhost", "myusername", "mypass") or
trigger_error(mysql_error(),E_USER_ERROR);
for($i=1; $i <= $_POST["totalrows"]; $i++) {
if(!isset($_POST["active$i"])) $_POST["active$i"] = 0;
$insertSQL = sprintf("INSERT mytable
(firstname, lastname, address, active)
VALUES
('%s','%s','%s',%d)",
$_POST["firstname$i"],
$_POST["lastname$i"],
$_POST["address$i"],
$_POST["active$i"]);
mysql_select_db("mydatabase", $testconnect);
$Result1 = mysql_query($insertSQL, $testconnect) or die(mysql_error());
}
}
?>
When creating SQL in the PHP page, it is also a good idea to clean the input coming from the user for the parameter to prevent SQL injections. The GetSQLValueString() function is used as part of the standard Dreamweaver insert and update behaviors, but can be copied to any page:
<?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;
}
}
Basically, you call the function with the parameter you want to clean, and the data type you want to use. The function also inserts single quotes around your fields, so that no longer has to be in the query code. Using the function, your insert code would now look like this:
<?php
if(isset($_POST["totalrows"])) {
$testconnect = mysql_pconnect("localhost", "myusername", "mypassword") or trigger_error(mysql_error(),E_USER_ERROR);
for($i=1; $i <= $_POST["totalrows"]; $i++) {
if(!isset($_POST["active$i"])) $_POST["active$i"] = 0;
$insertSQL = sprintf("INSERT mytable
(firstname, lastname, address, active)
VALUES
(%s,%s,%s,%d)",
GetSQLValueString($_POST["firstname$i"], "text"),
GetSQLValueString($_POST["lastname$i"], "text"),
GetSQLValueString($_POST["address$i"], "text"),
GetSQLValueString($_POST["active$i"], "int")
);
mysql_select_db("mydatabase", $testconnect);
$Result1 = mysql_query($insertSQL, $testconnect) or die(mysql_error());
}
}
?>
This article showed two ways to do a multiple insert. 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 methods requires an index field to be appended to each field on your page, but works well with all types of form fields. The second part of this series will show the same techniques used with update statements.