Simple UPDATE making me pull my hair out

[Twitch]

Ok, I'm just getting back into learning php and I'm trying to simply update one field based on the userid in a table when a user navigates to a certain page.  Basically without a a form.

 

I've been at it for 2 days and it seems like it should be a simple no brainer, but it's giving me fits.  I'm search and haven't really found a situation like mine so I gave in and decided to ask.

 

When the page loads, the user id is grabbed from a session variable:

$userid  =  $session['USERID'];

 

I have a mysql_query that as of now updates the project status field to "Closed" if it is "Open".

 

mysql_query("UPDATE projects SET projStatus = 'Closed' WHERE projStatus ='Open'");

 

That query works but closes every "Open" project in the table.  When I try to add the userID like so:

mysql_query("UPDATE projects SET projStatus = 'Closed' WHERE userID = $userid AND projStatus ='Open'");

 

Nothing updates at all.

 

I can echo the $userid and it shows up correctly so the session variable is the currently logged in user.

 

Seems like my query including the userID should work as is, no?

 

Any help would be much appreciated.

 

Thanks in advance,

Twitch

[mikesta707]

Assuming that a user with that userid has his projStatus set to Oopen, that should work. Have you made sure that the user id you are testing has his projStatus set to open? try echoing the query itself, and see what it looks like.

 

Any more code would help, because just that snippet seems fine, but something else may be effecting it

[mrMarcus]

try single-quotes around $userid (also added some anti-sql injection in mysql_real_escape_string()):

 

mysql_query("UPDATE `projects` SET `projStatus` = 'Closed' WHERE `userID` = '" . mysql_real_escape_string($userid) . "' AND `projStatus` ='Open'") or trigger_error(mysql_error());

 

EDIT: my bad, if it's a numeric value, single-quotes are not necessary.  wasn't paying attention.

 

anyways, check for SQL errors with the:

 

 or trigger_error (mysql_error());

[Twitch]

Wow thanks for the amazingly quick replies.

mikesta707, I have populated a couple of projects with different users so I could make sure it's not closing all of them.  And indeed, it is...ha ha

I'm going to use the error trigger that mrMarcus suggested and see what it tells me.

 

I'll keep you guys informed and again, can't thank enough for the quick replies.

 

-Twitch

[Twitch]

Well guys in the end, my original query was correct, but I had the session variable code in the wrong place.

I noticed that sometimes the simple query without the userID would suddenly stop working as well.  I deleted the session variable section of code and it updated the projStatus column to "Closed" as it should, I'd add back the variable code and it would stop.  I need that code so I moved it around and voilá, it worked at the top of the page.  As I've learned, that's where session variable code should be in the first place...ha ha  2 days of hair pulling may require a trip to the store for some 1554 by New Belgium to celebrate.  :-)

 

Thanks again for the quick replies, this site will be added to my daily reading.  Very helpful.

 

-Twitch