update multiple fields and rows

horstuff · Jan 9, 2003

Hi. I am trying to create a form that can update mutiple fields in a mysql database. I need it to be able to also update mutiple rows of the same field. The code below doesn't update, it actaually delets all data from the 4 rows. Any ideas? Thanks.

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

<body> 
<? 

$host         = "myhost"; //database host 
$user         = "myusername"; //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='$Name' , StreetAddress='$StreetAddress' , City='$City' , Country='$Country' 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=$fname 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>

statrat · Jan 9, 2003

should this code

echo "<td><input type=text name=$fname size=20 value=$row[$x]></td>\n";

be instead:

echo "<td><input type=text name=$i[x] size=20 value=$row[$x]></td>\n";

horstuff · Jan 9, 2003

Thanks, but that didn't do it. Any other ideas?

horstuff · Jan 9, 2003

okay, i did this

        echo "<td><input type=text name=" . $i[$x] . " size=20 value=$row[$x]></td>\n";

and now it updates, but it pulls the data from the last row in the db and ignores what is typed in the text box. ???

statrat · Jan 9, 2003

ah, sorry.
try this...

change

name=" . $i[$x] . "

to

name=field[$x]

so that
field[1] will hold the Name value, field[2] will hold the StreetAddress value, etc.

then change you're update statement accordingly...

UPDATE $table SET Name='$field[1]' , StreetAddress='$field[2]' .....

i'm not positive on the syntax. hope i'm not messing you up further. sorry, thanks. good luck.

horstuff · Jan 9, 2003

Nope. Still pulling the last row of data in the db and inserting it into my specified row. Aargh...

statrat · Jan 9, 2003

ok, i got this to work for me on a test db. since i don't know you're data/table structure, it may break. but maybe it will help. try testing the whole code as is. it can definitely be optimized.

i wasn't exactly sure what you're trying to do. using 1 Update statement to change multiple rows would set all the fields in those rows to the same data. is that what you want? i assumed not, so i put it in a for loop.
the other main thing i did was make the input textboxs multidimensional arrays with the update_id and field# as keys....like this....

<td><input type=checkbox name=update_id[] value='1'></td>
<td>1</td>
<td><input type=text name=update_field[1][1] size=20 value=New></td>
<td><input type=text name=update_field[1][2] size=20 value=New1></td>
<td><input type=text name=update_field[1][3] size=20 value=New2></td>
<td><input type=text name=update_field[1][4] size=20 value=New3></td>
</tr>
<tr>
<td><input type=checkbox name=update_id[] value='2'></td>
<td>2</td>
<td><input type=text name=update_field[2][1] size=20 value=Joe></td>
<td><input type=text name=update_field[2][2] size=20 value=200 Sale></td>
<td><input type=text name=update_field[2][3] size=20 value=Temple></td>
<td><input type=text name=update_field[2][4] size=20 value=Brazil></td>
</tr>
<tr>
<td><input type=checkbox name=update_id[] value='3'></td>
<td>3</td>
<td><input type=text name=update_field[3][1] size=20 value=John></td>
<td><input type=text name=update_field[3][2] size=20 value=444 Kiss></td>
<td><input type=text name=update_field[3][3] size=20 value=Houston></td>
<td><input type=text name=update_field[3][4] size=20 value=United K></td>
</tr>

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

<body>
<?


$host         = "myhost"; //database host
$user         = "myusername"; //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");

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

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

    for($j=0;$j<=$num_ids-1;$j++){
		$id_value=$update_id[$j];

    $query = "UPDATE $table SET Name='".$update_field[$id_value][j+1]."' , StreetAddress='".$update_field[$id_value][j+2]."', City='".$update_field[$id_value][j+3]."', Country='".$update_field[$id_value][j+4]."' WHERE $unique_column=$id_value";

//echo $query.'<br>';
        $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";
    }

}


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=update_field[$row[$unique_column]][$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>

horstuff · Jan 10, 2003

That did it. Works like a champ. Thank you very very much for hanging in there.

sfhc21 · Jun 12, 2003

I am trying to do something similar. I am just starting to understand arrays - but it still confuses me.

I've tried this example, and my query ends up empty everytime. But I'd also like to do something a little different.

What if I wanted to do the same thing - but without checkboxes. Everything gets updated.

I am making a database where I can update my crew sheets without having to make them in excel and output the file to html, and then post it on the web. here is a link to an old crewsheet to give you a idea of where Im coming from:

http://www.pcsproductions.com/crewinfo/March/MAVS%20Mar%201.htm

I have four tables - members, events, list, and positions (Where list contains the member id, event id and position id, along with its own unique id).

I can get the query to output to the text boxes - just can't get it to update.

update multiple fields and rows

Hhorstuff

Sstatrat

Hhorstuff

Hhorstuff

Sstatrat

Hhorstuff

Sstatrat

Hhorstuff

Ssfhc21