mysql update help???

[rcasinillo_s]

I retrieved data from database, output to a form thru loop after that iwant to change something and then i will send it back to the data base for update

here is the output datails so that you will the idea what iwantl.
http://www.nwafh.med.sa/eng/php/progs/edit_book_test.php

Thank you in advance

Roland

[Dani]

So what exactly is the problem? What is it you need help doing? Is it not working? Instead of using a SELECT * FROM table WHERE ... you would want to do an UPDATE table SET ... WHERE ...

[rcasinillo_s]

I apologize for not posting clearly. I have 100 rows in my database table with 3 fields I retrieved them all and I print inside the form so that i can edit them directly.
Let say I selected 3 rows from row 50 and I edit them.
row 51 i changed field 2
row 52 I changed field 3
row 53 I changed field 1 & 2
Then click update on my form.
So what is the best way to update this form???
Here is my form script:

print "<form method=\"post\" action=\"insertResult2.php\">";
print "<table width=\"934\" border=\"0\" cellspacing=\"8\" class=\"texxt\" align=\"center\">";
print "<tr>";
print "<td bgcolor=\"#FFFFFF\"><strong>ID</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>&nbsp;</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Authors</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Titles</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Editors</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Call No.</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Edition</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Year</strong></td>";
print "<td bgcolor=\"#FFFFFF\"><strong>Notes</strong></td>";
print "</tr>";
$color_a = "#CCCCCC";
$color_b = "#8B8B8B";
include "../../HTML/Inc/connect.inc";
$query = "SELECT id, author1, title, callnumber, editions, year, notes FROM book";
$result = mysql_query($query,$link) or die("Cannot read record: ".mysql_error());
while($i++ <= mysql_num_rows($result) && $rs = mysql_fetch_array($result))
{
$id[$i]=$rs;
$authors[$i]=$rs;
$title[$i]=$rs;
$callnumber[$i]=$rs;
$editions[$i]=$rs;
$year[$i]=$rs;
$notes[$i]=$rs;
$mod = ($i % 2);
print "<tr>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">$i</td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input type=\"checkbox\" name=\"box$i\" value=\"$id[$i]\"></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><textarea name=\"authors$i\" cols=\"8\">$authors[$i]</textarea></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><textarea name=\"title$i\">$title[$i]</textarea></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input size=\"5\" type=\"text\" name=\"editors$i\" value=\"$editors[$i]\"></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input type=\"text\" name=\"callnumber$i\" value=\"$callnumber[$i]\"></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input size=\"5\" type=\"text\" name=\"editions$i\" value=\"$editions[$i]\"></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input size=\"2\" type=\"text\" name=\"year$i\" value=\"$year[$i]\"></td>";
print "<td bgcolor=\"";
if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input type=\"text\" name=\"notes$i\" value=\"$notes[$i]\"></td>";
print "</tr>";
}//End While
print "<tr>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\">&nbsp;</td>";
print "<td bgcolor=\""; if($mod == 0){echo $color_a;}else {echo $color_b;}
print "\"><input type=\"Submit\" value=\"Update!\"></td>";
print "<input type=\"hidden\" name=\"edit_book\" value=\"book_record\">";
print "</tr>";
print "</table>";
print "</form>";

Thank in advance....

[rcasinillo_s]

Here is what I did:
The below script is working, but only for a single query what I mean is, when im going to tick single row on my form. If i'm going to tick more than one row it will get only the last row.
Could you suggest a better way or revise this script to work for multiple update...

$query = "UPDATE book SET ";
foreach($_POST as $key => $value){
if($key != "edit_book" && $key != "box") {//excluding edit_book & box
$query .= "$key = '$value', ";
}
if($key == "edit_book") {
$query .= "$key = '' ";
}
if($key == "box") {
$key = "id";
$key_id = $key;
$value_id = $value;
}
}
$query .= "WHERE $key_id = '$value_id'";

here is the query equivalent of the above script:

$query = "UPDATE book SET authors = 'Henry JB', title = 'Clinical diagnosis and management by laboratory methods', editors = '', callnumber = 'REF QY4 C6164', editions = '20th', year = '2001', notes = '', edit_book = '' WHERE id = '122' ";

Thanks in advance
Roland