...

View Full Version : Dividing & sorting records alphabetically



esap
07-06-2004, 06:47 PM
I am confused if I should be here, or in the mySQL forum, since it is a combination of the two (ASP and mySQL).

I have a very simple page that pulls the records from a mySQL database. The results are shown in a 2 coulmn table that goes left to right alphabetically. Problem is my employer wants the results to be 2 column, but descending alphabetically. I know I have to divide the records, but otherwise I havent a clue on how to display the results this way!

This is the code in use now:

<%
set oRS = getRS("SELECT * FROM charities WHERE active=1 order by charity_name")
iCount = 0
WHILE NOT oRS.EOF

If iCount = 0 Then Response.Write "<tr><td width='54'><img src='images/cdot.gif' width='54' height='1' alt='' border='0'></td>" End If
iCount = iCount + 1%>
<% IF oRS("charity_url") = "" then
%>
<td bgcolor="#FFFFCC" width="50%"><%=oRS("charity_name")%></td>
<%ELSE%>
<td bgcolor="#FFFFCC" width="50%"><a href="<%=oRS("charity_url")%>" target="_blank"><%=oRS("charity_name")%></a></td>
<%END IF%>
<%
If iCount = 2 Then Response.Write "<td width='54'><img src='images/cdot.gif' width='54' height='1' alt='' border='0'></td></tr>" END IF
oRS.MoveNext
IF iCount = 2 then iCount = 0
WEND
oRS.Close
%>

<%

'If NOT oRS.EOF then
' get_product = oRS("primary_key")
'Else
' get_product = "PRODUCT NOT FOUND"
'End If
set oRS = nothing
%>

This is an asp page and a mySQL database!

Any advice would be greatly appreciated!

raf
07-06-2004, 07:59 PM
SELECT * FROM charities WHERE active=1 order by charity_name DESC

esap
07-06-2004, 10:16 PM
Thanks! Thats definately a step in the right direction, but I still need to use two columns, fill the left column with half the records, but then fill the right column with the other half of the records. I think adding "DESC" will only create one column?

In case I am not building a good enough picture, this is the page in question: www.cars4causes.net/Charities.asp.

You can see the records right now go left to right in the columns instead of descending straight down, then back to the right column and descending down.

raf
07-06-2004, 10:29 PM
you need to use the MOD operator.


do while rsWhatever.EOF=false
If i MOD 2 = 0 then
' new row
else
'new column
end if
i = i + 1
loop

esap
07-07-2004, 05:37 AM
Thanks RAF - at the risk of sounding, umm...stupid, can you tell me where i would put that in relation to the code I posted at the start? :confused: I did all this website, with the exception of this page - if its not visual, I have a tough time.

raf
07-07-2004, 08:02 AM
my VBscript is kinda rusty, bit it shoudl be something like


do while oRS.EOF=false
If i MOD 2 = 0 then
Response.Write "<tr><td width=""54""><img src=""images/cdot.gif"" width=""54"" height=""1"" alt="""" border=""0""></td>"
end if
IF oRS("charity_url") = "" then
Response.Write "<td bgcolor=""#FFFFCC"" width=""50%"">" & oRS("charity_name") & "</td>"
ELSE
Response.Write "<td bgcolor=""#FFFFCC"" width=""50%""><a href=""" & oRS("charity_url") & """ target=""_blank"">" & oRS("charity_name") & "</a></td>"
END IF
If i MOD 2 = 0 then
Response.Write "<td width=""54""><img src=""images/cdot.gif"" width=""54"" height=""1"" alt="""" border=""0""></td></tr>"
end if
i = i + 1
loop


or alternatively (bit shorter, but not sure it's faster


dim printvalue
do while oRS.EOF=false
IF oRS("charity_url") = "" then
printvalue = "<td bgcolor=""#FFFFCC"" width=""50%"">" & oRS("charity_name") & "</td>"
ELSE
printvalue = "<td bgcolor=""#FFFFCC"" width=""50%""><a href=""" & oRS("charity_url") & """ target=""_blank"">" & oRS("charity_name") & "</a></td>"
END IF
If i MOD 2 = 0 then
Response.Write "<tr><td width=""54""><img src=""images/cdot.gif"" width=""54"" height=""1"" alt="""" border=""0""></td>" & printvalue & "<td width=""54""><img src=""images/cdot.gif"" width=""54"" height=""1"" alt="""" border=""0""></td></tr>"
end if
i = i + 1
loop



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum