duplicates recrods from database search

Forum|Forum|19 years ago
June 19, 2006
7 replies
526 views

Here is my resulting page from a selet statement

http://www.resistolhat.com/raw/retailers/locator_results.asp?select=OK&Submit=search

There are a few cities that come up more than twice, I need to only show each city once? Is there are a statement for this?

Thanks,
Fred

Server side applications

This topic has been closed for replies.

J

jeremyluby

Participating Frequently

more like select distinct cities from . . . .

T

tacomafredAuthor

Participating Frequently

ok, I will keep trying. Thank you for your time.

J

jeremyluby

Participating Frequently

well I have no idea what you are going to call your variable in the URL.

Assuming that you have an auto-incremented field in your table a select distinct * will return all the items, because no two rows can be the same.

you will need to use "select distinct field1, field2, field3 from . . . "

T

tacomafredAuthor

Participating Frequently

ok, I think I got it...

locator_rs.Source = "SELECT DISTINCT STATE FROM Retailers WHERE State = '$_GET['city_variable_from_URL', "'", "''") + "'"

Is everything else ok?

J

jeremyluby

Participating Frequently

you could pass the state in the url to the results page

then make a dynamic select statement on the results page like so:

select distinct city_name from table_name where state = $_GET['city_variable_from_URL'];

T

tacomafredAuthor

Participating Frequently

Here is the orginal select statement from the resutls page....

locator_rs.Source = "SELECT * FROM Retailers WHERE State = '" + Replace(locator_rs__MMColParam, "'", "''") + "'"

Here is what I think you told me to do....

locator_rs.Source = "SELECT DISTINCT * FROM Retailers WHERE State = '$_GET['city_variable_from_URL', "'", "''") + "'"

J

jeremyluby

Participating Frequently

Well since your database shows the entries more than once you cannot avoid using the distinct.

Have you considered normalizing your tables?

T

tacomafredAuthor

Participating Frequently

So i guess I will need to build a select statement? I'm just not sure how to do this.

Do I bult it on the map page or the results page? I have each state linked with a image map, so I will have to build that select satement for each state?

T

tacomafredAuthor

Participating Frequently

The previsous page is here

http://www.resistolhat.com/raw/locator.asp

Can i do this while avoiding an actual select statement, can I just hard code it into the actual link?

I want the user to select the state and the results to display all of the cities from the selected state

Thank you for your time

Fred

J

jeremyluby

Participating Frequently

use the word distinct in your select statement

such as:

select distinct city_name from table where blah blah blah

N

Newsgroup_User

Inspiring