MySQL Problem

Chardee MacDennis

right so i have a database with US state names, lng, lat, city names and zip codes. I am trying to search it by city name but when i do i get an empty set - the only way i get a return on any search is by Zip Code. any ideas what i can do to get around this?

Adam

BastardPrince wrote: »

right so i have a database with US state names, lng, lat, city names and zip codes. I am trying to search it by city name but when i do i get an empty set - the only way i get a return on any search is by Zip Code. any ideas what i can do to get around this?

You shouldn't need to "get around this". Post your code, it's just a problem with your query I'd say. No reason why you should get an empty set if the data exists.

seamus

Can you give an example of a query you're running as well as the structure of the table(s)?

Chardee MacDennis

so table is as follows:

ZIPCODE INT
STATEABV VARCHAR(5)
LAT VARCHAR(30)
LNG VARCHAR(30)
CITY VARCHAR(30)
STATE VARCHAR(30)

This works:

SELECT * FROM `USCITIESNEW` WHERE `ZIPCODE`=94704

and i get

94704  	 CA  	37.868575  	-122.25855  	 Berkeley  	 California

for this

SELECT *  FROM `USCITIESNEW` WHERE `CITY` LIKE 'Berkeley'

and this

SELECT *  FROM `USCITIESNEW` WHERE `CITY` = 'Berkeley'

get this

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0005 sec)

Adam

Well since it's not an error, are you positive that query should return a result? Is it spelled correctly in your query and in the database?

Chardee MacDennis

i copied the text 'Berkeley' from the first result!!

seamus

Could be a whitespace issue.

what about

SELECT *  FROM `USCITIESNEW` WHERE `CITY` LIKE '%Berkeley%'

Chardee MacDennis

seamus wrote: »

Could be a whitespace issue.

what about

SELECT *  FROM `USCITIESNEW` WHERE `CITY` LIKE '%Berkeley%'

BAM! thats got it thanks! is there anyway to rectify this issue, or do i have to search with the % from now on?

seamus

I would consider this to be a form of data corruption - probably caused by the initial import of data into the database (if there was one).

Basically, there is whitespace (spaces, tabs, CRLFs) at one or both ends of the city name. You can use the % to search in future, but it's not ideal - your best approach would be to try clean up the data corruption.

Chardee MacDennis

seamus wrote: »

I would consider this to be a form of data corruption - probably caused by the initial import of data into the database (if there was one).

Basically, there is whitespace (spaces, tabs, CRLFs) at one or both ends of the city name. You can use the % to search in future, but it's not ideal - your best approach would be to try clean up the data corruption.

well there are over 33,000 records and it was a pain to import them, think i may just leave it. thanks for all the help!

dkell

A problem you might run into using % is that it's a wildcard; so if you've got two similar records (e.g. 'Berkeley' and 'West Berkeley') you're going to both results returned.

As you can't clean the data, have you try using the Trim function to chop any whitespace when doing the query?

Chardee MacDennis

dkell wrote: »

A problem you might run into using % is that it's a wildcard; so if you've got two similar records (e.g. 'Berkeley' and 'West Berkeley') you're going to both results returned.

As you can't clean the data, have you try using the Trim function to chop any whitespace when doing the query?

yup using trim thanks!

cgarvey

Use trim to update the database then .. make your queries slightly quicker from then on