|
|
I have a list in a database that I have categorised using a category field. I now want to retrieve all records but grouped by categories. how do i get my asp to loop through each category creating a recordset for each one rather than hard code a new recordset for each category i would rather it did it dynamically if thats possible ???
so eg
category travel
1 - B&B's records (recid 1)
2- rail info (recid 2)
category local info
1 - swimming baths (recid 3)
2 - museum info (recid 4)
etc etc
cheers
|
|
|
|
The only way to get a recordset per category is to do a seperate query per category . If you want to do just one query (which is what I try to do), then simply select your data sorted by category, then process the recordset in order and write code to handle when the category changes. Something like this:
select category _name, company_id, company_name
from company
order by category_name, company_name
Then as you move through the recordset keep track of the last category so you can know if the current category is the same or the start of a new category.
Does that help?
|
|
|
|
|
|
Thanks
So basically the answer is a new Recordset query for each category - probably easier to hand code rather than pick up dynamically ?
pain because I was hoping there was a loop technique I could have used
|
|
|
|
If you truly want a "recordset per category" then you'll have to run a query per category. However, depending on the size of your data and the speed of the db server, network, etc.....you may prefer to run one query, then process the data in your ASP.
Running one query per category is probably the easiest, but probably not the most efficient. I prefer to run one query, ordered by category. (Like the example query I showed in my previous reply.)
Then you can process the data sequentially kind of like this: (This is not tested code, I just typed it into this reply as an example of the logic.)
rowCnt = 0
while not rs.eof
rowCnt = rowCnt + 1
category = rs.fields("category")
company = rs.fields("company")
if category <> lastCategory then
if rowCnt > 1 then
'Enter logic to wrap up whatever you need to do for the previous category. For example, write out your end table row and table tags.
end if
Response.Write("<h1>" & Category & "</h1>" & vbcrlf)
lastCategory = category
end if
Response.Write(company & "<br>" & vbcrlf)
rs.MoveNext
wend
This logic loops through the recordset, and for each new category, it ends the previous section (unless it is the first category), writes out the new category as a heading, then lists the companies in that category, then moves onto the next category and so on. See?
|
|
|
|
|
|
|
|
|
|
serafina
Troy Wolf