Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

Country & Region Table from DB

Hello,

I am trying to create a footer for a villa company and ave an issue, which I think is easily solvable, but I simply can't get my head around it today...!

I have a table of Country Names in one field and the next field with regions, so my data looks like this:

CountryName    RegionName
France                 Cote d'Azur
France                 Dordogne
Spain                   Costa Blanca

etc.

I am trying to create a table that will list 5 colums and flow from one to the next, so the table is even in length across the page - i.e. the first column would start with the first country name, then list the regions of that country below.

I have taken the number of lines in the DB and divided by 5, so i know how many lines each column needs to be.

I have created a table with 5 columns and put a variable in each column: i.e. <%Column1Data%>, <%Column2Data%> etc. and I'm using a counter to know when a column is full and so to build up the next list, but I can't seem to assign the Column1Data variable to my DB loop...

I know it's something stupid as I've completely forgotten how to do this...

Here's my code and please don't laugh (too loudly!):

Set RS = DB.Execute("Select COUNT(ID) as NumRecs from CountryList where NumberOfProperties > 0")
NumRecs = RS("NumRecs")
RS.Close
Set RS = Nothing

Response.Write(NumRecs & "<BR>")

NumRecs = CInt(NumRecs / 5)

Response.Write(NumRecs & "<BR>")

i = 0
j = 1

ListName = "CountryList" & j
response.Write(ListName & "<BR>")

Set RS = DB.Execute("Select Distinct(Country) from CountryList Order By Country")
	While Not RS.EOF
	i = i + 1
	if i < NumRecs then
		"CountryList" & J = ("CountryList" & J) & RS("Country") & "<BR>"
		Response.Write(RS("Country") & "<BR>")
		Set RS2 = DB.Execute("Select * from CountryList where Country = '" & RS("Country") & "'")
			While Not RS2.EOF
			if i < NumRecs then
			"CountryList" & J = ("CountryList" & J) & "&nbsp;&nbsp;&nbsp;&nbsp;" & RS2("Region") & "<BR>"
			Response.Write(RS2("Region") & "<BR>")
			i = i + 1
			RS2.MoveNext
			else
			j = j + 1
			i = 0
			ListName = "CountryList" & j
			response.Write(ListName & "<BR>")
			end if
			Wend
		RS2.Close
		Set RS2 = Nothing
	RS.MoveNext
	else
	j = j + 1
	i = 0
	ListName = "CountryList" & j	
	response.Write(ListName & "<BR>")
	end if
	Wend

Open in new window


Help please!

Thanks very much!
0
Nico2011
Asked:
Nico2011
  • 2
  • 2
2 Solutions
 
Wayne BarronCommented:
OK, lets take a step back, and see what you are doing in the database first off.

Do you have a single table, or are you using 2 tables?
1 Table = Country
1 Table = Regions
Or, is it all in 1 single table?
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This could be a lot easier. Since you know there are 5 columns of data.  Just  make  a div that will have enough width to repeat 5 times before wrapping.     I am assuming you want to end up with something like this:
http://jsbin.com/ahajew/1/edit
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%

Set RS = DB.Execute("Select Country, Region from tblCountries ORDER BY Country, Region") ' make up your own sql.  We just need the 2 fields.

if not rs.eof or not rs.bof then
	arrData=rs.getrows() 'fast
end if



%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Untitled Document</title>
<style type="text/css">
.footer {
	width: 600px;
}
  div.country{
    width:600px;
    font-size:16px;
	font-weight: bold;
 }
div.region {
	
	float: left;
	width: 110px;
	margin-right: 10px;
}
</style>
</head>

<body>

<div class="footer">
<%
currCountry=""
 For r = LBound(arrData, 2) To UBound(arrData, 2)
    	Country = arrData(0, r)
		Region = arrData(1, r)
		
		if Country<>currCountry then
			response.write "<div class=""country"">"&Country&"</div>"
			currCountry=Country' 
		end if
		
		response.write "<div class=""region"">"&Region&"</div>"
next
		
%>

</div>
</body>
</html>

Open in new window

0
 
Nico2011Author Commented:
Thanks Both.  I have given Padas more points as he came back with a full solution, although I am not using his answer, he did suggest an alternate layout for me, which I am going to use.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Thank you.  If you need to have this in an actual table, just substitute divs for td and tr.  Below works if you actually have regions divisible by 5.  I would just add some logic that tests for the count of columns and if the region is changing and there are less then 5 columns, add a <td colspan=<%=theDifferenceNeeded%>> to keep the tables in line.
From there you can give the td's some class and use css.

<table>
<%
currCountry=""
colCounter=0

 For r = LBound(arrData, 2) To UBound(arrData, 2)
          Country = arrData(0, r)
            Region = arrData(1, r)
            
            if Country<>currCountry then
                       
                  response.write "<tr><td colspan=""5"" class=""country"">"&Country&"</td></tr>"
                  currCountry=Country'
                        colCounter=0
            end if
            colCounter=colCounter+1
               
                 if colCounter=1 then
                      response.write "<tr>"
                 end if
                 if colCounter>5 then
                       response.write "</tr>"
                        colCounter=0
                 end if
                   response.write "<td class=""region"">"&Region&"</td>"
next
      
%>

</table>
0
 
Nico2011Author Commented:
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now