[SOLVED] why is query returning empty set???

[drumhrd]

building test DB for some stuff..put some test data in and did a simple query.

SELECT bandname FROM BANDS WHERE band_number = 25;

+-----------------+

| bandname        |

+-----------------+

|  Metal Militia  |

+-----------------+

1 row in set (0.00 sec)

 

 

 

Thought ok great..lets do the reverse and get the band_number where the bandname = "Metal Militia"

 

 

 

I get empty set

 

 

SELECT band_number FROM BANDS WHERE bandname = "Metal Militia";

Empty set (0.00 sec)

 

[warhead2020]

SELECT band_number FROM BANDS WHERE bandname = "Metal Militia";

 

 

try this...

 

SELECT band_number FROM BANDS WHERE bandname = 'Metal Militia'

 

 

try use single quote instead of double quote

[drumhrd]

It's a no go

 

SELECT band_number FROM BANDS WHERE bandname = 'Metal Militia';

 

Empty set (0.00 sec)

 

It seems to be doing this for everything..here is another piece of the test data doing the same thing

 

 

mysql> SELECT bandname FROM BANDS WHERE band_number = 28; 

+------------------------+

| bandname              |

+------------------------+

|  Guitars and Tractors  |

+------------------------+

1 row in set (0.00 sec)

mysql> SELECT band_number FROM BANDS WHERE bandname = 'Guitars and Tractors';

Empty set (0.00 sec)

 

[warhead2020]

mysql> SELECT band_number FROM BANDS WHERE bandname = 'Guitars and Tractors';

 

 

try this...

 

 

SELECT band_number,bandname FROM BANDS WHERE bandname = 'Guitars and Tractors';

 

add one more column in the select column statement....

 

[PFMaBiSmAd]

How was your data entered into the database? It might have white-space/non-printing characters either before or after (probably new-line characters) the names.

[drumhrd]

nope still not working

 

mysql> SELECT band_number,bandname FROM BANDS WHERE bandname = 'Guitars and Tractors';

Empty set (0.00 sec)

 

 

here is how I added data to the DB..don't laugh..it's just test data.

 

INSERT INTO BANDS (bandname, email, myspace, facebook, bio, genre, image, css_file) VALUES (" Metal Militia "," mailto:someband1@someplace.com "," http://www.myspace.com/something "," http://www.facebook.com/someband "," Metal Milita      We stole our name from Metallica, because we are posers.  We really play polka, but we want to be known as a metal band…yeha I like country..woo hoo..your momma's fat "," metal "," https://www.artists2you.com/images/ "," https://www.artists2you.com/css/221.css ")

 

 

 

 

The only think I can do is use a LIKE clause to find it

SELECT band_number FROM BANDS WHERE bandname LIKE '%Metal Militia%' ;

+-------------+

| band_number |

+-------------+

|          25 |

+-------------+

1 row in set (0.00 sec)

 

[PFMaBiSmAd]

I see excessive white-space both before and after the quotes - " Metal Militia "

 

In programming, " Metal Militia " and "Metal Militia" are two completely different pieces of data because computers only do exactly what their code and data tells them to do.

[drumhrd]

well after really thinking about the input method..I realized I have leading and trailing spaces

 

mysql> SELECT band_number FROM BANDS WHERE bandname = ' Metal Militia ';

+-------------+

| band_number |

+-------------+

|          25 |

+-------------+

1 row in set (0.00 sec)

 

 

 

ANY IDEA ON HOW TO STRIP THEM OUT?

 

 

THANKS FOR THE HELP GUYS

 

[PFMaBiSmAd]

Don't put the extra spaces in when the data is inserted, that wastes storage space and slows down any operation that accesses the data.