Alternate Record Paging. MySQL / ASP

[Jon T]

Before I start, here are two example URLs that display what I am doing now, and what I want to be doing.

Example Link of how it is working now, and is wrong (http://www.meyercountryfarms.com/search/results.asp?myState=AZ)

Example Link of the way I would Like it all to look (http://www.meyercountryfarms.com/wheretobuy/retailstorelist.asp)

I have created the following MySQL Statment to pull from two tables.

Table One: Stores - This table lists all main Retailers.
Table Two: Locations - This table lists all locations of a Retailer.

So, I match the storeID from stores to locations. When this match is good, a Retailer has Locations that can then be returned as a search result.

So, if a given Retailer or Store has multiple Locations, the results look like this.

Store 1, Location 1
Store 1, Location 2
Store 2, Location 1

I would prefer to look like.

Store 1, Location 1, Location 2
Store 2, Location 1

So now, instead of having one repeat reagion for the first example, I have to build a Logical 2 Repeat Reagion.

IF, a Store has a Location, Display the store, then all of its locations. Here is my current SQL Statement.

---- START MYSQL ----
SELECT stores.storeID, stores.Name, stores.URL, locations.storeID, locations.Location, locations.unitNUM, locations.Address1, locations.Address2, locations.City, locations.State, locations.Zip, locations.Phone, locations.Viewable

FROM stores, locations

WHERE stores.storeID = locations.storeID AND locations.State = 'valOne' AND locations.Viewable = '1'

ORDER BY stores.Name
---- END MYSQL ----

TYIA for any help in this matter.

[raf]

I don't see any recordsetpaging on these pages.

I also don't understand why you want to 'transpose' the recordset (turn rows into columns). Why do you need the locations all on one line, for that store? You can display the data as you wish when you loop through the recordset with your derver side scripting (ASP) You just ned to store the storeID in a variable, and at the start of your loop, check if the storeID from the record your processing then, had the same ID as the previous record (that ID is store inside the variable). After the check, you set this variable to the current records storeID.


In a db-driven webapplication, it would probably also make more sense, if you first display all stores, and when the client picks a store, that you then display the locations for that store. For instance through a dropdownmenu of the stores, with an onchange="submit();" that will then reload the page with the locations under the dropdown.
Or you could supplie 2 interdependent dropdowns with the stores and a dropdown with the locations. So that dropdown 1 fills dropdown 2 with relevant options, and that after an option from dropdown 2 is choosen, the details for that store-location are shown.

But maybe i'm completely off here.

[Jon T]

No, your not completly off. But I do like your final idea, only Display the main store information, then I could provide a link to the locations of that store.

The whole ideas was to display a store, then all of their locations, then loop to the next store and it's locations.

:thumbsup:

This is nice, you have given me a way to think around this one... Somethings I had not considered..

YOU DA MAN!

[raf]

Glad i inspired you :eek:

I always wanted to do a map thing where the map is dynamically build based on the values you selec from dropdowns (so there would be dots (links) where there are shops or whatever that meet your condition. But never had an opportunity to write one.
Maybe you can write it (and give it to me for free :))