smehsoud smehsoud - 1 year ago 65
SQL Question

Sql returns empty row on combinning primary key with another value in where clause

I am getting empty row on following sql

SELECT * FROM flyers WHERE fId='6' AND 'userId'='400'

MySQL returned an empty result set

(i.e. zero rows). (Query took 0.0004 sec)

But when i use

SELECT * FROM flyers WHERE fId='6'

Showing rows

0 - 0 (1 total, Query took 0.0005 sec).

As i got my result and that is correct using only primary key.

But i does not know why mysql returns empty row on using AND with primary key.

Note:- fId is flyer table primary key.

Answer Source

Hi In your query you have only one mistake that you used single quotes around uerId use back ticks instead or nothing.

SELECT * FROM flyers WHERE fId='6' AND userId='400'  

SELECT * FROM flyers WHERE fId=6 AND userId=400// safe not to use quotes

but i suggest not to use quotes around numbers for below reason

Real issue comes down to type casting. When you put numbers inside quotes, it is treated as a string and MySQL must convert it to a number before it can execute the query. While this may take a small amount of time, the real problems start to occur when MySQL doesn't do a good job of converting your string. For example, MySQL will convert basic strings like '123' to the integer 123, but will convert some larger numbers, like '18015376320243459', to floating point. Since floating point can be rounded, your queries may return inconsistent results