Hey I am just getting started learning php. I haven't really started using javascript though. I have a lot of domains on different servers. I am looking to create a script to log the traffic from my sites and send them to a centralized database. Similar to the google analytics. However, I would like to keep the information for myself rather than giving it all to Google. I found a tutorial that shows how to get started. http://www.noupe.com/php/how-to-create-your-own-stats.html 1.) I ran into a problem with the mysql part trying to setup the tables CREATE TABLE stats( page text UNIQUE, ip text, views UNSIGNED int DEFAULT 0, referrer text DEFAULT '' ); Code (markup): I am using MySQL 5.5.20 I tried all types of different ways to fix the problem with no luck. Hopefully somebody can help with this. The problem is on line 4 of that code 2.) Is this a safe method to be able to track all the traffic from my domains. I guess I would be using the javascript and sending the data to process.php which would have to hold the details for the main database. So I would have to have both the javascript and the process.php on each site. Would it be better or possible to try to send the data to a 1 central process.php file located on a different server. Like I said I'm new to this and thought it would be a good learning project I 100 percent sure about security wise. Would appreciate a simple point in the right direction or a tutorial of some sort. Thanks in advance for your time and input.
1) If you change the create script like so, it works (for me, at least): CREATE TABLE stats( page text, ip text, views int DEFAULT 0, referrer text DEFAULT '' ); Mysql doesn't like you applying the unique constraint to the page, and it doesn't like the "unsigned". Personally, if I was designing this table, I would make one database row per log entry, so just log page, IP, and referrer. You'll have a lot of problems if you do it this way - e.g. two sites accessing the table at once will mess up your numbers. 2. It seems fine, but you should look into APIs to protect against SQL injection protection in PHP. Basically any data that comes to your script could be faked, so any "INSERT" statements you do are at risk. You're also going to get a lot of bot traffic logged, and there are a lot of fields you could log that you aren't. I think it's a great idea for a learning project, but if you just want to keep your data, you might just want to install a tool on your host. AWStats seems to be popular, and there are many others.
Thanks for the response. Yeah I plan on improving upon this version to add some more details. It's a learning project but hopefully I can use it a little bit to have all of my different domains tracked in one central location instead of my current method. Most of my sites are with Joomla so I am using the J4age stats and I have to actually log into each site to check the traffic. This can be time consuming. And I have 5 different hosting accounts so I would still need to log on at least 5 different times. Thanks for your input. I just didn't want to go loading something on every site that will leave me upon to attacks. I guess I should do a little more research on security. I figured I could use something like the google Javascript code but I would imagine it would be hard to track the bots as they usually are without JS.
Yeah, doing it yourself can be really good once you get to the point where you track things specific to what you're doing. With Google analytics, etc, you're limited to seeing the data how they give it to you, and to what events they record, although that can be quite substantial. I suspect that most attacks are scripts written to target vulnerabilities in common software (Wordpress, etc) rather than custom built to attack a specific site, but that all depends how big you are