Idea For Using A Cron Job To Grab Daily Travian Map.sql Updates I want to build a library of resources for my clan members.

[travstatesmen 0]

Here is an idea that I have been toying with for a while now, and I'd really appreciate some help with it, as I'm not a great coder. I can get by, and will probably work out the intricacies of implementing this, all by myself, but it is going to take me quite a long time. I'd really appreciate some help with making this happen, so that I can use it on my upcoming Xisto website.

The MMOG that I am playing, Travian, creates a daily update file, which is essentially just an SQL file full of INSERT commands, that is made available to the public daily. Travian can be played on multiple servers, and there are also country-specific servers, and they all produce these map.sql files each day. For instance, if I am playing on the first international server then I can grab the SQL data from http://forums.xisto.com/no_longer_exists/ whereas, if I were playing on the fourth Australian server, I would use http://s4.travian.com.au/, or for the second Hungarian server I would use http://forums.xisto.com/no_longer_exists/, etc.

These map.sql files are already being used for some pretty complex things by other websites. For instance, there are Travian World Analyzers, that allow you to query the data over a period of time and see how players have progressed, which Alliances they have moved to, how fast their villages are growing, etc. Some websites use the map.sql files to generate a forum signature image that automatically updates with daily stats. Some are using the map.sql data to generate on-the-fly graphical maps of the Travian worlds, plotting on grids the locations of the villages. It is really useful to have a collection of these map.sql files. I want to get some for my new website on Xisto.

In order to achieve this, I can see that I will need a CRON job to action a script every day that will go to the necessary sites and download the map.sql files. I will need to specify which servers to go to, as I will only be interested in the servers where my clan members are currently active. This will save my hosting space. Also, the map.sql files will need to be renamed with a datestamp and server name.

There are several downloadable freeware programs that also use the data in these map.sql files, and I intend to have a selection of these tools available for download by my clan members. One such tool, TrUtil (by Lamb) is what got me started on this idea in the first place. This program allows you to specify a server to download from, adding it to your list of servers, then you select a server from your list and press the Download button, and it gets the latest data for that server and saves it as a file to your local computer, renaming it with a datestamp and server-name. But you have to remember to do that every day for every server that you play on. It becomes tedious and is often forgotten about, so my collection of map.sql files has pieces missing from it. I want to provide a useful resource for my clan members, a full, complete, up-to-date collection of all the map.sql files for all the servers that they are playing on. They will need to be hosted somewhere, probably separate to my Xisto site as they can be up to 10MB each, and I will no doubt be making use of them myself also, for team badges for my own forums, and other ideas, by inserting the data from each one into one of my own mySQL databases on my upcoming Xisto website.

So, this is the idea for my upcoming website here on Xisto. Any help and advice that I can get for implementing it would really be appreciated. I have also created a thread on the forums for help with specific mySQL database issues relating to this project. If you may be able to assist with this, please have a look at the thread entitled Need Advice On Setting Up Mysql Database. Thanks!

Edited August 22, 2008 by travstatesmen (see edit history)

[shadowx 0]

Heres an idea, basically get the SQl file details, then load them into a variable and use that variable as an SQL query to do its stuff, i present some code i modified from a site i found:

<?$host = 'shadows.trap17.com;;																		  //define your host$path = '/index.htm';																							   //then the path to the SQl file$fp = fsockopen ($host, '80');																				  //of course, selects the HTTP portif ($fp) {																												// checks it managed to open successfully fputs ($fp, 'GET '.$path." HTTP/1.0\r\nHost: ".$host."\r\n\r\n");								  //this seems to get the contents of the file, i dont really know, but it works :Pwhile (!feof($fp)) {																								// while there is data to collect, we collect it within the loop$data = fgets ($fp, 10240);																					//read the data into a variablemysql_query($data,$link);																					 //execute that variable using SQL}} else {echo "Oops... Didn't work... ";																			 //and at least you will know if it all goes *BLEEP* up!}	 ?>

That should work i think. Cron jobs is easy enough, if you need a hand with that let us know.

[Saint_Michael 3]

This is a bit off topic since my MySQL are a bit weak, but I have to be honest and is Xisto hosting isn't big enough to run a gaming server and in order to do that you would have to put restrictions everywhere because if you have thousands of players going at it with your Xisto account your going to be suspended very quickly in terms of bw usage. However, since it seems that is your plan for your hosting account what I recommend is set it up for basic needs and let about maybe 10 people play on that gaming server for about an hour and then tell them all to log off and then look at your data just see what your using. That way you can start multiplying those numbers in groups of 10 to see how long it wil take to go through 10GB of BW.Other then that another recommendation I have since I believe your the only two members here on this forum, you might want to be able to get support from that games forums as well sine they would have better support then we do. However, Xisto members might be able to help in others ways as well. like shadowx did in his post aboe me.

[travstatesmen 0]

This is a bit off topic since my MySQL are a bit weak, but I have to be honest and is Xisto hosting isn't big enough to run a gaming server and in order to do that you would have to put restrictions everywhere because if you have thousands of players going at it with your Xisto account your going to be suspended very quickly in terms of bw usage. However, since it seems that is your plan for your hosting account what I recommend is set it up for basic needs and let about maybe 10 people play on that gaming server for about an hour and then tell them all to log off and then look at your data just see what your using. That way you can start multiplying those numbers in groups of 10 to see how long it wil take to go through 10GB of BW.

Hmm, I don't know where you got the idea that I am setting up a gaming server, Saint_Michael. I am actually setting up an online gaming clan, and we play on several gaming servers elsewhere. This is more like a fan site about a game, rather than a game itself. We are a community of online gamers, and will not be setting up the game itself on Xisto. The data that we are intending to gather, store, and query on my Xisto hosting account is data generated by an online game. We then manipulate the data offline (as far as the game is concerned). I'm definitely NOT setting up a gaming server!

 

But thank you for all your advice and suggestions, and also to shadowx as well. I will certainly be going through the coding suggestion you've made. As I said in my application for a web hosting account here on Xisto, I am a copy/paste/tweak programmer, and am not really proficient at this type of thing, but I know how to make use of the many automated code generators that are available on the web. I will really appreciate any help that anybody here on the Xisto forums offers.

[iGuest 3]

 instead of all that scripting, how about using tools that do all the heavy lifting for you:

wget http://forums.xisto.com/no_longer_exists/ > map.Sql

Followed by:

Mysql -you username -ppassword travianDBName < droptables.Sql

Mysql -you username -ppassword travianDBName < map.Sql

droptables would simply be:

drop table if exists travian;

create table travian ... (you need to look up the column definitions, they are around)

I do know that this works, I have my own travian site that does exactly this.