...

View Full Version : Help with SQL for Random Record in Access.



pixelEngine
02-18-2007, 08:11 PM
Hello, I have been bouncing around the forum for over an hour now trying to figure out how to return just 1 random record from my Access DB based on only 1 condition ( featured=yes ).

I can easily generate a random number, but to generate a random # based on the # of records in the DB poses another issue for me. I COULD create another recordset and do a COUNT on it - but then I have 2 recordsets open and I would like to avoid this.

Here is what I currently have:



' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1
strSQL = "SELECT TOP 1 *, Rnd(" & 1 * (intRandomNumber) & ") FROM properties ORDER BY 1"
Set RS = cn.Execute(strSQL)


So above I need to figure out how to use the actual # of records that are flagged as 'featured=yes' instead of the '1000' where I set the intRandomNumber variable.

And then my next question is how do I figure out the pointer to that record?

Basically I am just trying to create a Featured Listing that will display a random listing everytime the page is loaded.

Any help would be great, like I said I have looked through I would bet all of the relavant articles on this forum to no ado.

Thanks !!!!!

graficus
02-18-2007, 08:36 PM
This is an entire file from a shopping cart random display. Hope this helps.


<%
Sub Displayrandomproducts
'***************************************************************
' Subroutine to di displays of products base on shop
' configuration options
' add no product logic and getconfig("xfrontpagerandomfield")
'******************************************************
dim sql, colcount, totalcolumncount, maxrecords, count
dim template, maxcolumns, randomselectfield, decimalpoint, randomrs, randomselectvalue
shopopendatabaseP conn
If conn.state<>adStateOpen then
shopclosedatabase conn
exit sub
end if
template="tmp_frontpage.htm"
count=0
maxcolumns=getconfig("xfrontpagemaxcolumns")
maxrecords=getconfig("xfrontpagemaxrecords")
randomselectfield=getconfig("xfrontpagerandomfield") ' new
randomselectvalue=getconfig("xfrontpagerandomvalue")
'randomselectfield="pother1"
If maxrecords<>"" and isnumeric(maxrecords) then
maxrecords=clng(maxrecords)
else
maxrecords=6
end if
If maxcolumns<>"" and isnumeric(maxcolumns) then
maxcolumns=clng(maxcolumns)
else
maxcolumns=3
end if
'
dim selectfield, selectvalue
selectfield=getconfig("xfrontpagefield")
selectvalue=getconfig("xfrontpagevalue")
decimalpoint=getconfig("xdecimalpoint")
dim conn
If ucase(selectvalue)="RANDOM" then
FrontpageGetrandomproducts conn, maxrecords, sql, randomselectfield, randomselectvalue
else
Generatefrontpagesql selectfield, selectvalue, sql
end if
If sql="" then
shopclosedatabase conn
exit sub
end if
set randomrs=conn.execute(sql)
Formatfrontpageheader
colcount=0
totalcolumncount=0
'main loop
do While Not randomrs.EOF and count<maxrecords
FormatFrontpageTemplate template,colcount,totalcolumncount,randomrs, maxcolumns
count=count+1
randomrs.movenext
loop
'
FrontpageFillRemainingColumns colcount, totalcolumncount, maxcolumns
response.write "</center></table>"
CloseRecordSet randomrs
shopclosedatabase conn
end sub
'**********************************************************************
' Template file is tmp_frontpage.htm
'*********************************************************************
Sub FormatFrontpageTemplate(template,colcount,totalcolumncount,objrs, maxcolumns)
dim rc
if colcount=0 then
Response.write FrontRow & vbcrlf
end if
response.write FrontColumn
ShopTemplateWrite template, objRs, rc
Response.write FrontColumnEnd & vbcrlf
colcount=colcount+1
totalcolumncount=totalcolumncount+1
if colcount>= MaxColumns then
response.write frontrowend & vbcrlf
colcount=0
end if
End Sub

'*************************************
Sub FormatFrontpageheader
' displays header for categories
response.write "<center>"
response.write FrontTable & vbcrlf
end sub

'*******************************************************************
' Generate the sql
'*******************************************************************
Sub GenerateFrontpagesql (selectfield,selectvalue, sql)
dim wherestr, sortstr
sortstr=getconfig("xsortproducts")
wherestr="where hide=0"
If selectfield<>"" and selectvalue<>"" then
wherestr=wherestr & " and " & selectfield & "='" & selectvalue & "'"
end if
sql="select * from products " & wherestr
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
sql=sql & " and (clanguage='" & getsess("language") & "'"
sql=sql & " or clanguage is null)"
end if
'inventory
sql = sql & " AND (highercatalogid is null)"
If sortstr<>"" then
sql=sql & " order by " & sortstr
end if
If getconfig("xdebug")="Yes" then
debugwrite sql
end if
end sub

'***********************************************************************
' make sure any columns are filled
'*************************************************************************
Sub FrontpageFillRemainingColumns (colcount, totalcolumncount, maxcolumns)
If colcount=0 then exit sub
If totalcolumncount< maxcolumns then
response.write FrontRowEnd
exit sub
end if
Do While Colcount<MaxColumns
response.write FrontColumn & "&nbsp" & frontcolumnend & vbcrlf
colcount=colcount+1
loop
response.write FrontRowEnd
end sub

Sub FrontpageGetrandomproducts (conn,maxrecords,sql, randomselectfield,randomselectvalue)
dim i, catalogid, index, plist, rs, sqlstr, sortstr
plist=""
Set rs=Server.CreateObject("ADODB.Recordset")
sqlstr="SELECT catalogid FROM products where hide=0"
If randomselectfield<>"" and (randomselectvalue="" or Ucase(randomselectvalue)="NULL") then
sqlstr=sqlstr & " and " & randomselectfield & "<>NULL"
elseif randomselectfield<>"" then
sqlstr=sqlstr & " and " & randomselectfield & "='" & Replace(randomselectvalue, "'", "''") & "'"
end if
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
sqlstr=sqlstr & " and (clanguage='" & getsess("language") & "'"
sqlstr=sqlstr & " or clanguage is null)"
end if

'inventory
sqlstr = sqlstr & " AND (highercatalogid is null)"
'debugwrite sqlstr
rs.Open sqlstr,conn,3,3
If rs.eof then
closerecordset rs
sql=""
exit sub
end if
Dim arrData ' Array to Store Data
Dim arrSequence ' Array to Hold Random Sequence
Dim iArrayLooper ' Integer for Looping
Dim iArraySize ' Size of Data Array
'VP-ASP 6.08a - Changed CINT below to CLNG
If getconfig("xmysql")<>"Yes" AND (instr(lcase(xdatabasetype), "mysql") = 0) then
iarraysize=clng(rs.recordcount)
else
'VP-ASP 6.09 - MYSQL issue with random value
'iarraysize=clng(GetRecordcount (conn))
iarraysize=clng(GetRecordcount (conn,randomselectfield,randomselectvalue))
end if
redim arrdata(iarraysize)
for i = 0 to iarraysize-1
arrData(i)=rs(0)
rs.movenext
next
RS.close
Set RS = Nothing
If iarraysize<maxrecords then
maxrecords=iarraysize
end if
' Get an array of numbers 0 to array size randomly sequenced.
arrSequence = Resequencearray(iArraySize)
for i = 0 to maxrecords-1
index=arrsequence(i)
catalogid=arrdata(index)
if plist<>"" then
plist=plist & ","
end if
plist=plist & catalogid
Next
sql="select * from products where hide=0 and catalogid In (" & plist & ")"
sortstr=getconfig("xsortproducts")
If sortstr<>"" then
sql=sql & " order by " & sortstr
end if
If getconfig("xdebug")="Yes" then
debugwrite sql
end if
end sub

Function ResequenceArray(iArraySize)
Dim arrTemp()
Dim I
Dim iLowerBound, iUpperBound
Dim iRndNumber
Dim iTemp
' Set array size
ReDim arrTemp(iArraySize - 1)
Randomize
iLowerBound = LBound(arrTemp)
iUpperBound = UBound(arrTemp)
For I = iLowerBound To iUpperBound
arrTemp(I) = I
Next
' Loop through the array once, swapping each value
' with another in a random location within the array.
For I = iLowerBound to iUpperBound
iRndNumber = Int(Rnd * (iUpperBound - iLowerBound + 1))
' Swap Ith element with iRndNumberth element
iTemp = arrTemp(I)
arrTemp(I) = arrTemp(iRndNumber)
arrTemp(iRndNumber) = iTemp
Next 'I
' Return our array
ResequenceArray = arrTemp
End Function
'***********************************************************************
' get record count for mysql
'************************************************************************
'VP-ASP 6.09 - MySQL issue with randomvalue
'Function GetrecordCount (conn)
Function GetrecordCount (conn,randomselectfield,randomselectvalue)
dim sqlstr, rs, rcount
sqlstr="select count(catalogid) FROM products where hide=0"
If randomselectfield<>"" and (randomselectvalue="" or Ucase(randomselectvalue)="NULL") then
sqlstr=sqlstr & " and " & randomselectfield & "<>NULL"
elseif randomselectfield<>"" then
sqlstr=sqlstr & " and " & randomselectfield & "='" & Replace(randomselectvalue, "'", "''") & "'"
end if
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
sqlstr=sqlstr & " and (clanguage='" & getsess("language") & "'"
sqlstr=sqlstr & " or clanguage is null)"
end if
sqlstr = sqlstr & " AND (highercatalogid is null)"
set rs=conn.execute(sqlstr)
if rs.eof then
rcount=0
else
rcount=rs(0)
end if
closerecordset rs
Getrecordcount=rcount
End function
%>

degsy
02-21-2007, 02:55 PM
the 1000 is not the amount of records. It's to change the random seed.

If you want to limit the recordset then put whatever filter you want in the query.



strSQL = "SELECT TOP 1 *, Rnd(" & 1 * (intRandomNumber) & ") FROM properties WHERE featured = yes ORDER BY 1"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum