[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:

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

Post by Bill H »

Well, the $_POST['id'] comes from the following, so I think I'm okay on that.

Code: Select all

 
     echo "<form name='inform' method=post action='o_edithalf.php'>\n";
     echo "<input type=hidden name='id' value=",$Rid,">\n";
 
Given the

Code: Select all

$Typ = 0 + $_POST['Type'];
one might have assumed I would know to be sure the record number was also an integer.

I am also familiar with the "backticks solve all problems" concept, but it failed here. I did try it; copied your suggestion exactly. Same results, Nused field is blanked out.
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 »

Sounds like you're getting frustrated. I saw a number of reserved keywords in the query, and I am lazy. It took me less time to backtick them all than it would have to look them up.

Anyway, can you please echo the new query being sent to mysql_query.

It's safe to assume one of the following:

1. Either Nused is not set in the database and you think it is or
2. Nused is not set in the query and you think it is.

Considering that Nused is in the query, that pretty much rules out number 1.
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, here is the procedure I am following. I pasted your code into my script. Ran the script. Looked at the echoed SQL statement and saw all of the backticks along with the "`Nused`='Tlr'" part. I saw, in fact that it was exactly what I expected it to be. I highlighted it with my cursor and hit "Ctrl-C" to put it into my clipboard.

Code: Select all

UPDATE Precord SET `Get`=1,`Nused`='Tlr',`Name`='William' WHERE id=20978
Then I went to phpmyadmin and selected the database in question. I clicked on "browse" and went to the last record and looked to see what it looked like. The "Nused" field was blank. I then clicked on "SQL" and went to the correct field. I hit "Crtl-V" and pasted the SQL statement and executed it. It indicated that the statement had executed without error and had affected one row. I then hit "browse" again and went to the last record again. The "Nused" record read "Tlr'.

Just out of curiosity, what "number of reserved keywords" did you notice in the query?
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 »

I saw Get, Name and Id. Looking at the reserved keyword list I don't see any of them on there but I know I've had problems with id before.

I'm starting to think that there may be whitespace someplace. I noticed that the query you posted was different than what I posted. Please try this query EXACTLY how it is.

Code: Select all

 
$Query = "UPDATE Precord SET `Get` = '$Typ', `Nused` = '$Posn', `Name` = '$Name' WHERE `id`= '" . trim($_POST['id']) . "'";
 
 
I would also pull the affected_rows and see how many rows were updated.
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've been using "id" in dozens of databases for more than five years, use it routinely for the unique identifier field, and I've never had the least bit of problem with it. It's in use with about 30 databases in this application right now and is causing no problems. Unless, of course, it is the source of this problem.

I know that two of the fields were altered, and that Nused was blanked. I'm not guessing at that, I'm using phpmyadmin to inspect the actual record and verify that, so I don't really need mysql_affected_rows() to tell me that one record was altered.

I have structured this SQL statement about two dozen different ways. I have done it with backticks and without backticks. I have done it with spaces before the commas and without spaces before the commas.

This script

Code: Select all

 
if (isset($_POST['Okay_x']))                                     // adding or revising: user approved data
{                                                                // ($_POST['id'] will have a value of zero if adding)
     $Ver = mysql_real_escape_string($_POST['Verif'],$Link);     // $_POST['Okay_x'] just apporves data in the form
     $Name = mysql_real_escape_string($_POST['Name'],$Link);
     $Inq = mysql_real_escape_string($_POST['Inq'],$Link);       // magic quotes is off, so the $_POST is clean
     $Nused = mysql_real_escape_string($_POST['Nused'],$Link);
     $Comm = mysql_real_escape_string($_POST['Comm'],$Link);
     $Cash = 0 + $_POST['Cash'];
 
     $Query = "SELECT Arch FROM Precord WHERE id=". $_POST['id'];
     $Result = mysql_query($Query, $Link);        // read it whether checkbox set or not
     $Row = mysql_fetch_array($Result);           // so as to preserve original setting
     $A = 0 + $Row['Arch'];                       // if checkbox is not checked
     if (isset($_POST['Arch']))
          $A = ($Row['Arch'] > 0) ? 0 : 1;        // will move really old ones to current period
 
     $Query = "UPDATE Precord SET Name='$Name',Verif='$Ver',Inq='$Inq',Nused='$Nused',Comment='$Comm',log=$Cash,Arch=$A WHERE id=" . $_POST['id'];
}
 
produces this SQL statement, which updates the Nused field properly.

Code: Select all

UPDATE Precord SET Name='Shari',Verif='employee list-10/7/08',Inq='holiday savings club',Nused='not requested',Comment='',log=1025,Arch=0 WHERE id=20657
This script

Code: Select all

 
if (isset($_POST['Type']))               // approved the addition/revision of a staff record
{
     $Ptypes = array("","Oth","Tlr","Rec","Fsr");
     $Inum = $_SESSION['Clnt'];
     $Lnum = $_SESSION['Locn'];
     $Name = mysql_real_escape_string($_POST['Name'],$Link);
     $Posn = $Ptypes[$_POST['Posn']];        // numeric $_POST from dropdown select
     $Typ = 0 + $_POST['Type'];              // numeric user input
     $D = date("Y-m-d") . " 00:00:00";       // date only used in satff records
 
     $Query = "UPDATE Precord SET Get=$Typ,Nused='$Posn',Name='$Name' WHERE id=" . $_POST['id'];
}
 
produces thie following SQL statement, which blanks the Nused field.

Code: Select all

UPDATE Precord SET Typ=1,Nused='Rec',Name='William' WHERE id=20968
Can anyone see a difference which would cause that? I should note, they are in the same script. One runs or the other one does.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Comment out this line:

Code: Select all

$Query = "UPDATE Precord SET Get=$Typ,Nused='$Posn',Name='$Name' WHERE id=" . $_POST['id'];
Add after it this line

Code: Select all

$Query = "UPDATE Precord SET `Get`='11', `Nused`='22', `Name`='33' WHERE id=20968";
execute the php script and see whether the record with id=20968 is properly updated (i.e. Nused = '22').
There are 10 types of people in this world, those who understand binary and those who don't
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 »

Oh boy, this is embarrasing. See that first code segment? Well it is located physically after the second one, and the second one also has an "okay" button which I stupidly named "Okay." So the $_POST['Okay_x'] segment is running right after the $_POST['Type'] one does. It is blanking several other fields in addition to the Nused field, but they are fields that aren't used for this particular activity. So I renamed the "Okay" button for that form to "StaffOkay" and the $_POST['Type'] segment to $_POST['StaffOkay_x'] and that solved everything. Only reason it didn't blank the Name field is I used the same variable names.

Boy do I feel stupid. :oops: :oops: :oops: :banghead: :oops: :oops:

Fortunately, I left the "echo" statement in the first one and it ran when it wasn't supposed to. "Oh, there's a clue."
Post Reply