MySQL not updating record

MySQL results are valid but physical record is not updated properly

[LukeDouglas]

In a nutshell, I have feedback indicating MySQL did update the record but the record fields were actually cleared of 'all' values.

I'll post the code with comments in [] brackets and the page printout below the code with comments in [] brackets. Any assistance to find this needle in a haystack would be greatly appreciated. I've spent several hours trying to find out if this is a problem with PHP or MySQL...or ME!

CODE:
------------------------------------------------------
include ("dbconn_Inventory.inc");
$rowid = $_GET["rowid"];

[these next few lines were to see a couple of fields of the record prior to updating]

$searchstring = "SELECT * FROM Inventory WHERE ID= $rowid";
$result = mysql_query($searchstring);
if (!$result) {
die('<p class="contents-home">Error performing query: ' . mysql_error() . '</p>');
}

while ($row = mysql_fetch_array($result)) {
print ($row['ID']) . "<br>";
print ($row['ItemNumber']) . "<br>";
print ($row['Description']) . "<br>";
}

[this is the main SQL statement. It doesn't work from the page but does work if I execute it directly within phpAdmin, of course with the actual values]

$sqlquery = "UPDATE Inventory SET
ItemNumber = '$ItemNumber',
Description = '$Description',
Details = '$Details',
Brand = '$Brand',
Category = '$Category',
SubCategory = '$SubCategory',
Price = '$Price',
ShippingAmt = '$ShippingAmt',
Notes = '$Notes',
Photo = '$Photo' WHERE ID=$rowid";

[this is the diagnostic printout to see the actual SQL statement with retrieved values]

print($sqlquery);
print("<br>");

[this is the diagnostic to ensure that there were no MySQL errors encountered during the updat with corresponding on page printout]

$result = mysql_query($sqlquery);
if (!$result) {
print("mysql error encountered<br>");
die('Could not query: ' . mysql_error());
}
else {
print("NO mysql error encountered<br>");
}
printf ("Updated records: %d\n", mysql_affected_rows());
print("<br>");

[closed mysql]
mysql_close();
------------------------------------------------------

PRINTOUT:
------------------------------------------------------
[the following three lines is to show me that the record was physically retrieved with two fields of data prior to updating]
50
item 1
description 1

[this is the SQL statement which works if I execute directly within phpAdmin]

UPDATE Inventory SET ItemNumber = 'item 2', Description = 'description 2', Details = 'details 2', Brand = 'brand 2', Category = 'PT', SubCategory = 'PT-Ladies', Price = '19.95', ShippingAmt = '0.00', Notes = 'Notes 2', Photo = 'DSCF0081.jpg' WHERE ID=50

[this shows that no MySQL errors were encountered and that one record was updated]

NO mysql error encountered
Updated records: 1

[this is an on page display of what changes 'should' have been made to the record. I didn't include the code for this section as it's basically just feedback and has nothing to do with the update process.]

You have updated a record with this information
ItemNumber: item 2
Description: description 2
Details: details 2
Brand: brand 2
Category: PT
SubCategory: PT-Ladies
Price: 19.95
ShippingAmt: 0.00
Notes: Notes 2
Photo: DSCF0081.jpg
------------------------------------------------------

I think that gives you all the information I have. If you can help, I would appreciate it. I'm stumped!

Luke

[StupidScript]

Have you tried committing the query?

printf ("Updated records: %d\n", mysql_affected_rows());

mysql_query("COMMIT");

Also, where are you grabbing the "result" page data from, if not from a new query?

[Salsa]

SS: Isn't COMMIT for transactional type tables, like BDB and InnoDB? Your question about where the results are coming from is a good one, but I guessed he kept reentering them with phpMyAdmin, but dunno....

Welcome to Webmaster World, Luke,

Often when someone posts such a long bit of code, many members will just take a glance and move on because usually so much code is not justified, and it's contrary to the forum charter [webmasterworld.com].

However! You've really done an admirable amount of troubleshooting before posting, and I think that every line of your post is justified.

The bad news is that I'm not sure what's wrong, either. But given your results, it seems consistent that all of your update variables are empty. That would not return an error and would update the row with empty strings. I saw where you

print($sqlquery);

, and I'm assuming that the output in your "PRINTOUT", the SQL that you queried phpAdmin, is the result of that, but that's not clear. When echoing out a lot of stuff while debugging, I like to use something like:

echo "\$sqlquery = '$sqlquery'<br>\n";

...that way you can easily match a variable with its value after you run the script.

Have you tried running the scripted query with string values rather than variables? like

if (!mysql_query("UPDATE Inventory SET ItemNumber = 'item 2', Description = 'description 2', Details = 'details 2', Brand = 'brand 2', Category = 'PT', SubCategory = 'PT-Ladies', Price = '19.95', ShippingAmt = '0.00', Notes = 'Notes 2', Photo = 'DSCF0081.jpg' WHERE ID=50")) die("String query failed: ".mysql_error());

Also, I'm not sure how you have PHP error reporting set, but if it's low, you might want to put:

error_reporting(E_ALL);

...at the top of your script to see what errors PHP might have to report.

I hope this helps, and if not, maybe the bump will encourage others to look at it.

[StupidScript]

Isn't COMMIT for transactional type tables, like BDB and InnoDB?

Usually, InnoDB, specifically. And I don't need to use it in my own update queries, either.

I was looking for anything that might be missing from the syntax, and noticed that LukeDouglas' code was nearly identical the the PHP manual example of an update query with a mysql_affected_rows() function, but was missing the manual's COMMIT line after the mysql_affected_rows() query.

From the manual:

Example 2. Update-Query

<?php
/* connect to database */
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Could not connect: " . mysql_error());
mysql_select_db("mydb");

/* Update records */
mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
printf ("Updated records: %d\n", mysql_affected_rows());
mysql_query("COMMIT");
?>

The above example would produce the following output:

Updated Records: 10

While there is a note from "mijnpc at xs4all dot nl" dated 16-June-2002 in the main mysql functions section that COMMIT is for use in InnoDB-type dbs, in the mysql_affected_rows() section the manual doesn't make any distinction between table types for the use of COMMIT.

My thoughts were that (a) perhaps Luke's db is set up as an InnoDB-type db, or (b) perhaps calling mysql_affected_rows() is doing something in his memory that's clearing out the values before actually writing them to the table, where any output between the query and it's being written might break the query.

(b) doesn't make much sense, I guess, but it couldn't hurt his code to add the line and test it, I figured.

I like your ideas better. :)

[Salsa]

SS: Okay, I see what you're saying. That's a good catch. And it makes me wonder more about your first question, too. The problem has to be something really simple or really wierd. In my experience it's usually something really simple--or at least it seems that way after the fact!

[LukeDouglas]

Salsa,

Thanks for the reply. I added a routine 'after' closing the MySQL connection to re-connect, do a query on the edited record and guess what? It did show the variables from the update. But when I Browse the table in phpAdmin, the fields are empty and if I load an edit page, the fields are empty.

So I did as you suggested and inserted the code to update using string values instead of variables as follows:

if (!mysql_query("UPDATE Inventory SET ItemNumber = 'item 2', Description = 'description 2', Details = 'details 2', Brand = 'brand 2', Category = 'PT', SubCategory = 'PT-Ladies', Price = '19.95', ShippingAmt = '0.00', Notes = 'Notes 2', Photo = 'DSCF0081.jpg' WHERE ID=50")) die("String query failed: ".mysql_error());

Well, it did update the record. So your guess that the variables were empty seems to be the problem.

However, if the variables are empty, then why did the sql string build properly and if the variables were empty, then why did the record print out properly 'after' I closed MySQL and re-connected?

Finally, how can I ensure that the variables are not empty? Believe it or not, I'm using basically the same coding as I've done on other sites which I recently tested and those are still working fine.

I have repaired, flush, deleted and recreated the Inventory table so the table structure is good.

This is just one of those nagging problems that I decided to seek another set of eyes because mine are missing something which is probably so insignificant on the surface but critical underneath.

Thanks.

Luke

[StupidScript]

Last thought, for now ...

Luke, maybe your phpAdmin installation is either malfunctioning (hmmm) or not re-reading the table data after the update?

If you can see the data in the table, but phpAdmin doesn't show it, it seems likely there's an issue between them.

Is the server this problem resides at the same kind of server as your working installations? i.e. the previous servers were Posix and the new one's Win?

[coopster]

Believe it or not, I'm using basically the same coding as I've done on other sites which I recently tested and those are still working fine.

Those variables don't happen to be coming from an HTML <form>, are they? If so, do those existing sites have register_globals [php.net] on and perhaps this new server does not?

Also, mysql_query returns TRUE if a query executes without error, but it still may not be returning any rows. A better way to check the result set on a SELECT query is to use mysql_num_rows [php.net]() as opposed to

if (!$result) {

[Salsa]

My answer to most of your questions is, "I don't know." I did notice that you're setting $rowid from $_GET["rowid"], but where are the other variables coming from? If the script has worked on other machines, maybe they had register_globals enabled, and this one has them disabled, in which case you'd have to get all of your variables from $_GET or $_POST, as the case may be.

That doesn't explain why $sqlquery prints okay, but does it? Did you do the

echo "\$sqlquery = '$sqlquery'<br>\n";

thing? Sorry, but I know how messy my code can get when I'm debugging, and maybe that's coming from someplace else--and whatever is wrong must be a stretch! ; )

Also, did you do the error_reporting(E_ALL) thing? if there are any unset variables, that will shout them out.

Also, at the bottom of your script, try putting:

echo "<pre>"; 
echo "\$GLOBALS = ". print_r($GLOBALS); 
echo "</pre>";

...to help you see what variables you have on hand.

[LukeDouglas]

I added the following code as you suggested after this line:
$result = mysql_query($sqlquery);

$num_rows = mysql_num_rows($result);
echo "$num_rows Rows\n";
echo "<br>";

I got this error message:

Warning: Supplied argument is not a valid MySQL result resource in /home/webadmin/thepeapod.com/html/admin/UpdateTableInventory.php on line 62

Rows

I am assuming I should have got a '1' result but according to the MySQL manual, this should have worked.

Luke

[StupidScript]

mysql_num_rows for SELECT, INSERT and a couple of other similar types of query.

mysql_affected_rows for UPDATE, DELETE and a couple of other similar types of query.

If you used num_rows on an UPDATE query, you'll get that error. (Likewise using affected_rows in a SELECT query will return the error.)

What happened with COMMIT, if you tried it?

[LukeDouglas]

I decided to try the post variables with no change using the $results update. I modified to use the mysql_query update with no change. Also, it shows '0' records updated and '0' rows affected.

$sqlquery = ("UPDATE Inventory SET ItemNumber = '" . $_POST['ItemNumber'] . "', Description = '" . $_POST['Description'] . "', Details = '" . $_POST['Details'] . "', Brand = '" . $_POST['Brand'] . "', Category = '" . $_POST['Category'] . "', SubCategory = '" . $_POST['SubCategory'] . "',Price = '" . $_POST['Price'] . "', ShippingAmt = '" . $_POST['ShippingAmt'] . "', Notes = '" . $_POST['Notes'] . "' , Photo = '" . $_POST['Photo'] . "' WHERE ID=$rowid");

print("<br>QUERY STRING:<BR>" . $sqlquery . "<br><br>");
mysql_query($sqlquery);
mysql_query("COMMIT");

printf ("Updated records: %d\n", mysql_affected_rows());
print("<br><br>");

$num_rows = mysql_affected_rows();
echo "Number of Rows: " . $num_rows;
echo "<br>";

Man, this is really confusing. I mean everything looks so good. I added this line at the end:
echo "<pre>"; echo "\$GLOBALS = ". print_r($GLOBALS); echo "</pre>";

I checked all the variables available and they are there, including the POST variables and array variables.

Something is happening from the time that update is executed and the tables are updated. I have my web sites on a VPS server with a server hosting company so all PHP variables are the same with all of my clients web sites.

Register Globals are on for both Local and Master.

Any other suggestions?

[StupidScript]

Man, what a noggin-scratcher! There's really not that much to it ... hmmmm ...

[Salsa]

Come to think of it, I may have had an experience like this. Luke, are you always connecting to the database in the same way, using the same set of user, password, default db etc? What I'm thinking is that many of my sites use identical database structures, too, and the files where I keep access stuff outside the root are also similar. I'll generally have various MySQL users with privileges limited to their roles. In a case like your update, the script might use the access values from the update_user file. Well, when I'm using an old site as a template for a new one, if forget to change the values in the update_user file, I'll find myself successfully updating an unsuspecting database. It's only happened once, but I recall that it had me puzzled for a while. And it could explain why you're seeing something different when you look in phpAdmin. Do you also use the command line, mysql client? That's my number one tool for seeing what's really happening.

Also note that mysql_affected_rows() will return 0 even if the update is successful but you update with identical values as already exist in the table, so try changing the update values if you're not already.

[LukeDouglas]

SS & Salsa,

Thanks for the reply. Salsa, I found out one thing. If I remove all HTML coding and keep just the PHP update routines, it works fine. So I'm going step by step with the HTML coding to try and determine at which line it messes up the update routine.

I bet when I find the problem, I'll hit my head and say, "wow, I could have had a V-8".

Better yet, I'll just confirm what I've always suspected. I'm just plain stupid!