Update not updating!

Square1

Hi all:

What should be simple has caused me about 2 hours of frustration so far.

I have a simple Update query. It is updating a record based on a username being pulled in from a cookie. The cookie was written in ASP Classic and is an array. I have to explode the cookie array in PHP to get to the username. But when I run the code, the SQL is not performing the update. I have echoed the cookie array variable for the username and it is the correct variable.

I am new at PHP. If I could echo the SQL query I might get my answer but I have not been able to do that yet. Here is the code:


  <?php
  require($_SERVER['DOCUMENT_ROOT'] . '/inc/dbconn_RW.php');

  $Luxe_Cookie=explode("&", $_COOKIE['OLD']);
  $Luxe_Cookie[1]=str_replace("T1aUs=","",$Luxe_Cookie[1]);
  $cUsername = mysql_real_escape_string($Luxe_Cookie[1]);

  mysql_query("Update members SET member_group_id = '3' WHERE name = 

'$cUsername'")
	or die(mysql_error());
  mysql_close($dbhandle);

  ?>

Thank you!

wyattstorch42

Why can't you echo the query?

echo "UPDATE members SET member_group_id = '3' WHERE name = '$cUsername'";

Also, you should definitely be getting a MySQL error if it's not updating. What error is returned, or do you get nothing at all?

Res

You will only get an error from MySQL if there was a syntax issue with the query. If you were to update based on a an unmatched where clause, you would be successfully updating 0 rows with no error even though the where clause is incorrect.

Echo the query like wyattstorch42 showed, this should help you out. If it look right but still isn't updating, try selecting data where the user name matches the variable. It sounds like it's an issue with the username in the cookie being different then in the table. Keep in mind that MySQL is case sensitive on an equals operation, so you might want to try using LIKE instead of =. Also make sure you don't have leading or trailing spaces, as that would make the name appear to be the same, but not actually the same.

bradgrafelman

Res wrote:
Keep in mind that MySQL is case sensitive on an equals operation

That's true only if you're using a case-sensitive character set (e.g. one that doesn't end in "_ci").

Square1

Thank you all for your help so far. Here is some more info that make this stranger.

echo UPDATE shows everything looks fine. The variable is correct.
There are no spaces in the variable. The case is all lower as it should be

Okay, this will separate the men from the boys....

When I run the update the FIRST time it works. If I change the SET portion and rerun the UPDATE that too works. So for example the following will all work no matter how many time I try it or changes that I make:

UPDATE members SET member_group_id = '1' WHERE name = '$cUsername'";
UPDATE members SET member_group_id = '32' WHERE name = '$cUsername'";
UPDATE members SET member_group_id = '23' WHERE name = '$cUsername'";
UPDATE members SET member_group_id = '4' WHERE name = '$cUsername'";

So it sounds like there was never any problem, right? Wrong!

In my testing I found that when I go to the table directly in myphpadmin and manually adjust the 'member_group_id' to a different number and THEN try the UPDATE it no longer works! Doesn't matter if I change the SET numbers. It is stuck and will not respond to the UPDATE script.

I am not 100% sure about the following, but I believe I walk away for a few hours and come back and all seems fine again (until I reset the table manually). I say I am not sure because I wasn't aware of doing it before, but not I am making a note of it.

Something is screwing things up when I manually adjust the member_group_id directly in the table. It stops the script from working!!!

Thank you all and Happy New Year!!!!

Square1

OK, here is what I think is going on.

It is a phpMyAdmin thing. When you browse a table and manually edit a record and then dynamically change that same record you cannot go back to the browse screen and hit an F5 or browser refresh and see your record updated to the dynamically changed record. By doing this your record will revert back to what it was before the dynamic change. You need to hit the refresh button that is part of the phpMyAdmin commands. Then the record is properly updated.

Live and learn.

I thank you all for your help with this!!! And have a great New Year!