UPDATE dbTable SET searchCount=searchCount+1 WHERE queryparams
The default value for searchCount would be 0, if the record never came up in a search the searchCount is 0.
I wouldn't just track "viewed" or not, you might as well track how many times it's been viewed (viewCount). It will give more data without much added cost.
Now, if you want to keep separate logs for each user, you need to create a "log" table. The log table would have the following fields:
relatedRecordID, userID, searchCount, viewCount
When the user does a search, you first update the searchCount in the log table, something like:
UPDATE logTable,mainTable SET searchCount=searchCount+1
WHERE queryparams AND
logTable.relatedRecordID=mainTable.ID AND logTable.userID=###
Then you add log entries, using INSERT SELECT, for records that the user has searched on for the first time. Something like:
INSERT INTO logTable (relatedRecordID, userID, searchCount)
SELECT mainTable.ID,<userID>,1 FROM mainTable
WHERE queryparams
Updating the log entry to track viewed, is of course just a simple update.
UPDATE logTable SET viewCount=viewCount+1 WHERE relatedRecordID=### AND userID=###
On Nov 18, 2004, at 11:14 AM, Stuart Felenstein wrote:
I'm not even sure what this would be called, but maybe someone (or more then one) can give me some pointers and where to learn how this is down:
Someone does a search on my system:
1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented
2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as "viewed", again , incrementing everytime they are viewed is needed.
Sorry, if this is another lame question. I am not looking for the code, just some ideas how these things get implemented. Using 4.0.22
Thank you. Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
