I have problem updating my MySQL database. The user can edit a form in my page and once they press 'send', the php script should be able to update all the information. However the database doesn't seem to be updated after pressing the 'send' button in the form but only redirect it self to admin.php.

Somehow only the last line took effect. Is there something wrong with my SQL statement?

Any help would be appreciated! :)

if(isset($_POST["action"])&&($_POST["action"]=="update")){  
    $query_update = "UPDATE `item_record` SET ";
    $query_update .= "`item_name_chi`='".$_POST["item_name_chi"]."',";
    $query_update .= "`item_name_eng`='".$_POST["item_name_eng"]."',";
    $query_update .= "`item_price`='".$_POST["item_price"]."',";
    $query_update .= "`item_comment`='".$_POST["item.comment"]."',";
    $query_update .= "`item_avai`='".$_POST["item.avai"]."',";
    $query_update .= "WHERE `item_id`=".$_POST["item_id"];  
    mysql_query($query_update);
    header("Location: admin.php");
}

On line 8 you should probaly have a space before WHERE

$query_update .= " WHERE `item_id`=".$_POST["item_id"];

and the comma on line 7 should not be there (before the WHERE):

$query_update .= "`item_avai`='".$_POST["item.avai"]."'";

The easiest way to check is to insert a debug code between lines 8 and 9:

die($query_update);

which will display the query and stop the script. You can copy the query into phpmyadmin and test it there.

Thanks! That works!

Just one more question, item_avai is actually a checkbox in the form. Somehow I'm unable to change the binary after checking/unchecking. Is there a way to get around?

PS the 'item.comment' and 'item.avai' on top should be 'item_comment‘ and 'item_avai’.

The checkbox only pass on 'on' rather than 0/1 (binary).

The checkbox, if checked, has a value in the $_POST array determined by a value attribute. If it hasn't been checked then it is not set in the $_POST at all. So you can code it like this:

<input type="checkbox" value="1" name="item_avai" />

and you check for it in the action page:

if(isset($_POST['item_avai']) && $_POST['item_avai'] == 1) {
    $item_avai = 1;
} else {
    $item_avai = 0;
}

$query_update .= "`item_avai`='" . $item_avai . "',";

instead of using if-else,it will be better to use conditional ternary operator.

 $item_avai=(isset($_POST['item_avai']) && $_POST['item_avai'] == 1)?"on":"off";