[SOLVED] MySQL field ok on INSERT, selectively not updating

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

[SOLVED] MySQL field ok on INSERT, selectively not updating

Post by Bill H »

My database has a number of fields, among them Name and Nused, both of which are VARCHAR(30) latin1_swedish_ci not_null fields. The two following SQL commands are typical of what is causing my headache, as the "Oth" text is copied correctly into the database on the INSERT command, but after the UPDATE command, while the Name fields and Get fields are changed appropriately, the Nuser field is now blank. Whatever was in it before is blanked out.

Code: Select all

 
INSERT INTO Precord (id,iref,lref,Get,Name,Nused,Date) VALUES (0,32,197,1,'William','Oth','2009-03-03 00:00:00') 
 
UPDATE Precord SET Get=-1,Name='Mary',Nused='Oth' WHERE id=20968
 
To repeat, the Get value is updated, the Name value is updated, the Nused value is blanked out.

I have echoed the query statements to make sure they are being constructed correctly. That is, in fact, what I have pasted above. When the SQL statement is pasted into phpmyadmin it works fine, updates the Nused field exactly as one would expect. When executed from within PHP, it does not.

Here's the code.

Code: Select all

 
     if ($_POST['id'] > 0)
          $Query = "UPDATE Precord SET Get=$Typ,Name='$Name',Nused='$Posn' WHERE id=" . $_POST['id'];
     else $Query = "INSERT INTO Precord (id,iref,lref,Get,Name,Nused,Date) VALUES (0,$Inum,$Lnum,$Typ,'$Name','$Posn','$D')";
     mysql_query($Query, $Link);
     echo $Query;
 
Any ideas?
Last edited by Bill H on Fri Mar 06, 2009 3:33 pm, edited 1 time in total.
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by susrisha »

try to put an error catch statement for the query and see if anything turns up..
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Bill H »

No error. Not unexpected given that it is updating the record. It merely is not updating one field within the record. Wierd.
(Well, it is updating that field, but not with the data sent by the SQL statement.)
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by papa »

Is $Posn empty perhaps ?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Bill H »

Is $Posn empty perhaps ?
No, the output of the echo(); statement is shown in the op. $Posn contains "Oth".

Here's more that is interesting. When the following is sent.

Code: Select all

UPDATE Precord SET Get=1,Name='William B' WHERE id=20971
The Nused field is erased. It did contain "Tlr" but after the SQL is executed it is blank!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Benjamin »

This will delete all your records, so just a heads up.

What does this do?

Code: Select all

 
TRUNCATE TABLE Precord;
INSERT INTO Precord (`id`, `iref`, `lref`, `Get` , `Name` , `Nused`, `Date`) VALUES ('0', '32', '197', '1', 'William', 'Oth', '2009-03-03 00:00:00');
UPDATE Precord SET `Get` = '-1', `Name`= 'Mary', `Nused`= 'Oth' WHERE `id`= '20968';
 
 
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Bill H »

Well, I can't afford to truncate the table. I think my client would get upset at losing several years worth of data.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Benjamin »

LOL, Ok, well try the last two queries then and see if that works.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Eran »

Try escaping the $Posn variable with mysql_real_escape_string and see if it helps. Also, var_dump that variable and see if the character count matches what you expect (might be some hidden / UTF characters that you can't see with an echo)
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Bill H »

I mysql_real_escape_string everything on a regular basis, and a var dump didn't show anything. I have this in databases, but I am by no means sure that the issue has to do with the database, since the db has been up and running for a couple of years, and has been updating properly (including the "Nused" field) in quite a few scripts. I cannot, for the life of me, see any difference in this script. It's driving me nuts.

The only differing condition is that the value for that field is being entered by the user, in this case, by means of a drop down and the value is of the nature

Code: Select all

<option value='Tlr'>Teller</option>
I'm going to play around with something different in that area and see what happens.

Edit: this is just wierd. Here's the SQL statement preparation from the $_POST array

Code: Select all

 
     $Ptypes = array("","Oth","Tlr","Rec","Fsr");
     $Inum = $_SESSION['Clnt'];
     $Lnum = $_SESSION['Locn'];
     $Name = mysql_real_escape_string($_POST['Name'],$Link);
     $Posn = $Ptypes[$_POST['Posn']];
     $Typ = 0 + $_POST['Type'];
     $D = date("Y-m-d") . " 00:00:00";
 
     if ($_POST['id'] > 0)
          $Query = "UPDATE Precord SET Get=$Typ,Nused='$Posn',Name='$Name' WHERE id=" . $_POST['id'];
     else $Query = "INSERT INTO Precord (id,iref,lref,Get,Name,Nused,Date) VALUES (0,$Inum,$Lnum,$Typ,'$Name','$Posn','$D')";
     echo $Query;
     mysql_query($Query, $Link);
 
And here is the resulting SQL statement:

Code: Select all

UPDATE Precord SET Get=1,Nused='Tlr',Name='William' WHERE id=20978
Two fields are updated, the "Nused" field is blanked out.

When I take that statement and paste it into phpmyadmin the statement executes without error, and the "Nused" field is updated to read "Tlr" as one would expect. Well, one would normally expect.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Benjamin »

What was ther result of the queries I gave you?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Bill H »

The insert was fine. The update blanked the Nused field. In phpmyadmin the update worked fine.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Benjamin »

Please echo your update query exactly as it's being sent to mysql_query.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Bill H »

Please echo your update query exactly as it's being sent to mysql_query.
Check my post at 3:29pm
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL field ok on INSERT, selectively not updating on UPDATE

Post by Benjamin »

Ok, change it to this please and it should work:

Code: Select all

 
$Query = "UPDATE Precord SET `Get` = '$Typ', `Nused` = '$Posn', `Name` = '$Name' WHERE `id`= '{$_POST['id']}'";
 
 
Also just as an FYI make sure the $_POST['id'] variable is an integer before using it in the query. Same for all the other fields.
Post Reply