| Author |
 Topic  |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
 Posted - 30 December 2004 : 06:03:16
|
Okay, it ís somewhat related to the forum, but as it concerns a highly modded forum, I think this cat is the right place to post this question.
The thing is this ; I've got a frontpage/shortnews section on oxle.
These section show posts from specified forums in a different format.
What started as a small list, keeps on growing.
I've limited the Select statement to only show the TOP 100 (sorted on T_DATE), if there's no year selected. (by means of ?year=2003 for example).
If a year is selected, there's no TOP statement, and it shows all topics from thát year.
Example: http://www.oxle.com/portal_content.asp?view=all&year=2003
What I now want to do is this ; if you visit http://www.oxle.com/portal_content.asp?view=all it shows the last 100 topics, and there's no visible clue that there you can view all topics of 2004 and 2003.
I can hardcode these links into the page, but I want to automate that, so that in 2 days, it also shows a link for 2005.
I think I need to use a seperate select statement that checks the highest date, and the lowest date, and create a FOR year = high to low to create the links for 2003, 2004 and 2005.
But..I don't know how that SQL statement should be build...
Any suggestions would be very welcome.  |
portfolio - linkshrinker - oxle - twitter |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 December 2004 : 21:53:27
|
| I don't quite understand what you want but it seems to me that what you want must be accomplished through ASP and SQL and not through SQL exclusively. What you probably need is a statement that gets you the minimum and maximum values for year (or date, that really depends on the DB you are using) and then you will need to use those values in ASP to build the links you need. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 31 December 2004 : 03:35:40
|
exactly! I need to determine (through a seperate SQL statement) what the year of the newest post is, which fills YearHigh, and what the year of the oldest post is which fills YearLow.
Then I could use
For i = YearHigh to YearLow
response.write "<a href="&year=""" & i & """>" & i & "</a>"
next
Something like that...But I cannot seem to find the way to determine the YearHigh and YearLow values |
portfolio - linkshrinker - oxle - twitter |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
|
|
cripto9t
Average Member
USA
881 Posts |
Posted - 31 December 2004 : 08:56:25
|
This may help you. It displays archived posts by the year and months.
"SELECT DISTINCT" is the key.
Similar to the archive links you see in some web logs.
2005
Jan
Feb
etc...
2004
Jan
Feb
etc...
p.s. I'm not sure about the year part(all my posts are in the same year).
<%
'### Get Years and loop through
strSql = "SELECT DISTINCT LEFT(T.T_DATE,4)"
strSql = strSql & " FROM " & strTablePrefix & "A_TOPICS T"
strSql = strSql & " WHERE T.FORUM_ID = " & Forum_ID
strSql = strSql & " ORDER BY left(T.T_DATE,4) DESC"
set rsYear = Server.CreateObject("ADODB.Recordset")
rsYear.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
if rsYear.EOF then
recYearCount = ""
else
allYearData = rsYear.GetRows(adGetRowsRest)
recYearCount = UBound(allYearData,2)
end if
rsYear.close
set rsYear = nothing
if recYearCount = "" then
' do nothing
else
Response.Write " <div id=""mSecWrap"" style=""width:100%; background-color:" & strForumCellColor & "; border:1px solid " & strTableBorderColor & "; margin-bottom:20px; padding-bottom:10px;"">" & vbNewLine & _
" <div id=""mtitle"" style=""background-color:" & strCategoryCellColor & "; padding:2px; margin-bottom:10px; border-bottom:1px solid " & strTableBorderColor & "; text-align:center;"">" & vbNewLine & _
" <font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strCategoryFontColor & """><span style=""font-weight:bold;"">Archives</span>" & vbNewLine & _
" </font>" & vbNewLine & _
" </div id=""mtitle"">" & vbNewLine
yT_DATE = 0
for iYear = 0 to recYearCount
A_Year = allYearData(yT_DATE,iYear)
Response.Write " <div id=""arcYear"" style=""text-align:center; text-decoration:underline; font-weight:bold;""><font size=""" & strDefaultFontSize & """ face=""" & strDefaultFontFace & """ color=""" & strDefaultFontColor & """>" & A_Year & "</font>" & vbNewLine & _
" </div>" & vbNewLine
'### Get Months and Loop through
strSql = "SELECT DISTINCT mid(T.T_DATE,5,2)"
strSql = strSql & " FROM " & strTablePrefix & "A_TOPICS T"
strSql = strSql & " WHERE T.FORUM_ID = " & Forum_ID
strSql = strSql & " AND left(T.T_DATE,4) = " & A_Year
strSql = strSql & " ORDER BY T.T_DATE ASC"
set rsMonth = Server.CreateObject("ADODB.Recordset")
rsMonth.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
if rsMonth.EOF then
recMonthCount = ""
else
allMonthData = rsMonth.GetRows(adGetRowsRest)
recMonthCount = UBound(allMonthData,2)
end if
rsMonth.close
set rsMonth = nothing
if recYearCount = "" then
' do nothing
else
mT_DATE = 0
for iMonth = 0 to recMonthCount
A_Month = allMonthData(mT_DATE,iMonth)
A_Date = A_Year & A_Month
Response.Write " <div id=""arcMon"" style=""text-align:center; font-weight:bold;""><font size=""" & strFooterFontSize & """ color=""" & strDefaultFontColor & """ face=""" & strDefaultFontFace & """>" & vbNewLine & _
" <a href=""entry.asp?ARCHIVE=true&A_DATE=" & A_Date & "&FORUM_ID=" & Forum_ID & """>" & Monthname(A_Month) & "</a></font>" & vbNewLine & _
" </div>" & vbNewLine
next '### next Month
end if
next '### next Year
Response.Write "</div>" & vbNewLine
end if
%> |
_-/Cripto9t\-_ |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 December 2004 : 09:09:55
|
With Access 2000 you need to get the maximum and minimum values for the post date and then use the Year() VbScript function to get the year. An SQL statement to get the top year is:
SELECT MAX(T.T_DATE) As MaxDate FROM FORUM_TOPICS T;
For the minimum date use MIN instead of MAX.
Once you get the date values, as I said, use the Year function to get the year value for the maximum and minimum years. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 31 December 2004 : 09:22:51
|
Mmmm...I think this will be something for next year.
I've created this piece of 'code':
strSQLYearLowHig = "SELECT MAX(T.T_DATE) As MaxDate FROM FORUM_TOPICS WHERE "
strSQLYearLowHig = strSQLYearLowHig & " FORUM.FORUM_ID = " & strNewslinksForum
Set objRec2 = Server.CreateObject("ADODB.RecordSet")
response.write(strSQLYearLowHig)
set objRec2 = my_Conn.Execute(strSQLYearLowHig)
It gives me this:
http://www.oxle.nl/shortnews2.asp |
portfolio - linkshrinker - oxle - twitter |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 December 2004 : 14:25:49
|
If you have a look at my SQL statement, there is an alias being used for FORUM_TOPICS:
SELECT MAX(T.T_DATE) As MaxDate FROM FORUM_TOPICS T
You forgot to include the alias. Also you are using a reference to a FORUM table that is not included in the FROM clause
The correct SQL statemente should be:
SELECT MAX(T.T_DATE) As MaxDate FROM FORUM_TOPICS T WHERE T.FORUM_ID=...
Also, beware that T_DATE is a string with the date format used by Snitz. To get the year, you can simply use the Left VbScript function, or convert the string to a date, with StrToDate and then use Year to get the desired year from the date value. I forgot this little detail but testing the query in Access quickly reminded me about it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
| |
Topic |
|
Topic Locked
