Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3 ==> 3 ==> 1179007121
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727) ==> 1 ==> 1179007129
[web] [mysql] Best way to handle a 'dynamic' amount of items in a field?
Author
What's the best way to handle a 'dyanmic' amount of items from a field? Just to use string splicing and dicing, or is there some MySQL feature out there that makes this easier?
Example of 'string splicing and dicing':
I have a Stats feature for some stuff I'm working on, and I want to record browsers, hits, and last visit per visitor. The simplest approach I've made is to format the data with strings.
I have a table that has an 'ip' column (guess what that's for), and a 'client_data' column for the stuff I metnioned before, and it's formatted. Here's an example of 'client_data'
Why don't you store each in it's own field? Is there a limit to how many fields you can put in your table?
On McLean Books (.com) I have a products table. Each product had to have the ability to be "slotted" under one or more subjects (or none). I used a field in each product's row (subject) and for each id of the subject the client wishes to slot it under I used a colon (:).
Example:
Title of Book => title
23.95 => price
34:13:24:9: => subject
Then each subject searches for it's number (ex 9:)
The reason I suggest more fields is faster sorting, but I don't know exactly where your going with this data and how you will use it. Certainly, in any case you choose - you CAN do what you mentioned, and it WILL work.
On McLean Books (.com) I have a products table. Each product had to have the ability to be "slotted" under one or more subjects (or none). I used a field in each product's row (subject) and for each id of the subject the client wishes to slot it under I used a colon (:).
Example:
Title of Book => title
23.95 => price
34:13:24:9: => subject
Then each subject searches for it's number (ex 9:)
The reason I suggest more fields is faster sorting, but I don't know exactly where your going with this data and how you will use it. Certainly, in any case you choose - you CAN do what you mentioned, and it WILL work.
Author
I don't store each client variable in its own field becuase each client may have a different amount of browsers he or she uses... I don't want to split 'agent','hits', and 'date' up because the latter two are coupled with the first. One client may have Firefox and IE 7, and another may have Firefox, IE, Safari, Opera, and all that... It could be kind of a hassle to handle with at least 5 different sets of fields concerning relatively the same information, and then having to add columns and such... I can't think of that way being that much better than the current one I have in place.
Maybe I'm just being too fickle in my choices. I'm searching namely for an easier alternative, yet one that's still somewhat human readable...
Thanks for the help.
Maybe I'm just being too fickle in my choices. I'm searching namely for an easier alternative, yet one that's still somewhat human readable...
Thanks for the help.
So what's the example of an ip address with more then one browser on the same day? Are you going to make a new row in your table for the same ip address with the different agent or are you going to store all agents and associated hits along with them all into only one unique row?
Then think about how your going to display the information and what type of sorting may take place. It may be a few minutes more work to seperate the other two into their own fields but save your half an hour later if want to sort through everything and display highest amount of hits for a certain day or by the month, or by year.
Then think about how your going to display the information and what type of sorting may take place. It may be a few minutes more work to seperate the other two into their own fields but save your half an hour later if want to sort through everything and display highest amount of hits for a certain day or by the month, or by year.
Author
In splitting the client data apart, you would have to synchronize arrays of data across one field, and that can get somewhat iffy, then again, not as iffy as text manipulation...
And with having different sets of data with the same IP, that would require querying for multiple rows and then combining them for analysis and all that.
I've already got the full Stats system working with about 200 lines total (including the admin panel), with sorting and all that in place, it's just that I was using that as an example scenario. I just don't know at the time...
And with having different sets of data with the same IP, that would require querying for multiple rows and then combining them for analysis and all that.
I've already got the full Stats system working with about 200 lines total (including the admin panel), with sorting and all that in place, it's just that I was using that as an example scenario. I just don't know at the time...
string based delimiter systems just wont do. Say for example who's to say what could happen in the original implementation that the user agent contains the substring "==>" somewhere in it (remember user-agent is easily modifiable).
The simplest method i can think of would be to simply build a key based array of the client data (you can do this in one line if you want to). Then you can serialize() the array, send the serialization off to the DB(3 lines so far, the array, the call to serialize, then the db query. Hell if you arent going for verbose code it could be a single line.). Then when you pull that field out of the DB you can pass it through unserialize() and the array will be automatically rebuilt.
The simplest method i can think of would be to simply build a key based array of the client data (you can do this in one line if you want to). Then you can serialize() the array, send the serialization off to the DB(3 lines so far, the array, the call to serialize, then the db query. Hell if you arent going for verbose code it could be a single line.). Then when you pull that field out of the DB you can pass it through unserialize() and the array will be automatically rebuilt.
Some of the advice people are giving here are really bad database design. Having a single column that represents multiple values is a huge no-no. Database Normal Forms and all that. Makes for poor performance. Having tables with multiple fields that duplicate functionallity is also bad.
How about something like this:
UniqueUser[userID, IPAddress]
Browser[browserID, userAgentString, friendlyName, version] --whatever you want to track
BrowserStats[statID, userID, browserID, numHits]
Some simple queries,
Number of hits for a specific browser:
SELECT SUM(numHits) FROM BrowserStats WHERE browserID=[inputBrowserID]
Number of hits from a specific user:
SELECT SUM(numHits) FROM BrowserStats WHERE userID=(SELECT userID FROM UniqueUser WHERE IPAddress=[inputIPAddress])
Number of clients used by a specific user:
SELECT COUNT(browserID) FROM BrowserStats WHERE userID=(SELECT userID FROM UniqueUser WHERE IPAddress=[inputIPAddress])
How about something like this:
UniqueUser[userID, IPAddress]
Browser[browserID, userAgentString, friendlyName, version] --whatever you want to track
BrowserStats[statID, userID, browserID, numHits]
Some simple queries,
Number of hits for a specific browser:
SELECT SUM(numHits) FROM BrowserStats WHERE browserID=[inputBrowserID]
Number of hits from a specific user:
SELECT SUM(numHits) FROM BrowserStats WHERE userID=(SELECT userID FROM UniqueUser WHERE IPAddress=[inputIPAddress])
Number of clients used by a specific user:
SELECT COUNT(browserID) FROM BrowserStats WHERE userID=(SELECT userID FROM UniqueUser WHERE IPAddress=[inputIPAddress])
Author
Alright, I'm going to try it that way... I'm just not that well-versed in any sql language, and there's some stuff just right there that I didn't ever really think that could be done.
The design: (Just changed the names some, that's it)
Now I've got to add MySQL-function capability to my database interface to simplifiy the process.
Thanks!
EDIT: Changed 'client' to 'client_id' and 'browser' to 'browser_id'
[Edited by - deadimp on May 13, 2007 9:03:56 PM]
The design: (Just changed the names some, that's it)
stat_client[id,ip] - Records client infostat_browser[id,agent,[etc]] - Records browser infostat[id,client_id,browser_id,hits] - Records visits for a clientNow I've got to add MySQL-function capability to my database interface to simplifiy the process.
Thanks!
EDIT: Changed 'client' to 'client_id' and 'browser' to 'browser_id'
[Edited by - deadimp on May 13, 2007 9:03:56 PM]
Author
Alright, I've got the base system working now. (82 lines versus the 105 before, though a couple more queries per page)
Now I gotta work on the admin panel part.
Thanks for the tips!
Now I gotta work on the admin panel part.
Thanks for the tips!
Author
Question: How would you be able to sort browser by their total hits?
I've tried the filter "select * from `stat_browser` order by (select sum(`hits`) from `stat` where `browser_id`=id)", but I don't know how to make it select the id of the browser it's currently comparing...
I've tried the filter "select * from `stat_browser` order by (select sum(`hits`) from `stat` where `browser_id`=id)", but I don't know how to make it select the id of the browser it's currently comparing...
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement