Darn you SQL, what is it this time?!?

[lomn75]

Getting an error in SQL syntax and can't figure out why:
(PHP + MySQL, using mysql_error())
table structure (table called 'players'):

Field   	Type  		Attributes  	Null  	Default  	
player_id  	smallint(5) 	UNSIGNED 	No  	auto_increment  
lname  	        varchar(50) 			Yes	NULL	  	
fname          	varchar(50) 			Yes 	NULL  		
picture_loc  	varchar(25) 			Yes 	NULL  		
nflteam_id  	tinyint(3) 	UNSIGNED 	Yes  	NULL  	  	
position_id  	tinyint(3) 	UNSIGNED 	Yes  	NULL  	  	
active   	tinyint(1) 			Yes	1  		
nfl_profile  	varchar(100) 			Yes	NULL

SQL query:

SELECT player_id FROM players WHERE active=1

error:

Unknown column 'active' in 'where clause'

I've tried putting backticks (`) around "active" to no effect. Additionally, this is some code I'm upgrading, and the previous version (used same syntax here with same relevant DB structure) had, and still has, no problems with this. Furthermore, I've successfully accessed 'players' elsewhere without referencing "active".

 

[KingKaeru]

try [active]

--KK

 

[lomn75]

no go.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '[active]=1' at line 1

//edit: I should mention: I did change this from int to tinyint while rewriting, since it's just a boolean flag. Doesn't seem like that would make a difference, but....

 

[lomn75]

well, dammit, putting it back to INT fixed it. That's just stupid. Any idea why a one-bit flag failed? The docs clearly say that "bit", "bool", etc, are aliases for TINYINT(1), no "UNSIGNED" needed....

//edit: looks like the docs are wrong, or at least very confusing. "bit" is indeed an alias for "TINYINT(1)", but a normal 0/1 bit should be "TINYINT(1) UNSIGNED". Makes sense in hindsight but it's a lousy alias.

 

[KingKaeru]

That really is unusual as I use tinyint(1) often with no problems. Is the query you posted in the first post the exact query? Also, have you tried the query via the mysql client instead of php?

--KK

 

[lomn75]

The query was an exact copy.

Trying the query from phpMyAdmin worked fine with and without the UNSIGNED.

Don't really feel like punching commands directly into MySQL, it's late and I don't remember my syntax all that well.