value inserted into MySQL is lost

[herschen]

Everything is getting passed to the database fine except for 'new_picorder' which, when printed to the screen the numbers are correct, but it is passed to the database as '1.'

 

So here's the deal: I have four form fields that I am passing from my form to this script: total_images (the total amount of images being updated), propertyid (the property for which the images are being updated), old_picorder (the old order in which these images would be displayed), and new_picorder (the new order).

 

In the form, I have a do...while loop that makes the form repeat itself for the amount of images in the database where the id = 'propertyid.' The whole point of this form is to update the order in which the images are displayed on my Properties web page.

 

In my scripts, the first for loop makes sure there are no duplicate numbers in new_picorder and the second loop should insert these new numbers into the picorder column of my database (only for the specified propertyid).

 

Obviously the first two numbers are being inserted correctly because:

 

a) the 'propertyid' that I am testing (36) is the only one that gets modified in the database. If this form field reverted to 1 when being inserted into the database, 'propertyid' 1 would have been modified.

 

b) all the values in the database with 'propertyid' 36 get updated, not just the first one. If 'old_picorder' reverted to 1 when being inserted into the database, it would modify the row with the 'picorder' 1 (and only this column)

 

c) I did a test and changed the syntax of my query so it was $updateSQL = "UPDATE propertiesimages SET picorder='$old_picorder' WHERE propertyid='$propertyid' AND picorder='$old_picorder'"; . It inserted the value 'old_picorder' flawlessly

 

I tried converting the column 'picorder' to a VARCHAR and still had the same problem.

 

Here's the syntax of my form:

<?php require_once('../Connections/connProperties.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;
    }
}

$colname_rsImages = "-1";
if (isset($_GET['propertyid'])) {
    $colname_rsImages = $_GET['propertyid'];
}
mysql_select_db($database_connProperties, $connProperties);
$query_rsImages = sprintf("SELECT * FROM propertiesimages WHERE propertyid = %s ORDER BY picorder", GetSQLValueString($colname_rsImages, "int"));
$rsImages = mysql_query($query_rsImages, $connProperties) or die(mysql_error());
$row_rsImages = mysql_fetch_assoc($rsImages);
$totalRows_rsImages = mysql_num_rows($rsImages);



?>

<form method="post" action="update_images_process.php" name="update_image" method="post">
                 <table width="100%" border="0">
                     <tr>
                         <td width="35%"> </td>
                         <td width="65%">Order to view images (first 5 are the main images):</td>
                      </tr> </table>
              <?php do { ?>
                    <table width="100%" border="0">
                      <tr>
                          <td width="35%"><img src="<?php echo $row_rsImages['path']; ?>" width="200" height="150" /></td>
                           <td width="65%"><label>
                               <?php echo "<input name=\"new_picorder" . $row_rsImages['picorder'] . "\" type=\"text\" id=\"new_picorder" . $row_rsImages['picorder'] . "\" size=\"2\" />"?>
                               <?php echo "<input name=\"old_picorder". $row_rsImages['picorder'] . "\" type=\"hidden\" id=\"old_picorder". $row_rsImages['picorder'] . "\" value=\"". $row_rsImages['picorder']."\">" ?>
                                      
                                    </label><?php echo $row_rsImages['picorder'];
                                    $total_images = $row_rsImages['picorder'];?></td>
                                  </tr>
                                </table>
                                <?php } while ($row_rsImages = mysql_fetch_assoc($rsImages)); ?>
                                <?php echo "<input name=\"total_images\" type=\"hidden\" id=\"total_images\" value=\"$total_images\">
                                <input name=\"propertyid\" type=\"hidden\" id=\"propertyid\" value=\"$colname_rsImages\">
                                <input type=\"submit\" name=\"update_images_now\" value=\"Update Now!\">" ?>
                                </form>

 

And here's the syntax of my script:

<?php require_once('../Connections/connProperties.php'); ?>
<?php

$num_files = $_POST['total_images'];
   $error = 0;

for ($i = 1; $i <= $num_files; $i++) {
    for ($num = 1; $num <= $num_files; $num++) {
        if ($_POST['new_picorder'.$num] == $_POST['new_picorder'.$i]) {
            $error++;
        }
    }
} 

  for ($i = 1; $i <= $num_files; $i++) {
       if ($error == $num_files) {
           $new_picorder = $_POST['new_picorder'.$i];
           $propertyid = $_POST['propertyid'];
           $old_picorder = $_POST['old_picorder'.$i];
                mysql_select_db($database_connProperties, $connProperties);
                $updateSQL = "UPDATE propertiesimages SET picorder='$new_picorder' WHERE propertyid='$propertyid' AND picorder='$old_picorder'";
                 
                       
                       
                $Result1 = mysql_query($updateSQL, $connProperties) or die(mysql_error()); 
       } else {
           echo "You cannot have images with the same order number. Please click the back button and adjust";
          break;
       }
   }    

?>

[herschen]

Sorry I didn't post this information earlier. I also have more information about my problem. I am using PHP 5.2.4, MySQL 5.0.45, and Apache HTTPD 2.2.6 9 (with XAMPP 1.64).  I have simplified my script code, so it no longer checks for duplicate entries; the database allows this and this is only for testing purposes. I have my 'propertiesimages' database setup with InnoDB and 'propertyid' is a foreign key to 'propertyid' in the 'properties' database. The column 'picorder' dictates in which order the images will display on the web page. I've been working on a page that allows me to update the order in which these images are displayed. When I create a simple update form, using Dreamweaver's Update Wizard, and it only updates 'picorder' where 'propertyid' = the propertyid passed in the form. This works fine, albeit it just updates every instance of 'picorder' to the number I choose (which isn't very helpful--every picture can't have the number 5).

 

So what I've done is created a do...while loop in the form section, so that a new form text field is created for every image that is listed with that 'propertyid' (with a name 'picorder1', 'picorder2', 'picorder3' ... where 1,2,3 is the original picorder). I also pass a hidden field with the total number of pictures, and I pass the original 'picorder' as a hidden field (old_picorder1, 2, 3, etc.)

 

The php script has a for loop that queries the database and updates 'picorder' with the new number where 'picorder' = old_picorder1 (or 2,3,4) and propertyid = propertyid from the form. Everytime I execute this, every instance of 'picorder' with the 'propertyid' from the form gets the value '1.'  Please somebody help...I've been trying to figure this out for days. I've posted below the code. Also, please note that I did not change the number 4 in the form and when I ran the query, every other instance of 'picorder' changed to '1' whereas 4 remained the same.

 

Code that works:

 

 

//Form page
<form action="Untitled-2_process.php" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">Picorder:</td>
<td><input type="text" name="picorder" value="<?php echo htmlentities($row_Recordset1['picorder'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Update record" /></td>
</tr>
</table>
<input type="hidden" name="propertyid" value="<?php echo $colname_Recordset1; ?>" />
</form>

//Script
$updateSQL = sprintf("UPDATE propertiesimages SET picorder=%s WHERE propertyid=%s",
GetSQLValueString($_POST['picorder'], "text"),
GetSQLValueString($_POST['propertyid'], "int"));
//GetSQLValueString is just a Dreamweaver function (I've inputted these values directly, too)

mysql_select_db($database_connProperties, $connProperties);
$Result1 = mysql_query($updateSQL, $connProperties) or die(mysql_error()); 

 

Here's the code that doesn't work:

 

//Form Page
<form action="Untitled-2_process.php" method="post" name="form1" id="form1">
<table align="center">
<?php do { ?> <tr valign="baseline">
<td nowrap="nowrap" align="right">Picorder:</td>
<td><input type="text" name="<?php echo "picorder" . htmlentities($row_Recordset1['picorder'], ENT_COMPAT, 'utf-8'); ?>" value="<?php echo htmlentities($row_Recordset1['picorder'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
<input type="hidden" name="<?php echo "old_picorder" . htmlentities($row_Recordset1['picorder'], ENT_COMPAT, 'utf-8'); ?>" value="<?php echo htmlentities($row_Recordset1['picorder'], ENT_COMPAT, 'utf-8'); ?>" />
<?php $total_images = $row_Recordset1['picorder'];?>
</tr> <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Update record" /></td>
</tr>
</table>
<input type="hidden" name="total_images" value="<?php echo $total_images; ?>" />
<input type="hidden" name="propertyid" value="<?php echo $colname_Recordset1; ?>" />
</form>

//Script
$total_images = $_POST['total_images'];
for ($i = 1; $i <= $total_images; $i++) {
$updateSQL = sprintf("UPDATE propertiesimages SET picorder=%s WHERE propertyid=%s AND picorder=%s",
GetSQLValueString($_POST['picorder'.$i], "text"),
GetSQLValueString($_POST['propertyid'], "int"),
GetSQLValueString($_POST['old_picorder'.$i], "text"));

mysql_select_db($database_connProperties, $connProperties);
$Result1 = mysql_query($updateSQL, $connProperties) or die(mysql_error());
}

[herschen]

*bump*

[Daukan]

This doesn't look right

$_POST['picorder'.$i]

Maybe this?

$_POST['picorder'][$i]