asked on

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

Jason C. Levine

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.

beanybag

ASKER

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

ASKER CERTIFIED SOLUTION

Jason C. Levine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

beanybag

ASKER

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

Jason C. Levine

>> 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.

beanybag

ASKER

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.

beanybag

ASKER

I had to find a proper solution elsewhere but I suppose I was given some sort of guidance.

Jason C. Levine

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...