Skip to main content
Drymetal
Drymetal
Inspiring
May 31, 2011
Answered

Update Record skips every other row of fields?

  • May 31, 2011
  • 1 reply
  • 1770 views

     I am having a slight problem in trying to figure out a way to create a form and have it update multiple rows in my database that are under the same table.  When I think this through...I get that sensation you get when you contemplate a never-ending Universe or an always-existing-God.

Quickly, the issue is in the fact that when I fill out the form I built - it only updates every other row in the database.  In much more depth, here is what I have done:

1. I created a form on a blank page.

2. I hit enter three times for vertical space within the form.

3. I inserted nine text fields:  (Each field corresponding to fields in the database.  Being Employee ID, Employee Name, Days of the week for scheduling.)

  • id
  • employee
  • mon
  • tue
  • wed
  • thur
  • fri
  • sat
  • sun

4. I created a Recordset that consisted of all the appropiate information from the database.

5. I clicked on Bindings and then dragged each item (id, employee, etc) to its corresponding text field in the form.  This way, it would automatically populate it.

6. I put a submit button.

7. I went to Server Behaviors and chose Update Record.

Now, at this point in time I knew two things:

1. It would pull the first employee in the database and display the information inside the text fields.

2. It would work.

And it did work.  It showed the correct information and then updated the database correctly when I hit submit.

8. So then I selected one of the fields and clicked the < tr > tag at the bottom of the window to select the whole row.

9. I went to Server Behaviors and chose Repeat Region, chose ALL and hit okay.

10. I prayed, danced around my cat and ate some carrots in preperation of seeing if it works or not.

It did not.

Not entirely.

So now, I can have three employees in the database, or fifty.  It doesn't matter.   If I go through and fill out the schedule and hit submit, it will only update every other employee.  See screenshot below:

I have no idea why.  Even shaving my cat didn't help any.  Any ideas?  Suggestions?  Solutions?

Below is the code from my page.  I have no idea why the head stuff is in the middle of the page.

----------------------------------------------------------------------------------------------------------------------------------------------------------

<?php require_once('Connections/check_emp.php'); ?>
<?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 employees SET employee_name=%s, Monday=%s, Tuesday=%s, Wednesday=%s, Thursday=%s, Friday=%s, Saturday=%s, Sunday=%s WHERE id=%s",
                       GetSQLValueString($_POST['employee_name'], "text"),
                       GetSQLValueString($_POST['Monday'], "text"),
                       GetSQLValueString($_POST['Tuesday'], "text"),
                       GetSQLValueString($_POST['Wednesday'], "text"),
                       GetSQLValueString($_POST['Thursday'], "text"),
                       GetSQLValueString($_POST['Friday'], "text"),
                       GetSQLValueString($_POST['Saturday'], "text"),
                       GetSQLValueString($_POST['Sunday'], "text"),
                       GetSQLValueString($_POST['id'], "int"));

  mysql_select_db($database_check_emp, $check_emp);
  $Result1 = mysql_query($updateSQL, $check_emp) or die(mysql_error());
}

mysql_select_db($database_check_emp, $check_emp);
$query_getSchedule = "SELECT id, employee_name, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM employees ORDER BY id ASC";
$getSchedule = mysql_query($query_getSchedule, $check_emp) or die(mysql_error());
$row_getSchedule = mysql_fetch_assoc($getSchedule);
$totalRows_getSchedule = mysql_num_rows($getSchedule);
?>
<!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>
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <table width="900">
    <tr>
      <td>id</td>
      <td>emp</td>
      <td>mon</td>
      <td>tue</td>
      <td>wed</td>
      <td>thur</td>
      <td>fri</td>
      <td>sat</td>
      <td>sun</td>
    </tr>
    <?php do { ?>
      <tr>
        <td><input name="id" type="text" disabled="disabled" id="id" value="<?php echo $row_getSchedule['id']; ?>" readonly="readonly" /></td>
        <td><input name="employee_name" type="text" disabled="disabled" id="employee_name" value="<?php echo $row_getSchedule['employee_name']; ?>" readonly="readonly" /></td>
        <td><input name="Monday" type="text" id="Monday" value="<?php echo $row_getSchedule['Monday']; ?>" /></td>
        <td><input name="Tuesday" type="text" id="Tuesday" value="<?php echo $row_getSchedule['Tuesday']; ?>" /></td>
        <td><input name="Wednesday" type="text" id="Wednesday" value="<?php echo $row_getSchedule['Wednesday']; ?>" /></td>
        <td><input name="Thursday" type="text" id="Thursday" value="<?php echo $row_getSchedule['Thursday']; ?>" /></td>
        <td><input name="Friday" type="text" id="Friday" value="<?php echo $row_getSchedule['Friday']; ?>" /></td>
        <td><input name="Saturday" type="text" id="Saturday" value="<?php echo $row_getSchedule['Saturday']; ?>" /></td>
        <td><input name="Sunday" type="text" id="Sunday" value="<?php echo $row_getSchedule['Sunday']; ?>" /></td>
      </tr>
      <?php } while ($row_getSchedule = mysql_fetch_assoc($getSchedule)); ?>
  </table>
  <p> </p>
  <p>
    <input type="submit" name="submit" id="submit" value="Submit" />
  </p>
  <p> </p>
  <input type="hidden" name="MM_update" value="form1" />
</form>
<p><a href="admin/add_employee.php">Add Employee</a></p>
</body>
</html>
<?php
mysql_free_result($getSchedule);
?>

 
This topic has been closed for replies.
Correct answer David_Powers
This is what the amended code should look like:
<?php require_once('Connections/check_emp.php'); ?>
<?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")) {
  // find the number of employees
  $count = count($_POST['id']);
  // if a positive number is returned, proceed with update
  if ($count) {
       // select the database
       mysql_select_db($database_check_emp, $check_emp);
        // loop through the subarrays to insert the values
       for ($i = 0; $i < $count; $i++) {
            // use $i to access the correct element in each subarray
          $updateSQL = sprintf("UPDATE employees SET employee_name=%s, Monday=%s, Tuesday=%s, Wednesday=%s, Thursday=%s, Friday=%s, Saturday=%s, Sunday=%s WHERE id=%s",
                       GetSQLValueString($_POST['employee_name'][$i], "text"),
                       GetSQLValueString($_POST['Monday'][$i], "text"),
                       GetSQLValueString($_POST['Tuesday'][$i], "text"),
                       GetSQLValueString($_POST['Wednesday'][$i], "text"),
                       GetSQLValueString($_POST['Thursday'][$i], "text"),
                       GetSQLValueString($_POST['Friday'][$i], "text"),
                       GetSQLValueString($_POST['Saturday'][$i], "text"),
                       GetSQLValueString($_POST['Sunday'][$i], "text"),
                       GetSQLValueString($_POST['id'][$i], "int"));


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

mysql_select_db($database_check_emp, $check_emp);
$query_getSchedule = "SELECT id, employee_name, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM employees ORDER BY id ASC";
$getSchedule = mysql_query($query_getSchedule, $check_emp) or die(mysql_error());
$row_getSchedule = mysql_fetch_assoc($getSchedule);
$totalRows_getSchedule = mysql_num_rows($getSchedule);
?>
<!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>
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <table width="900">
    <tr>
      <td>id</td>
      <td>emp</td>
      <td>mon</td>
      <td>tue</td>
      <td>wed</td>
      <td>thur</td>
      <td>fri</td>
      <td>sat</td>
      <td>sun</td>
    </tr>
    <?php do { ?>
      <tr>
        <td><input name="id[]" type="text" value="<?php echo $row_getSchedule['id']; ?>" readonly="readonly" /></td>
        <td><input name="employee_name[]" type="text" value="<?php echo $row_getSchedule['employee_name']; ?>" readonly="readonly" /></td>
        <td><input name="Monday[]" type="text" value="<?php echo $row_getSchedule['Monday']; ?>" /></td>
        <td><input name="Tuesday[]" type="text" value="<?php echo $row_getSchedule['Tuesday']; ?>" /></td>
        <td><input name="Wednesday[]" type="text" value="<?php echo $row_getSchedule['Wednesday']; ?>" /></td>
        <td><input name="Thursday[]" type="text" value="<?php echo $row_getSchedule['Thursday']; ?>" /></td>
        <td><input name="Friday[]" type="text" value="<?php echo $row_getSchedule['Friday']; ?>" /></td>
        <td><input name="Saturday[]" type="text" value="<?php echo $row_getSchedule['Saturday']; ?>" /></td>
        <td><input name="Sunday[]" type="text" value="<?php echo $row_getSchedule['Sunday']; ?>" /></td>
      </tr>
      <?php } while ($row_getSchedule = mysql_fetch_assoc($getSchedule)); ?>
  </table>
  <p> </p>
  <p>
    <input type="submit" name="submit" id="submit" value="Submit" />
  </p>
  <p> </p>
  <input type="hidden" name="MM_update" value="form1" />
</form>
<p><a href="admin/add_employee.php">Add Employee</a></p>
</body>
</html>
<?php
mysql_free_result($getSchedule);
?>
Making these changes will result in Dreamweaver no longer recognizing the Update Record server behavior. However, if you want to use server behaviors, you frequently need to customize them to achieve anything beyond the simplest of tasks. It's also unwise to use server-side code without understanding what it does. Time to start learning, rather than relying on incantations and abusing your cat.
1 reply
David_Powers
David_Powers
Inspiring
May 31, 2011
Drymetal wrote:
I have no idea why.  Even shaving my cat didn't help any.  Any ideas?  Suggestions?  Solutions?
Cats don't like being shaved and tend to take their revenge by embedding sharp claws in sensitive parts of your body. A far better solution is to learn the technology you're trying to use.
The server behaviors in Dreamweaver are intended for quick prototyping and/or as a learning tool. They are not recommended for use in a production situation.
There are several problems with your page.
First of all, you are using IDs in a loop (repeat region). This results in the same ID being inserted in the page multiple times. An ID should be used only once in a page.
Secondly, the Update Record server behavior is capable of updating only one record. It cannot be used to update multiple records without being customized.
Thirdly, to submit multiple values from form fields that share the same name, you need to submit them as an array. In PHP, you do so by adding an empty pair of square brackets after the field name like this:
name="employee_name[]"
Finally, the ID field in your form is disabled. That means the value won't be submitted with the form. Instead of disabled, you should be using readonly.
I need to disappear for a short while, but I'll post the correct code for you a bit later.
David_Powers
David_PowersCorrect answer
Inspiring
May 31, 2011
This is what the amended code should look like:
<?php require_once('Connections/check_emp.php'); ?>
<?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")) {
  // find the number of employees
  $count = count($_POST['id']);
  // if a positive number is returned, proceed with update
  if ($count) {
       // select the database
       mysql_select_db($database_check_emp, $check_emp);
        // loop through the subarrays to insert the values
       for ($i = 0; $i < $count; $i++) {
            // use $i to access the correct element in each subarray
          $updateSQL = sprintf("UPDATE employees SET employee_name=%s, Monday=%s, Tuesday=%s, Wednesday=%s, Thursday=%s, Friday=%s, Saturday=%s, Sunday=%s WHERE id=%s",
                       GetSQLValueString($_POST['employee_name'][$i], "text"),
                       GetSQLValueString($_POST['Monday'][$i], "text"),
                       GetSQLValueString($_POST['Tuesday'][$i], "text"),
                       GetSQLValueString($_POST['Wednesday'][$i], "text"),
                       GetSQLValueString($_POST['Thursday'][$i], "text"),
                       GetSQLValueString($_POST['Friday'][$i], "text"),
                       GetSQLValueString($_POST['Saturday'][$i], "text"),
                       GetSQLValueString($_POST['Sunday'][$i], "text"),
                       GetSQLValueString($_POST['id'][$i], "int"));


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

mysql_select_db($database_check_emp, $check_emp);
$query_getSchedule = "SELECT id, employee_name, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM employees ORDER BY id ASC";
$getSchedule = mysql_query($query_getSchedule, $check_emp) or die(mysql_error());
$row_getSchedule = mysql_fetch_assoc($getSchedule);
$totalRows_getSchedule = mysql_num_rows($getSchedule);
?>
<!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>
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <table width="900">
    <tr>
      <td>id</td>
      <td>emp</td>
      <td>mon</td>
      <td>tue</td>
      <td>wed</td>
      <td>thur</td>
      <td>fri</td>
      <td>sat</td>
      <td>sun</td>
    </tr>
    <?php do { ?>
      <tr>
        <td><input name="id[]" type="text" value="<?php echo $row_getSchedule['id']; ?>" readonly="readonly" /></td>
        <td><input name="employee_name[]" type="text" value="<?php echo $row_getSchedule['employee_name']; ?>" readonly="readonly" /></td>
        <td><input name="Monday[]" type="text" value="<?php echo $row_getSchedule['Monday']; ?>" /></td>
        <td><input name="Tuesday[]" type="text" value="<?php echo $row_getSchedule['Tuesday']; ?>" /></td>
        <td><input name="Wednesday[]" type="text" value="<?php echo $row_getSchedule['Wednesday']; ?>" /></td>
        <td><input name="Thursday[]" type="text" value="<?php echo $row_getSchedule['Thursday']; ?>" /></td>
        <td><input name="Friday[]" type="text" value="<?php echo $row_getSchedule['Friday']; ?>" /></td>
        <td><input name="Saturday[]" type="text" value="<?php echo $row_getSchedule['Saturday']; ?>" /></td>
        <td><input name="Sunday[]" type="text" value="<?php echo $row_getSchedule['Sunday']; ?>" /></td>
      </tr>
      <?php } while ($row_getSchedule = mysql_fetch_assoc($getSchedule)); ?>
  </table>
  <p> </p>
  <p>
    <input type="submit" name="submit" id="submit" value="Submit" />
  </p>
  <p> </p>
  <input type="hidden" name="MM_update" value="form1" />
</form>
<p><a href="admin/add_employee.php">Add Employee</a></p>
</body>
</html>
<?php
mysql_free_result($getSchedule);
?>
Making these changes will result in Dreamweaver no longer recognizing the Update Record server behavior. However, if you want to use server behaviors, you frequently need to customize them to achieve anything beyond the simplest of tasks. It's also unwise to use server-side code without understanding what it does. Time to start learning, rather than relying on incantations and abusing your cat.
David_Powers
David_Powers
Inspiring
June 1, 2011
Unrealistic or not, at least I did take your advice and spent an entire day and night reading about php, which should -at the very least- show that I do have an interest in learning.  Is the sharing of knowledge only quantifiable so long as it is serious and confounded to the known barriers of reality?
"Children want to learn to the degree that they are unable to distinguish learning from fun. They keep this attitude until we adults convince them that learning is not fun."
-- Glenn Doman, founder of The Institutes for the Achievement of Human Potential 
Either way, I do appreciate your help.  You providing code that did work inspired me.  To just look at code and find errors and know immediately what is wrong is pretty awesome and it makes the whole coding thing seem less intimidating.  Thank you.

Drymetal wrote:
Unrealistic or not, at least I did take your advice and spent an entire day and night reading about php, which should -at the very least- show that I do have an interest in learning.  Is the sharing of knowledge only quantifiable so long as it is serious and confounded to the known barriers of reality?
There's absolutely nothing wrong with experimenting in the way that you did. In fact, it's very gratifying that you took to heart the message about the need to understand the code you're working with.
However, programmatically unravelling the complex array that you created would take some time and thought. If it were a real example, the advice from most experienced programmers would be to restructure your data rather than attempt to handle such complexity.

        
                                                

        

    


            

                                        

            
                        

                                                                                                            
            




        

                    

    
    





    


                                        

                                                        

            
        
    






                                    
Terms & ConditionsAccessibility statement

            
        



        
            
    
        
    
        



        
        
        


        


        
        
        
        
                                                        

                            
    

                    

  

    

      
        
      
    

    

      
        Using the community
      
      
        Terms of use
      
      
        Privacy
      
      
        Cookie preferences
      
      
        Do not sell or share my personal information
      
      
        ad choicesAdChoices