Hi, I'm creating a URL Shortening website. I want to allow users to view the stats for the links. I planned to do Country stats. I think I should use a library similar to GeoIP for PHP to get the country, but, how can be the MySQL. My weakness is the design of the MySQL tables. Can you recommend me a table format, please?
A:
A table, something like:
id, fromip, toip, country
Use the number format for the ip ranges which you'll use to query for the country.
Also: http://stackoverflow.com/questions/459076/how-to-store-and-search-for-an-ip-address
zaf
2010-04-19 17:03:44
@Downvoter - please leave a comment so you can enlighten us where we went wrong.
zaf
2010-04-19 17:08:54
Up voted, this is the format Ive used in the past. Its worth pointing out that the IP address is stored as an integer.
James Westgate
2010-04-19 17:13:44
Cheers James. I did write "Use the number format" but I suppose some people don't read that far.
zaf
2010-04-19 17:58:55
What does it `toip`?
Francesc
2010-04-19 18:13:23
@Francesc You store a range of IP addresses for each row in the table. Also, you use the integer format for the ip address. For example, one row could be: 1, 2130706433 , 2130706687 , 'myCountry'. Where 2130706433 represents the 'fromip' address 127.0.0.1 and 2130706687 represents the 'toip' address 127.0.0.255
zaf
2010-04-19 18:26:17
A:
What I would do is for each view, I would take the person who is viewing's ip, lat&long, or whatever you want to use for tracking. Perhaps have either id, ip, lat, long, url. The id could be autoincremented, and the ip, latitude, and longitude might combine to give you accurate results. Just select the rows from the table where the URL is equal to the one wanted, and you're set. (You could also have a 'user' column, if you want to show stats for all of the user's links.)
Arianna
2010-04-19 17:14:56