Re: Question: Marking records
Sorry, logTable is just an arbitrary name I came up with. You can name the table anything you want, like SearchViewTracking. On Nov 18, 2004, at 12:26 PM, Stuart Felenstein wrote: --- Brent Baisley <[EMAIL PROTECTED]> wrote: It depends on whether you are tracking info for individual users Individual users (lucky me!) 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 I just ran a search on Log Tables. I'm coming to the conclusion they are just "tables" , myisam or innodb. No different , except "log" is how they are used ? Is this a correct assumption ? Stuart -- 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]
Re: Question: Marking records
--- Brent Baisley <[EMAIL PROTECTED]> wrote: > It depends on whether you are tracking info for > individual users Individual users (lucky me!) > 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 > I just ran a search on Log Tables. I'm coming to the conclusion they are just "tables" , myisam or innodb. No different , except "log" is how they are used ? Is this a correct assumption ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: Marking records
It depends on whether you are tracking info for individual users or just on a global basis. On a global basis, you just need to run an update query: 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,,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]
RE: Question: Marking records
[snip] I'm thinking this through. In other words, when a record is returned in a search , and insert statement makes an entry into another table? This will involve scripting as well as sql statements ? [/snip] Well, you could use triggers and stored procedures, but yeyou are going to have to do something to add the "marks". -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: Marking records
--- Jay Blanchard <[EMAIL PROTECTED]> wrote: > [snip] > 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. > [/snip] > > Additional tables to hold status information is in > order. You would > query those tables for their current counts and > update as required. > I'm thinking this through. In other words, when a record is returned in a search , and insert statement makes an entry into another table? This will involve scripting as well as sql statements ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: Marking records
[snip] 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. [/snip] Additional tables to hold status information is in order. You would query those tables for their current counts and update as required. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
