CODEWITHSUNDEEP
sqlmysql
Ole Media
Ole Media

Reputation: 1642

Why does MySQL always return no data for this query?

I have a database in which a few fields are set as index. I don't know if this has to do with the problem or not, but when creating a query I get no results. Here is how I have things set in the database. The database has 90,000 records and this table is set like:

| fieldA  | fieldB | fieldC  | fieldD |

fieldA = index - CHAR
fieldB = index - CHAR
fieldC = index - CHAR
fieldD = index - VARCHAR

If I do:

SELECT * 
  FROM tableA 
 WHERE 'fieldD'='A LONG STRING WITH SYMBOLS AND CHARCTERS';

I get 0 results and I know that the value for fieldD exist and is there.

What am I doing wrong?

Upvotes: 0

Views: 437

Answers (7)

user170442
user170442

Reputation:

Throw away apostrophes around field name, You may use them but apostrophes around field names are opposite = "´"

SELECT * FROM tableA WHERE fieldD='A LONG STRING WITH SYMBOLS AND CHARCTERS'

Upvotes: 1

longneck
longneck

Reputation: 12226

the column does not have the data in it you think it does. try this:

first run this query:

select hex('100/80R16 M A39 50S')

then run this query:

select fieldD, hex(fieldD) from tableA

then compare the results from the first query to the appropriate row from the second query. i think you will find the hex() representations do not match.

if you can't tell why they don't match from the hex() output, post it here and i'll help you figure out why it's not working.

Upvotes: 0

Ole Media
Ole Media

Reputation: 1642

I don't know why but the following query did the trick. If any of you can explain why will be appreciated:

SELECT * FROM tableA WHERE completo LIKE ( '1008017 P MT/75 FRONT 52H%' ) LIMIT 0 , 30

I had to place a %' at the end of the string to work

Upvotes: 0

Phill Pafford
Phill Pafford

Reputation: 85308

BACKTICKS instead of single quotes

SELECT * FROM tableA WHERE `fieldD`='A LONG STRING WITH SYMBOLS AND CHARCTERS';

The backticks key is to the left of the 1 key

Upvotes: 0

TJ L
TJ L

Reputation: 24452

It looks to me like your problem is you're enclosing your column names with single quotes. This is causing MySQL to treat it as a string instead of a column name, so you are literally comparing the string 'fieldA' with long string. Remove the quotes from around the column name and you should be good to go.

SELECT * FROM tableA WHERE fieldD='A LONG STRING WITH SYMBOLS AND CHARCTERS';

Upvotes: 5

randomx
randomx

Reputation: 2377

There could be a problem with character encoding here. I would try this query and see what happens: SELECT * FROM tableA WHERE 'fieldD' like '%A LONG STRING WITH SYMBOLS AND CHARCTERS%';

Also, I noticed a typo on the word 'CHARACTERS'.

Upvotes: 1

Matt Wrock
Matt Wrock

Reputation: 6640

Not sure if this was a typo in your query above, but fieldD should not be in single quotes.

Upvotes: 0

Related Questions