Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL SELECT query returning for integer and integer + string

Tags:

sql

php

mysql

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 ?

like image 593
phantomCoder Avatar asked Aug 20 '14 05:08

phantomCoder


People also ask

What is %s and %D in MySQL?

12 years, 10 months ago. it's for php to know how to handle the parameters, %d – the argument is treated as an integer, and presented as a (signed) decimal number. %s – the argument is treated as and presented as a string. in your examples, $slug is a string and $this->id is an integer.

How do I select only the value of an integer in MySQL?

Syntax to check if the value is an integer. select yourColumnName from yourTableName where yourColumnName REGEXP '^-?[0-9]+$'; The query wherein we have used regular expression. This will output only the integer value.

How do you assign a selected value to a variable in MySQL?

The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.


2 Answers

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

like image 111
Taryn East Avatar answered Sep 28 '22 16:09

Taryn East


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

like image 25
Mauricio Piber Fão Avatar answered Sep 28 '22 16:09

Mauricio Piber Fão