[SOLVED] Updating multiple rows is not working!

[herschen]

Everytime I try to update 'picorder' in the 'propertiesimages' table, picorder gets converted to 1. I'm using PHP 5.2.4, MySQL 5.0.45, and the table is InnoDB. 'propertyid' in table 'propertiesimages' is a foreign key to 'propertyid' in 'properties' Here's the code that should work (I got this code at http://www.theblog.ca/?p=12:

 

Form:

<?php $hostname = "localhost";
$database = "properties";
$username = "properties";
$password = "xxxxxxx";

mysql_connect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);

$colname_rsImages = "-1";
if (isset($_GET['propertyid'])) {
  $colname_rsImages = $_GET['propertyid'];
  }
mysql_select_db($database);
$result = mysql_query("SELECT * FROM propertiesimages WHERE propertyid = '$colname_rsImages' ");

$i = 0;

print "<form name='update_image' method='post' action='shit_process.php'>\n";

while ($images = mysql_fetch_array($result)) {

print "<input type='hidden' name='id[$i]' value='{$images['propertyid']}' />";
print "<input type='hidden' name='oldpicorder[$i]' value='{$images['picorder']}' />";
print "<p>{$images['picorder']}: <input type='text' size='40' name='newpicorder[$i]' value='{$images['picorder']}' /></p>\n";

++$i;
}
print "<input type='submit' value='submit' />";
print "</form>";
mysql_close();
?>

 

Script:

<?php $hostname = "localhost";
$database = "properties";
$username = "properties";
$password = "xxxxxxx";

mysql_connect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database);
$size = count($_POST['newpicorder']);
$i = 0;
while ($i < $size) {
$newpicorder = $_POST['newpicorder'][$i];
$id = $_POST['id'][$i];
$oldpicorder = $_POST['oldpicorder'][$i];

$query = "UPDATE propertiesimages SET picorder ='$newpicorder' WHERE propertyid = '$id' AND picorder='$oldpicorder'";
mysql_query($query) or die ("Error in query: $query");
print "$oldpicorder" . " updated to" . " $newpicorder". "<br />";
++$i;
}
mysql_close();
?>

 

[attachment deleted by admin]

[darkfreaks]

<?php
$query = 'UPDATE propertiesimages SET picorder ='$newpicorder'
WHERE propertyid = '$id' AND picorder='$oldpicorder'';
mysql_query($query) or die ("Error in query: $query");?>

 

 

<?php

$result = mysql_query('SELECT * FROM propertiesimages 
WHERE propertyid = '$colname_rsImages' ');?>

[herschen]

That unfortunately did not work. I got this message when I replaced my code with your code: Parse error: syntax error, unexpected T_VARIABLE in C:\xampp\htdocs\RealEstate\properties\update_process.php on line 16

 

I believe you need to have double-quotes on the outside and single-quotes on the inside of the mysql_query. Thanks for your prompt response.

[axiom82]

Have you reviewed the html source of your scripts form output?

 

Have you reviewed a print_r() on the $_POST array?

 

You also have to account for when the order change occurs, shifting the entire pic order down a number if it is greater than the selected pic number.

[herschen]

You also have to account for when the order change occurs, shifting the entire pic order down a number if it is greater than the selected pic number.

Could you please clarify what you mean here?

 

I have updated the query, so I am now inserting an integer into the database and I have converted picorder to int. It still changes every instance of picorder (only where propertyid=what I specified) to '1' when I do the while loop, but if I delete the while loop (and just edit the first listing) I have no problem. What could be wrong with the loop? This did not work with a for loop either.

 

<?php function convertToInt($string) {
    $y = ltrim($string, '0');
    $z = 0 + $y;
    return $z;
}

mysql_select_db($database);
$size = count($_POST['newpicorder']);
$i = 0;

$id = $_POST['id'][$i];
$oldpicorder = $_POST['oldpicorder'][$i];
$newpicorder = convertToInt($_POST['newpicorder'][$i]);
print_r($newpicorder);
print "<br>";
$query = "UPDATE propertiesimages SET picorder ='$newpicorder'
WHERE propertyid = '$id' AND picorder='$oldpicorder'";

mysql_query($query) or die (mysql_error());
print "$oldpicorder" . " updated to" . " $newpicorder". "<br />";

mysql_close();
?>

[herschen]

I just ran the mysql query with the while loop, but I set it to only loop once ($i = 0;  while ($i <= 0) {). This worked fine, updating picorder on the first row fine. I also tried changing it to while $i <= 2...this worked fine once, updating the first 3 entries successfully.  Then I tried converting count($_POST['newpicorder'} to an integer with the convertToInt function and using the variable returned in the while loop. The resulting picorder entries were 4, 4, 4, 1, 2,1. Not what I wanted, but at least every instance of picorder is not 1.

 

I tried using '5' and back to '2' in the while loop, and this didn't work a second time...I don't know why. Finally, I set the while loop to loop once and it worked again with flying colors. I really don't have the foggiest idea what is going wrong.

[herschen]

*bump*

[herschen]

Does anyone have an idea of what might be the problem?

Supposedly this script has worked great for a bunch of people: http://www.theblog.ca/?p=12

[herschen]

*bump*

[herschen]

Does anybody know anything...please respond, just a simple hello would let me know that someone is looking at this post.