Using Dreamweaver PHP, MySQL DB bindings, how do I update multiple database entries?

I am trying to update multiple rows on a MySQL data using the PHP bindings in Dreamweaver CS4.

Currently I am using the built-in Dreamweaver functions to extract multiple rows within a single table on a MySQL database.

In this case, each row is a guest and each group of guests is grouped by a "familyid".

I would then like the user to update the details for these guest in a single form with a single click. (Note attached screenshot).

The additional forms below the guests should be updated in each one of the guest field entries.

The question is, how do I go about modifying the Dreamweaver generated code to update all the entries in a single form.
<?php require_once('../Connections/wedding_db.php'); ?>
<?php require_once('../Connections/wedding_db.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "guest_login.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0) 
  $MM_referrer .= "?" . $QUERY_STRING;
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  $username_temp = $_SESSION['MM_Username'];
  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;
}
}

$maxRows_rsvp = 10;
$pageNum_rsvp = 0;
if (isset($_GET['pageNum_rsvp'])) {
  $pageNum_rsvp = $_GET['pageNum_rsvp'];
}
$startRow_rsvp = $pageNum_rsvp * $maxRows_rsvp;

$colname_rsvp = "-1";
if (isset($_SESSION['username_select'])) {
  $colname_rsvp = $_SESSION['username_select'];
}
mysql_select_db($database_wedding_db, $wedding_db);
$query_rsvp = sprintf("SELECT guestid, familyid, forename, surname, username, rsvp, menu, food_comments, hotel, hotel_comments FROM guests WHERE username = %s", GetSQLValueString($colname_rsvp, "text"));
$query_limit_rsvp = sprintf("%s LIMIT %d, %d", $query_rsvp, $startRow_rsvp, $maxRows_rsvp);
$rsvp = mysql_query($query_limit_rsvp, $wedding_db) or die(mysql_error());
$row_rsvp = mysql_fetch_assoc($rsvp);

if (isset($_GET['totalRows_rsvp'])) {
  $totalRows_rsvp = $_GET['totalRows_rsvp'];
} else {
  $all_rsvp = mysql_query($query_rsvp);
  $totalRows_rsvp = mysql_num_rows($all_rsvp);
}
$totalPages_rsvp = ceil($totalRows_rsvp/$maxRows_rsvp)-1;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<p>Please let us know if you are able to attend our wedding by either RSVP'ing with your invitation by post or more simply with the form below. You can change your RSVP using this form up until the .</p>
<p>When confirming, please select your meal preference for each person.</p>
<form id="form1" name="form1" method="post" action="">
  <table width="600" border="0" align="center">
    <tr>
      <td>&nbsp;</td>
      <td align="center">Please select if you will attend</td>
      <td align="center">&nbsp;</td>
    </tr>
    <?php do { ?>
      <tr>
        <td><?php echo $row_rsvp['forename']; ?> <?php echo $row_rsvp['surname']; ?></td>
        <td width="190" align="center"><label>
          <input <?php if (!(strcmp($row_rsvp['rsvp'],1))) {echo "checked=\"checked\"";} ?> type="checkbox" name="rsvp" id="rsvp" />
          <input name="guestid" type="hidden" id="guestid" value="<?php echo $row_rsvp['guestid']; ?>" />
        </label></td>
        <td width="188" align="center"><label>
          <select name="food" id="food">
          <?php if($row_rsvp['menu'] == NULL) { ?>
            <option selected="selected">Select menu choice</option>
            <option value="lamb">Lamb Menu</option>
            <option value="fish">Fish Menu</option>
            <option value="vegetarian">Vegetarian Menu</option>
          <?php } elseif($row_rsvp['menu'] = "lamb") { ?>
            <option>Select menu choice</option>
            <option value="lamb">Lamb Menu</option>
            <option value="fish">Fish Menu</option>
            <option value="vegetarian">Vegetarian Menu</option>
          <?php } elseif($row_rsvp['menu'] = "fish") { ?>
            <option>Select menu choice</option>
            <option value="lamb">Lamb Menu</option>
            <option value="fish">Fish Menu</option>
            <option value="vegetarian">Vegetarian Menu</option>
          <?php } elseif($row_rsvp['menu'] = "vegetarian") { ?>
            <option>Select menu choice</option>
            <option value="lamb">Lamb Menu</option>
            <option value="fish">Fish Menu</option>
            <option value="vegetarian">Vegetarian Menu</option>
		  <?php } ?>            
</select>
        </label></td>
      </tr>
      <?php } while ($row_rsvp = mysql_fetch_assoc($rsvp)); ?>
  </table>
  <p>If you have any special dietary requirements, please let us know so we can inform the hotel chef.</p>
  <table width="500" border="0" align="center">
    <tr>
      <td align="center"><label>
        <textarea name="food_comments" id="food_comments" cols="45" rows="3"><?php echo $row_rsvp['food_comments']; ?></textarea>
      </label></td>
    </tr>
  </table>
  <p>Although it's not necessary, please let us know which hotel you plan to stay at. Remember we have special rates for all wedding guests at the Hotel where the wedding will take place so if you require a room, please download the booking form and send it back to the hotel as soon as possible.</p>
  <table width="500" border="0" align="center">
    <tr>
      <td align="center"><label>
        <select name="hotel" id="hotel">
          <?php if($row_rsvp['hotel'] == NULL) { ?>
          <option selected="selected" value="">Please select your hotel</option>
          <option value="hotel">Hotel</option>          
          <option value="other">Other (Please indicate which below)</option>
          <?php } elseif($row_rsvp['hotel'] = "kempinski") { ?>
          <option value="">Please select your hotel</option>
          <option value="hotel">Hotel</option>          
          <option value="other">Other (Please indicate which below)</option>
          <?php } elseif($row_rsvp['hotel'] = "other") { ?>
          <option value="">Please select your hotel</option>
          <option value="hotel">Hotel</option>          
          <option value="other">Other (Please indicate which below)</option>
          <?php } ?>
</select>
      </label></td>
    </tr>
    <tr>
      <td align="center"><br />If other, please provide details:</td>
    </tr>
    <tr>
      <td align="center"><label>
        <textarea name="hotel_comment" cols="45" rows="3" id="hotel_comment"><?php echo $row_rsvp['hotel_comments']; ?></textarea>
      </label></td>
    </tr>
  </table>
  <input name="familyid" type="hidden" id="familyid" value="<?php echo $row_rsvp['familyid']; ?>" />
  <br />
  <table width="500" border="0" align="center">
    <tr>
      <td align="center"><input type="submit" name="submit" id="submit" value="RSVP and Update Details" /></td>
    </tr>
  </table>
</form>
</body>
</html>
<?php
mysql_free_result($rsvp);
?>

Open in new window

05-04-2010-18-31-53.png
beanybagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason C. LevineNo oneCommented:
Hi beanybag,

Unfortunately, Dreamweaver's built-in behaviors can't update multiple rows from one form.  Your choices in this situation are:

1) Learn how to do it by hand-coding PHP.  If you have some basic PHP knowledge, this is the cheapest option.

2) Buy a third-party extension to Dreamweaver that allows you to do multiple updates from the single form

3) Forget about the multiple update and have each row contain a separate form and allow the users to update one at a time only.
0
beanybagAuthor Commented:
Not good!

Regarding the first option, I've given it a go already without luck. Since I have to join two tables together and update them, would it make any difference with regards to how the update is done? My SQL view is as follows in the first code snippet.

I then have to adapt the following MySQL function in PHP to try and update the fields. I've started but I'm getting pretty stuck! The next code snippet shows what I've already tried.

<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE guests SET rsvp='$rsvp[$i]', menu='$menu[$i]' food_comments='$food_comments' hotel='$hotel' hotel_comments='$hotel_comments' WHERE guestid='$guestid[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:section_rsvp.php");
}
mysql_close();
?>

I'm using the original Dreamweaver query to work this one. Still needs a lot of work to be done!

Regarding the 2nd option, do you know any plugins that may be available?


select `guests`.`guestid` AS `guestid`,`guests`.`familyid` AS `familyid`,`guests`.`forename` AS `forename`,`guests`.`surname` AS `surname`,`guests`.`username` AS `username`,`guests`.`rsvp` AS `rsvp`,`guests`.`menu` AS `menu`,`hotel`.`hotel` AS `hotel`,`hotel`.`hotel_comments` AS `hotel_comments`,`hotel`.`food_comments` AS `food_comments` 
from (`guests` join `hotel` on((`guests`.`familyid` = `hotel`.`familyid`)))

Open in new window

0
Jason C. LevineNo oneCommented:
>> Since I have to join two tables together and update them, would it make any difference
>> with regards to how the update is done?

How you do the SELECT isn't that relevant so long as the Primary Key of the table you want to update is present.  

Your PHP code does not give me enough information to really help.  What you need to do is have the fields repeating inside the repeat region of the form but set up as array fields.  You initialize a counter variable outside of the repeat and update it before the while statement.

<?php
$i=0;
?>
<form... >
<?php do {
echo "<input name='something[$i]'>";
echo "<input name='primarykey[$i]' value='$row_rsYourRecordset[\"primarykeycolumn\"]'>";
$i++
} while (conditions)
?>
<input name="submit" type="submit"

That will submit all fields named "something" into an array:

something[0], something[1], something[2], etc.

and associate the primary key in an array:

primarykey[0],primarykey[2],primarykey[2], etc.

You then use PHP to step through the array and run an update statement for each name/value pair in the array. Here's a tutorial about it:

http://www.theblog.ca/update-multiple-rows-mysql

>> Regarding the 2nd option, do you know any plugins that may be available?

Yes.  WebAssist's DataAssist will do this:

http://www.webassist.com/dreamweaver-extensions/dataassist/

It essentially replaces the built-in DW behaviors, including the Repeat Region behavior.  You generate the recordset, use DataAssist to generate the Repeat region, and then use DataAssist's Multiple Record Update behavior to generate the PHP needed to update everything at once.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

beanybagAuthor Commented:
Hey jason1178,

Thanks for the feedback. What more information do you need to help you solve the problem?

I can provide you with a table map of my DB and the full page code if necessary.....

I've tried doing what you mentioned but I'll be honest in saying I'm quite lost as this is new to me.

Thanks,

beanybag
0
Jason C. LevineNo oneCommented:
>> I'll be honest in saying I'm quite lost as this is new to me.

Then I recommend you buy DataAssist or engage a developer for the hour or two it would take you to write the code.  I'm going to be traveling this week and next and I can't rewrite your code for you.   If you want to take the time and work through the tutorial I posted and ask specific questions as you get stuck, I'll respond as best I can.
0
beanybagAuthor Commented:
To be honest, the solution did not provide me with an answer and I had to look elsewhere for a solution.

Thank you for trying.
0
beanybagAuthor Commented:
I had to find a proper solution elsewhere but I suppose I was given some sort of guidance.
0
Jason C. LevineNo oneCommented:
I gave you a link to a tutorial and an extension, either of which would have solved your problem.  Given that I am an unpaid volunteer and had to travel and couldn't do the coding for you, I'm not sure what else I could have done...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.