weekly 'featured' database record 1

[aamaker]

I have a database that I use to hold properties / buildings that are for sale. I need to be able to use ASP to select a random one as the 'weekly' featured property.

To make it weirder, I still need to be able to allow some override (in the event the administrator wants to select a specific property - which would be rare, but still an important 'control' feature.

Whats the best way to approach this in terms of ASP / VBscript? Im using an access database and I think that makes it harder to accomplish, but my hands are tied there.

I can handle the admin feature whereby a property is selected as the override, but the

a) random
b) week duration

... have me stumped.


Thanks for any help you guys can provide on this one!

 

[emozley]

I used this to display a random tip of the day on an app of mine. Basically you count the number of records then loop through to a random number that is equal to or less than the number of records then display that record.

<%
TBL.Open "SELECT count(*) AS Total FROM Tips", DB
Total=TBL("Total")
TBL.Close

Randomize
RandomTipNo=int(rnd*Total)

TBL.Open "SELECT * FROM Tips", DB

For i=1 to RandomTipNo
TBL.MoveNext
Next

Response.Write(TBL("Tip"))

TBL.Close
Set TBL=Nothing
Set DB=Nothing
%>

With regard to your override problem I would create a table in your database called something like SystemConstants. In this you could have a yes/no field called DisplayRandomHouse. You could then load this value and if it was true do one thing (eg display a random record) or otherwise go to another page. Obviously you would have to make a form where an administrator could set this option in the SystemConstants table.

Something along the lines of:

<%

TBL.Open "SELECT DisplayRandomHouse FROM SystemConstants", DB
If Not TBL.EOF Then
DisplayRandomHouse=TBL("DisplayRandomHouse")
End If
TBL.Close

If DisplayRandomHouse=TRUE Then

'Insert the code I posted above here

Else

' Insert code to do something else here

End If

Set TBL=Nothing
Set DB=Nothing
%>

 

[aamaker]

looks good, but what Im after would 'hold' one randomly chosen item and would regard it as the featured one for a full week (Sunday to Saturday)... I think Im just going to allow the administrator to decide which one is featured using the admin access they have. If no one record is chosen to be the 'featured' item, then I'll just show the most recently selected item as the featured record.

Thanks though

 

[CaffeinatedBacon]

I'd try the following

1. Pick a time/day of the week you want the new feature to display

2. Create an include file, something like features.inc, on your server which contains variables for both the DB index for the feature you'd like to display and the last date the file was updated; seed that file with the day of the week & time you would like to have future updates occur

3. On your pages that display the feature, call a sub that uses DateDiff to check if the include file is older than 7 days.

a. If it is older, call a function like that specified by emozley to select an item from your db then use Scripting.FileSystemObject to overwrite the include file with variables for the db item's index and the closest previous Day/Time to your update interval (i.e. if your update day/time is 5am Sunday and it's Thursday May 25, 2006, set the last update day/time as May 21, 2006 @ 5am)

b. If it isn't older, then simply use the db index variable in your include to select the feature from your DB
Your include will be a very simple three line page like so:

Dim strDBIndex,datLastUpdated
strDBIndex="Waverly House"
datLastUpdated=CDate("05/21/2006 05:00")

Then on your calling page have something like:

<!--#include file="fetures.inc" -->
If DateDiff("D",datLastUpdated,Date()) > 7 Then
  'You may want to use "N", a variation on Date() & 10080 to get down to the minute accuracy

  'Run code to select & display a new feature
Else
  'Display the feature
End If

 

[Sheco]

I assume you want this to happen automatically but you don't necessarily want to create some process that runs on a schedule every sunday to give you a new one.

Lets build on emozley's idea by adding a new table. Lets call it the FeaturedItems table and lets have 2 fields in the table: ItemID and FeatureDate.

Now on the page(s) where you want to show the featured item, you have some query like this:
[TT]SELECT Top 1 ItemID, FeatureDate
FROM FeaturedItems
ORDER BY FeatureDate DESC[/tt]


OK, so that gets us the most recent ID and the date it was featured, now we need to figure out if that date is in the current week:
[TT]
Dim fID, fDate
IF datediff("ww", rs("FeatureDate"), Now) = 0 THEN
'The existing feature is still current
fID = rs("ItemID")
fDate= rs("FeatureDate")
ELSE
'We need a new feature.

'*** Code goes here to select random ID...
'*** you can use emozley's method or maybe a pure SQL approach
'*** fID = ???

'Calculate date of most recent sunday sunday
fDate = Dateadd("d", (-1 * (weekday(Date) - 1)), Date)

'Insert new record into FeaturedItems table something like:
'INSERT INTO FeaturedItems VALUES (fID, fDate)
END IF[/TT]

 

[Tarwn]

Star for CB, I like doing DateModified checks to control caching, nice to see others posting that train of thought