Page 1 of 2
[SOLVED] MySQL field ok on INSERT, selectively not updating
Posted: Tue Mar 03, 2009 12:22 pm
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?
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Tue Mar 03, 2009 10:55 pm
by susrisha
try to put an error catch statement for the query and see if anything turns up..
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 9:03 am
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.)
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 9:46 am
by papa
Is $Posn empty perhaps ?
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 10:11 am
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!
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 12:20 pm
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';
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 12:27 pm
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.
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 12:37 pm
by Benjamin
LOL, Ok, well try the last two queries then and see if that works.
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 1:12 pm
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)
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 4:29 pm
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.
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 6:30 pm
by Benjamin
What was ther result of the queries I gave you?
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 7:45 pm
by Bill H
The insert was fine. The update blanked the Nused field. In phpmyadmin the update worked fine.
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 7:49 pm
by Benjamin
Please echo your update query exactly as it's being sent to mysql_query.
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Wed Mar 04, 2009 11:59 pm
by Bill H
Please echo your update query exactly as it's being sent to mysql_query.
Check my post at 3:29pm
Re: MySQL field ok on INSERT, selectively not updating on UPDATE
Posted: Thu Mar 05, 2009 12:35 am
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.