CODEWITHSUNDEEP
phpmysqlsql
phantomCoder
phantomCoder

Reputation: 1587

MySQL SELECT query returning for integer and integer + string

My table name students 

uid  |  name
-----+-------
10   |  John
11   |  Smith

**Data types : **

uid  int(11)
name varchar(256)

My query : 

SELECT name FROM students WHERE uid = '10'

Returns : John

My 2nd query : 

SELECT name FROM students WHERE uid = '10someChar'

Returns : John

Why the second query returns John ?

Upvotes: 5

Views: 1927

Answers (2)

Mauricio Piber Fão
Mauricio Piber Fão

Reputation: 943

MySQL automatically converts numbers to strings as necessary, and vice versa.

It is also possible to convert a number to a string explicitly using the CAST() function.

Type Conversion

take a read.

MySQL ever tries to return something - even if it's the wrong type, he'll cast automatically.

You should filter at PHP to validate your business rule.

Postgresql should throw a exception

Upvotes: 1

Taryn East
Taryn East

Reputation: 27747

The uid column is integer, and the value you pass in the where clause is first coerced into an integer... and most integer-conversion algorithms just grab the first set of digits they can find in the string (and ignore anything non-matching after it)... thus it finds 10 and ignores the rest

Upvotes: 5

Related Questions