update multiple rows and fields. Simple?

horstuff

Hi. Im trying to create a form which will enable the user to update mutiple rows and fields, using checboxes to select and a submit button. The code below updates the field, but it always inserts the last row in the db. It inserts it in the selected row number (catalog), but it inserts the same data over an over (the last row in the db). Also, if more than one checkbox is selected, I get error Error in Query: >>>UPDATE catalog SET Name='Fred' , StreetAddress='502 yadda, City='somewhere' , Country='USA' WHERE catalog=(8,30)<<<. Error: You have an error in your SQL syntax near '30)' at line 1
(8,30) are the numbers/checkboxes I selected and the data (fred, etc.) is from the last row, and is not what I type in the text fields. Ideas? Thanks.

<html> 
<head> 
<title>Update rows in database table</title> 
</head> 

<body> 
<? 

//Config values 
$host         = "myhost"; //database host 
$user         = "myuser"; //database user 
$password     = "mypw"; //database password 
$database     = "mydb"; //name of database 
$table         = "catalog"; //name of table 

$columns = "catalog,Name,StreetAddress,City,Country"; 

$unique_column = "catalog"; 

$link_id = mysql_connect($host,$user,$password) or die("Cannot connect to database"); 
$db = mysql_select_db($database) or die("Cannot select database"); 

if(isset($HTTP_POST_VARS[update])) 
{ 
    if(count($update_id) > 0) 
    { 
        $update_rows = implode(",",$update_id); 

    $query = "UPDATE $table SET Name='$field[1]' , StreetAddress='$field[2]' , City='$field[3]' , Country='$field[4]' WHERE $unique_column=($update_rows)"; 
    $result = mysql_query($query) or die("Error in Query: >>>$query<<<. Error: " . mysql_error()); 

    $affected = mysql_affected_rows(); 
    $count = count($update_id); 
    $msg = "$affected / $count rows updated"; 
} 
else 
{ 
    $msg = "No rows selected for updating"; 
} 

} 

$i = explode(",",$columns); 
$num_columns = count($i); 

echo "<form method='post' action='$PHP_SELF'>\n"; 
echo "<table border='1' cellpadding='1' cellspacing='1'>\n"; 

if(isset($msg)) 
{ 
    echo "<tr><td align='center' colspan='" . ($num_columns+1) . "'><i>$msg</i></td></tr>\n"; 
} 

echo "<tr>\n"; 
echo "<td align='center'><b>Update?</b></td>\n"; 
for($x=0;$x<$num_columns;$x++) 
{ 
    echo "<td align='center'><b>" . $i[$x] . "</b></td>\n"; 
} 
echo "</tr>\n"; 

$query = "SELECT $columns FROM $table"; 
$result = mysql_query($query) or die("Error in Query >>>$query<<<. Error: " . mysql_error()); 
if($row = mysql_fetch_array($result))
{ 
    do{ 
        echo "<tr>\n"; 
        echo "<td><input type=checkbox name=update_id[] value='" . $row[$unique_column] . "'></td>\n"; 
        echo "<td>$row[$unique_column]</td>\n";
        for($x=1;$x<$num_columns;$x++) 
        {  

            echo "<td><input type=text name='field[$x]' size=20 value='$row[$x]'></td>\n"; 
        } 
        echo "</tr>\n"; 
    }while($row = mysql_fetch_array($result)); 

echo "<tr><td align='center' colspan='" . ($num_columns + 1) . "'><input type='submit' name='update' value='Update Selected Products'></td></tr>\n"; 
}  

echo "</table>\n"; 
echo "</form>\n"; 

?> 

</body> 

</html>

gottes_tod

simply try to correct your sql-query
there is a way like this: ...where catalog=8 or catalog=30 or catalog=...

UPDATE catalog SET Name='Fred' , StreetAddress='502 yadda, 
City='somewhere' , Country='USA' WHERE catalog=8 or 
catalog=30

gottes_tod

and another way

... where catalog in (1,2,3)

horstuff

Thanks, but how does that read the checkboxes that the user checks?

ahundiak

Never have seen it done quite this way before.
I think you are close but you need a few adjustments.

Start with the checkbox:
<input type=checkbox name=update_id[] value="id">
Is ok. When a checkbox array is posted only those checkboxes that are actually selected get posted.

So
$update_id = $_POST['update_id'];
foreach($update_id as $id) {
echo 'Record id to update ',$id;
}

So far so good. But for the Name column you have:
<input type="text" name="field[1]" value="xxx">

All you get back is one entry $field[1] which will hold the name of the last record in the list.

What you want to do is to generate
<input type="text" name=field1[id]" value="xxx">
Where id is the same number (or key) that's in your update_id array;

Now you have
$update_id = $POST['update_id'];
$field1 = $POST['field1']; // Name column
$field2 = $_POST['field2']; // StreetAddress column

foreach($update_id as $id) {
echo "Record $id $field1[$id] $field2[$id]";

// Update only one record at a time
// Sql does not let you do multiple records
// In one statement
}

Finally, unless you really have a reason for keeping things generic then you might want to just use the column names instead of field1,field2 etc.

I just noticed that this was cross posted. Dont do that. It's annoying to try and help someone only to realize that another thread has already covered the same issues. I can't speak for other memebers but I use the "view recent posts" command to view posts regardless of which forum they are in.

horstuff

Sorry.
The code on the two posts was actually a bit different, but it was pretty much a cross post, I guess. Won't happen again.

Your input helped a great deal. Thanks very much.