CODEWITHSUNDEEP
mysql
JVE999
JVE999

Reputation: 3517

MYSQL UPDATE with WHERE= is not selecting the correct entries

My database code is 

SELECT `Title`,`Rating`,`Times_Played` from audio WHERE 'Username'='Hamburger7'

There are no entries where the username is equal to Hamburger7, but it selects two entries anyway. Instead of choosing entries that match the condition, it chooses the ones that are equal to '0', (as opposed to NULL).

The output on my PHP admin page, where the server settings are, is:

SELECT  `Title` ,  `Rating` ,  `Times_Played` 
FROM audio
WHERE  `Username` =  'Hamburger7'
LIMIT 0 , 30

Upvotes: 1

Views: 54

Answers (3)

spencer7593
spencer7593

Reputation: 108400

The datatype of the Username column is a numeric type, probably INT. But definitely not CHAR or VARCHAR.

MySQL is converting your string literal 'Hamburger7' into a datatype that matches the column. And that converts to 0 in terms of INT.

Your query is effectively: 

WHERE  `Username` = convert_string_literal_to_integer('Hamburger7')

which is equivalent to

WHERE  `Username` = 0

(Oh the joys of convenient, implicit datatype conversions!)

Upvotes: 1

Andy Lester
Andy Lester

Reputation: 93676

I think the problem is that you have Username in single quotes in the query, which means that you MySQL sees it as a literal string and not a column name.

SELECT `Title`,`Rating`,`Times_Played` from audio WHERE 'Username'='Hamburger7'

Change it to

SELECT `Title`,`Rating`,`Times_Played` from audio WHERE `Username`='Hamburger7'

Or better still

SELECT Title,Rating,Times_Played from audio WHERE Username='Hamburger7'

because you don't need to put column names in backticks unless the column name is a reserved word, and making your column names the same as reserved words is a Bad Idea anyway.

Upvotes: 2

exussum
exussum

Reputation: 18550

SELECT `Title`,`Rating`,`Times_Played` from audio WHERE `Username`='Hamburger7'

your comparing the string Username to the string Hamburger7 not the column username

Upvotes: 2

Related Questions