We help IT Professionals succeed at work.
Get Started

Complex Cf query and order display question

CFbubu asked
Last Modified: 2013-12-24
Hi, this question has stumped me and I am hoping someone would be able to enlighten me. It is kinda complex(to me) and I hope I can explain it well.

1) Let's say I have a DB table with 100 lines. Each line has info for 1 item, thus, DB table has 100 line items.
2) Each of these line items has to be entered with the inclusion of 2 critical data fields. One is 'time-stamp' at the instant of entry into the DB, and the other is 'View-Count'.
3) I have a page that I want to display 4 line items. These 4 items would be drawn from a query from the above mentioned table with the 100 line items.
4) The 4 items chosen to be displayed on the page will have to be displayed in the order of 'time_stamp' of when it was displayed, (oldest first) and 'view_count'.
5) Each time the user refreshes the page, it will show the next 4 items based on criteria in point number 4. Each time an item is displayed, the 'view_count' for that item is to be increased by +1 and the ''time_stamp' field updated by Now().

Therefore, the way this works is that any items that has NOT been 'displayed'('view_count' of '0')  will take priority over any items that have already been displayed. If all items have been displayed once with a 'view_count ' of '1', and a new item is entered into the DB table(new item view_count' of '0'), than the new item will take priority.  If all line items have already been displayed once, than the display of the items will be prioritised by which items are 'older' based on the displayed 'time_stamp' field.

This means if 4 items are displayed on the page(From above paragraph), 3 items will have a view count of '1' while one item(new DB entry) will have a 'view_count' of '0'. Of the 3 items with a view_count of '1', they are arranged in the order of  when they were last displayed from the 'time_stamp' field. Of course, after all these 4 items are displayed on the page, all their 'view_counts' would go up by +1 and their respective 'time_stamp' fields would be updated to Now().

6) Each items to be displayed are only to be displayed for 10 days from the date it was first displayed. So this will require something like a 'Time_Display_Startdate' for each line item. This field would probably store time_stamp when the item is first displayed and this will not have to be updated.

(Interesting twist)
7) If just starting out, and the DB table has less than 100 items, lets say only 2 items, BUT the page still has to display 4 items, than would it be possible to fill in the other 2 slots with a random query from another DB(No special requirements here)? Similarly, if the original table only had 3 items, than the page will display the 3 items from the first query, and than 'fill' in the 4th slot with an item from another source.

So, from the above theoratical questions, I hope to be able to 'cycle' through the items in a DB 'fairly' without repeating the items for a limited time(10 days), and be able to draw from another source(No special requirements for the second source as it will just be getting 2 random items) if there are not enough items from the first table.

I realise this is a fairly complex question, but hope someone here may be able to see what I am trying to achieve and help me out. I really would like to know how I would be able to write the CF code/queries  to achieve such a mechanism. For better clarity, maybe we could say that the table is named 'T_Product' with current fields 'View_Count', 'Display_Timestamp', and 'Time_Display_Startdate'. Do feel free to add any other fields or suggestions/changes to make this work.

Thank you so very much!
Watch Question
Most Valuable Expert 2015
This problem has been solved!
Unlock 7 Answers and 57 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE