Jump to content
  • Advertisement
Sign in to follow this  
deadimp

[web] [mysql] Best way to handle a 'dynamic' amount of items in a field?

This topic is 4054 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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'
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
That's for the same visitor using Firefox and IE two different times. Pattern: "[agent] ==> [hits] ==> [date]\n[...]". I write some code that splits this up, and then handles it from there. It's just that when I do it like this, it feels a little off, but I can't think of any other better, easier-to-use way...

Share this post


Link to post
Share on other sites
Advertisement
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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])

Share this post


Link to post
Share on other sites
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)
stat_client[id,ip] - Records client info
stat_browser[id,agent,[etc]] - Records browser info
stat[id,client_id,browser_id,hits] - Records visits for a client

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]

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!