SQL Query Not Returning Results, But Data There!

goclimb

I'm stumped with something. I have a table in my database called 'parks.' It has four fields - id, url, sitename, location. There is data in the table, and if I do 'SELECT * FROM parks WHERE 1' query I get everything in the table back. Here is an example of the data:

ID 	URL 	SiteName 	Location
1 	[url]http://www.nps.gov/alag/[/url] 	Alagnak Wild River 	Alaska
2 	[url]http://www.nps.gov/ania/[/url] 	Aniakchak National Monument & Preserve 	Alaska
3 	[url]http://www.nps.gov/bela/html/vvcenter.htm[/url] 	Bering Land Bridge National Preserve 	Alaska
4 	[url]http://www.nps.gov/noaa/[/url] 	Cape Krusenstern National Monument 	Alaska
5 	[url]http://www.nps.gov/dena/home.html[/url] 	Denali National Park and Preserve 	Alaska
6 	[url]http://www.nps.gov/gaar/webvc.htm[/url] 	Gates of the Arctic National Park and Preserve 	Alaska
7 	[url]http://www.nps.gov/glba/evc.htm[/url] 	Glacier Bay National Park and Preserve 	Alaska
8 	[url]http://www.nps.gov/katm/[/url] 	Katmi National Park and Preserve 	Alaska
9 	[url]http://www.nps.gov/kefj/[/url] 	Kenai Fjords National Park 	Alaska

However, if I run a query like :

SELECT * FROM parks WHERE Location = 'Alaska'

I get an empty results set! But, clearly, from the data above, there are entries where Location = Alaska!

Anybody have any thoughts on this?

Thanks!

Mark

NogDog

Could some white-space (or some non-printing characters) somehow have gotten included with the location, so that it's something like 'Alaska....' (where the dots are spaces)? Maybe try a LIKE to see if this could be the case:

. . . WHERE Location LIKE '%Alaska%';

goclimb

NogDog;10931557 wrote:
Could some white-space (or some non-printing characters) somehow have gotten included with the location, so that it's something like 'Alaska....' (where the dots are spaces)? Maybe try a LIKE to see if this could be the case:
. . . WHERE Location LIKE '%Alaska%';

That actually worked! I'm not sure what got into the Location data that is causing this problem...looks like plain text to me. But, at least the query works now. Thanks!

johanafm

If you don't have to use "Like '%...%'", don't.

So, to get rid of your whitespaces

UPDATE parks SET Location = TRIM(Location)

goclimb

johanafm;10931615 wrote:
If you don't have to use "Like '%...%'", don't.

So, to get rid of your whitespaces
UPDATE parks SET Location = TRIM(Location)

I ran the TRIM command, then ran the following query, again getting an empty result:

Select * FROM parks WHERE Location = 'Alaska'

This is very odd!

Weedpacket

Try "select distinct location from parks" and output all the results as "[".$row['Location."]" so that you can see if there is any whitespace remaining. It's possible that a non-breaking space is there (trim() won't strip those out).

You certainly will want to clean up the data in the table so that your first version of the query would work: the others would clobber your DBMS's attempt to use indexes to optimise its search.

_newbie

how about:
SELECT * FROM parks WHERE Location = "Alaska"

I had problems using single quotes to retive strings in the past, fixed by changing to double. Just a thought.

laserlight

$newbie wrote:
I had problems using single quotes to retive strings in the past, fixed by changing to double.

It is correct to use single quotes to quote strings in SQL. The use of double quotes is a vendor specific extension; double quotes are otherwise normally used to quote identifiers instead.

tomhath

How did you load the data into that table? Obviously you have some non-printable characters in there, I've seen this many times. You'll need to display all of the characters individually to see what they are. See the mysql HEX() or ASCII() functions.