Keep getting empty result

[Rocky86]

Hi people pls help me having some trouble with the SQL I try to test the SQL to get a value but it does not work this is my table name location:

uid uname lat
1 Peter Collins 1.378
2 Mary Hopenson 1.298

I trying to get the the name Peter Collins to be display so I have the following code input:

SELECT uname
FROM location
WHERE location.lat = '1.378'
LIMIT 0 , 30;

but when I run i got this "MySQL returned an empty result set"
why is it like that I thought my code say get uname from table location where the lat is = 1.378 that mean it should display Peter Collins but instead it return nothing plss help me thx!

[r035198x]

Hi people pls help me having some trouble with the SQL I try to test the SQL to get a value but it does not work this is my table name location:

uid uname lat
1 Peter Collins 1.378
2 Mary Hopenson 1.298

I trying to get the the name Peter Collins to be display so I have the following code input:

SELECT uname
FROM location
WHERE location.lat = '1.378'
LIMIT 0 , 30;

but when I run i got this "MySQL returned an empty result set"
why is it like that I thought my code say get uname from table location where the lat is = 1.378 that mean it should display Peter Collins but instead it return nothing plss help me thx!

It is possible that you have spaces in the lat field. Try using

where lat like '%1.378%' or use the trim function.

[code green]

If the second field 1.378 is a numeric field then remove the quotes from the where clause

[Rocky86]

hello guys thx for the reply but I try both the matter still getting the same result it just return:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)SQL query:SELECT uname
FROM location
WHERE location.lat = '%1.378%'
LIMIT 0 , 30;

any suggestion or feedback?

[r035198x]

hello guys thx for the reply but I try both the matter still getting the same result it just return:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)SQL query:SELECT uname
FROM location
WHERE location.lat = '%1.378%'
LIMIT 0 , 30;

any suggestion or feedback?

Don't use = when using wildcards. See my reply again it uses like. As in
[CODE=sql]SELECT uname
FROM location
WHERE location.lat like '%1.378%'
LIMIT 0 , 30;[/CODE]
Besides, isn't that supposed to be a numerical field?

[Rocky86]

thx for yr help it reallx works!! but I now having another problem this time is to show the district name when the location table colume location.lat and location.lng value is between the district.startl at and district.endlat AND district.startl ng and district.endlng of the district table
it should display the district name this is the table:

District Table
districtno districtname startlat endlat startlng endlng
56 Ang Mo Kio 1.394 1.360 103.820 103.877
57 Bishan 1.363 1.343 103.820 103.848


Location Table
uid uname lat lng
1 Peter Collins 1.378 103.807
2 Mary Hopenson 1.298 103.853

I trying to display the districtname from the table when the location table lat value is between the startlat and endlat of the district table and lng of location table is between startlng and endlng for example from location table the lat is 1.378 and lng is 103.807 if it is between the startlat 1.394 and endlat 1.360 and the lng is between the startlng 103.820 and endlng 103.877 it would display the district name "Ang Mo Kio"
this is code I come out with but it does not seen to work plss help me thx!!

SELECT districtname FROM location,distri cts
WHERE location.lat BETWEEN district.startl at AND districts.endla t AND location.lng
BETWEEN district.startl ng AND districts.endln g

but instead I get empty result is that anything wrong with my code?

[r035198x]

thx for yr help it reallx works!! but I now having another problem this time is to show the district name when the location table colume location.lat and location.lng value is between the district.startl at and district.endlat AND district.startl ng and district.endlng of the district table
it should display the district name this is the table:

District Table
districtno districtname startlat endlat startlng endlng
56 Ang Mo Kio 1.394 1.360 103.820 103.877
57 Bishan 1.363 1.343 103.820 103.848


Location Table
uid uname lat lng
1 Peter Collins 1.378 103.807
2 Mary Hopenson 1.298 103.853

I trying to display the districtname from the table when the location table lat value is between the startlat and endlat of the district table and lng of location table is between startlng and endlng for example from location table the lat is 1.378 and lng is 103.807 if it is between the startlat 1.394 and endlat 1.360 and the lng is between the startlng 103.820 and endlng 103.877 it would display the district name "Ang Mo Kio"
this is code I come out with but it does not seen to work plss help me thx!!

SELECT districtname FROM location,distri cts
WHERE location.lat BETWEEN district.startl at AND districts.endla t AND location.lng
BETWEEN district.startl ng AND districts.endln g

but instead I get empty result is that anything wrong with my code?

1.) Is the table name district or districts? Correct that in your code.
2.) Use ( ) to make your code more readable
3.) The problem could be the same as the first one. Trim your values first as in
where trim(location.l at) e.t.c

[Rocky86]

hi thx for the reply I already check through the 3 thing you state for me but still I getting the same error again this is the code:


MySQL returned an empty result set (i.e. zero rows). (Query took 0.0199 sec)
SQL query:
[code=sql]
SELECT districtname
FROM location, districts
WHERE location.lat
BETWEEN districts.start lat
AND districts.endla t
AND location.lng
BETWEEN districts.start lng
AND districts.endln g
LIMIT 0 , 30
[/code]

or does it have anything to do with the data type? right now my lat,lng,startla t,endlat and startlng and endlng is all in varchar data type or I need to change into decimal or float format?

[r035198x]

hi thx for the reply I already check through the 3 thing you state for me but still I getting the same error again this is the code:


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

SQL query:
[code=sql]SELECT districtname
FROM location, districts
WHERE location.lat
BETWEEN districts.start lat
AND districts.endla t
AND location.lng
BETWEEN districts.start lng
AND districts.endln g
LIMIT 0 , 30
[/code]

or does it have anything to do with the data type? right now my lat,lng,startla t,endlat and startlng and endlng is all in varchar data type or I need to change into decimal or float format?

No you did not. There are no brackets ( ) and you still have not used the trim function anywhere. Whether you should use varchar or number depends on the data that you want to store. If the data is going to be all numbers then use numbers.

[Rocky86]

No you did not. There are no brackets ( ) and you still have not used the trim function anywhere. Whether you should use varchar or number depends on the data that you want to store. If the data is going to be all numbers then use numbers.

thx for your reply but I don't understand what you mean by no brackets( ) and the trim function can show me example of it? thx alot!! I will change as soon as I know what you mean

[r035198x]

thx for your reply but I don't understand what you mean by no brackets( ) and the trim function can show me example of it? thx alot!! I will change as soon as I know what you mean

I've already showed that in an example.
For the brackets it's good to write as

[CODE=sql]select tableColumn from tableName where (c1 between 1 and 5) and (c2 between 5 and 7); [/CODE]

So you don't have c1 between 1 and 5 and c2 between 5 and 7 which is slightly less readable.

For the trim function just trim all your columns as they are retrieved from the database.
[CODE=sql]select columnName from tableName where trim(columnName ) = "someValue" ;[/CODE]

[Rocky86]

For the trim function just trim all your columns as they are retrieved from the database.

Code: ( sql )
SELECT columnName FROM tableName WHERE trim(columnName ) = "someValue" ;

what is trim function what does it do?

Does the BETWEEN function work also with varchar type or must it be integer or decimal type?

[Rocky86]

Just curious I am suppose to be comparing the value and if the value is between the districts.start lat and districts.endla t then return the districts name shouldn't I be using the <,> operator instead of BETWEEN operator?

[r035198x]

Just curious I am suppose to be comparing the value and if the value is between the districts.start lat and districts.endla t then return the districts name shouldn't I be using the <,> operator instead of BETWEEN operator?

Trim removes leading a and trailing spaces (see the link that I gave you for it). Between works with varchar as well. Either you use < and > or you use between. They'll give you the same results.