Failed to update database

[ljCharlie]

I'm trying to update a MySQL database but the program kept failing. I believe the problem came from the WHERE clause where the comparison failed to make a match.

Below are my codes:

[QUOTE]$query = UPDATE members SET (FstName='$txtFstName', MdlName='$txtMdlName', LstName='$txtLstName') WHERE memberID ='$usrID';
$result = mysql_query($query);
QUOTE]

The variable $usrID is a number string passing from an update .shtml page to the .php updated page. In the database, the memberID is defined as VARCHAR(50). Please tell me why the comparison failed to match to update.

Many thanks in advance!

ljCharlie

[raf]

Try this


$query = "UPDATE members SET (FstName='" . $txtFstName ."', MdlName='" . $txtMdlName . "', LstName='" . $txtLstName . "') WHERE memberID ='" . $usrID . "'";
$result = mysql_query($query);


So with doublequotes arround the strings and with . between strings and variables.
(why a varchar for memberID ? and why such a big foield, must be expecting loads of members, like, the whole world)

Best check the sql string you execute with

echo $query;

so you can see which statement is sent to the db. It'll make clear why it's not working.

[ljCharlie]

Thanks for trying to help me. I tried the WHERE memberID ='" . $usrID . "'"; but it's not improving. I did try the echo $query; and the $usrID number was there in the query fine. The query showed the correct number. I even tried hard coding the $usrID into the memberID ='0027'" but the error is still the same. It just failed to make a match. However, if instead of an Update, I tried a Select query and it worked. The program matches in Selec query but not when I do an Update query. I tried with and without the quote on both ends of the $usrID and nothing seems to be working.

ljCharlie
:confused:

[raf]

Hmm.

What error did you get ?

Probably nothing, but did you try it for an id that doesn't start with '00' and are you sure it's not 'OO'

[ljCharlie]

Here's the code:

$query = "UPDATE members SET (members.Address='$txtAddress') WHERE memberID =".$usrID;
$result = mysql_query($query);
if($result){
while($row= mysql_fetch_array($result, MYSQL_ASSOC)){
$usrID = $row["memberID"];
}
}
else{
echo"Update has failed. Check your password and/or user name.";

}

It will not generate an error if the query failed to match. The script will echo the Update fail message only. So I'm not sure it's going wrong. I have even simplify the Update query to just only one field but still fail to match.

ljCharlie

[ljCharlie]

I figured it out. It seems to work when I leave out the parenthesis. Anyway, my next problem is to put all NULL into the database field if a textbox is not filled by the user. Currently I have tried this:


$txtEmail = "\N";

$query = "UPDATE members SET FstName='$txtFstName',Email='".addslashes($txtEmail)."' WHERE memberID =$usrID";

However, when I check the database, it only put in \N and not NULL into the database field.

ljCharlie
:confused:

[ljCharlie]

I got it. Instead of \N I tried NULL and it worked!

ljCharlie