Re: best way to store search terms entered on a website?
I also log how many hits they got from their search. That tells us if our search mechanisms are working or not. Jerry Johnson wrote: Yes, but the rows are not (duplicate). The time, the search string. Potentially the IP address. in combination, always unique. On 8/24/07, stylo stylo [EMAIL PROTECTED] wrote: You seriously just keep adding row upon (duplicate) row every day for years and years? That's a hell of a lot of rows because people search a lot. I'm surprised, but I'm no expert. If no one thinks that's a bad idea... Thanks again. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287561 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: best way to store search terms entered on a website?
Yes, but the rows are not (duplicate). The time, the search string. Potentially the IP address. in combination, always unique. Well, I just meant a duplicate term meaning after a year you'd have some insane number of rows. Thanks. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287118 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: best way to store search terms entered on a website?
Create two data tables, one for terms (have an term_ID and search_term field) and one for search date/time (have a term_ID and date/time field). I know little about db design, sorry: why would you use 2 tables rather than one with 3 columns? Sure it's helpful. I want to know how many times people search for terms as it helps me figure out what people are searching for. But your description of the task didn't have a counter. Or would just keep adding repeat entries ad infinitum which can't be good. In any case, I don't see how the lastdate used helps me to do much. The term might not be used for a year, then once last week. Doesn't help to see what has been popular this month. My top 10 might be really old searches. I could query so lastdate must be within a recent period, but no more than that. I guess that's enough if no better solution. That's basically why I was asking what people have done and considered a file for each period. Am I missing anything obvious? The userid tip might be nice but we already have a custom bookmark page where the products are determined by users saving them to that page. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287023 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
Actually, I wrote an article about this for CFDJ, which includes code for a cfc that reports on search terms, auto-mails a weekly (or other period) report, etc.: http://cfdj.sys-con.com/read/48234.htm I'll take a look, thanks for the tip! ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287028 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: best way to store search terms entered on a website?
Just have one table, do not worry about the size of the table, it's negligible. If it's a site that requires a login, save their login id, the search term and the date (it does not matter if you intend on using it now, you might later). You do not need a counter, you can count using the SQL query. Good grief. On 8/24/07, stylo stylo [EMAIL PROTECTED] wrote: Actually, I wrote an article about this for CFDJ, which includes code for a cfc that reports on search terms, auto-mails a weekly (or other period) report, etc.: http://cfdj.sys-con.com/read/48234.htm I'll take a look, thanks for the tip! ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287029 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
You seriously just keep adding row upon (duplicate) row every day for years and years? That's a hell of a lot of rows because people search a lot. I'm surprised, but I'm no expert. If no one thinks that's a bad idea... Thanks again. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287102 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: best way to store search terms entered on a website?
Yes, but the rows are not (duplicate). The time, the search string. Potentially the IP address. in combination, always unique. On 8/24/07, stylo stylo [EMAIL PROTECTED] wrote: You seriously just keep adding row upon (duplicate) row every day for years and years? That's a hell of a lot of rows because people search a lot. I'm surprised, but I'm no expert. If no one thinks that's a bad idea... Thanks again. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287104 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
best way to store search terms entered on a website?
Any suggestions on how best to store search terms entered on a website? If the database, then see if used already and increment, or add if not, and maybe set a datelastused? But then that is 2 db hits right there and could grow quite big. Or just write them to a file and parse and reset the whole file weekly or monthly? I'm thinking the latter, but how slow is writing to a file (on Linux)? it's not a heavily trafficked site, but just wondering what people do. Thanks. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286916 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
I just log the term and the date. So one row per search. This then lets me do stuf like top ten search phrases. On 8/23/07, stylo stylo [EMAIL PROTECTED] wrote: Any suggestions on how best to store search terms entered on a website? If the database, then see if used already and increment, or add if not, and maybe set a datelastused? But then that is 2 db hits right there and could grow quite big. Or just write them to a file and parse and reset the whole file weekly or monthly? I'm thinking the latter, but how slow is writing to a file (on Linux)? it's not a heavily trafficked site, but just wondering what people do. Thanks. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286922 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
That's what databases are for tho.. to be 'quite big' ;). We have tables that have hundreds of millions rows. And as long as they're properly indexed (partitioned is super nice too) they'll fly. Don't worry about having a big db table, unless you're limited on space in the DB. On 8/23/07, Raymond Camden [EMAIL PROTECTED] wrote: I just log the term and the date. So one row per search. This then lets me do stuf like top ten search phrases. On 8/23/07, stylo stylo [EMAIL PROTECTED] wrote: Any suggestions on how best to store search terms entered on a website? If the database, then see if used already and increment, or add if not, and maybe set a datelastused? But then that is 2 db hits right there and could grow quite big. Or just write them to a file and parse and reset the whole file weekly or monthly? I'm thinking the latter, but how slow is writing to a file (on Linux)? it's not a heavily trafficked site, but just wondering what people do. Thanks. ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286925 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
I just log the term and the date. So one row per search. This then lets me do stuf like top ten search phrases. Wouldn't you need to search for the phrase first and increment a total? Otherwise you'd have naked on 90,000 rows :-) And the date only refers to the last time, so not so helpful, no? That's why I was thinking a file might be better. ?? ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286970 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
If you split multiple search terms on a single search up into multiple inserts (ie; my best dog would result in three inserts) then you could do a COUNT(*) AS C and a GROUP BY TERM, ORDER BY C DESC, TERM or something like that. That'll do yer. BTW, if you want to be extra sneaky, if the site has logins, also save the userid. Then you can let users know what their top ten searches are (like Google) and allow them to click on them to revisit their search results. Auto bookmarks! Mik At 01:38 PM 8/23/2007, you wrote: I just log the term and the date. So one row per search. This then lets me do stuf like top ten search phrases. Wouldn't you need to search for the phrase first and increment a total? Otherwise you'd have naked on 90,000 rows :-) And the date only refers to the last time, so not so helpful, no? That's why I was thinking a file might be better. ?? ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286975 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
Sure it's helpful. I want to know how many times people search for terms as it helps me figure out what people are searching for. Having times also is important. Ajax may be super important this year, but may fall next year. On 8/23/07, stylo stylo [EMAIL PROTECTED] wrote: I just log the term and the date. So one row per search. This then lets me do stuf like top ten search phrases. Wouldn't you need to search for the phrase first and increment a total? Otherwise you'd have naked on 90,000 rows :-) And the date only refers to the last time, so not so helpful, no? That's why I was thinking a file might be better. ?? ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286980 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: best way to store search terms entered on a website?
Create two data tables, one for terms (have an term_ID and search_term field) and one for search date/time (have a term_ID and date/time field). In the terms table check to see if the term is there. If it is get the term_ID and update the time table with the term_ID and date. If it is new, add the term, return the ID, then update add the date/time and term_id to the date table. Now you have two tables: one with unique terms; another with date/time of each search... linked using the term_id as a key. I think this is the most flexible way you can do it. Do it this way and you should be able to write queries and reports to analyze that data pretty much every way possible. Is this explanation understandable? Robert B. Harrison Director of Interactive services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 T : 631.231.6600 Ext. 119 F : 631.434.7022 www.austin-williams.com Great advertising can't be either/or... It must be . ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286982 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
I just log the term and the date. So one row per search. I've been asked to come up with a similar function for a book database so as to be able to sort search results by top X. After the initial search is run, if somebody clicks on a particular title to see the details, it gets a vote. After that, if they actually purchase the book, it gets another vote. Using the votes, I'll be able to sort results by most popular, display todays top ten on their index page and stuff. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286983 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
In my mind, logfiles should be lean, simple and fast. no thought, just entry. Write only. Just the facts, ma'am Once the data goes in, reporting from it is a different matter. queries, olap apps, and any rolled up or calculated data happen during a different process. Thoughts? Jerry On 8/23/07, Robert Harrison [EMAIL PROTECTED] wrote: Create two data tables, one for terms (have an term_ID and search_term field) and one for search date/time (have a term_ID and date/time field). In the terms table check to see if the term is there. If it is get the term_ID and update the time table with the term_ID and date. If it is new, add the term, return the ID, then update add the date/time and term_id to the date table. Now you have two tables: one with unique terms; another with date/time of each search... linked using the term_id as a key. I think this is the most flexible way you can do it. Do it this way and you should be able to write queries and reports to analyze that data pretty much every way possible. Is this explanation understandable? ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287004 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: best way to store search terms entered on a website?
Actually, I wrote an article about this for CFDJ, which includes code for a cfc that reports on search terms, auto-mails a weekly (or other period) report, etc.: http://cfdj.sys-con.com/read/48234.htm I hope this of interest, James Edmunds On 8/23/07, stylo stylo [EMAIL PROTECTED] wrote: Any suggestions on how best to store search terms entered on a website? If the database, then see if used already and increment, or add if not, and maybe set a datelastused? But then that is 2 db hits right there and could grow quite big. Or just write them to a file and parse and reset the whole file weekly or monthly? I'm thinking the latter, but how slow is writing to a file (on Linux)? it's not a heavily trafficked site, but just wondering what people do. Thanks. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287019 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
