Not sure how to explain this (Timestamp and updating values)

[bloodless2010]

Okay, so I'm making a game like Tribal Wars/Travian, I'm currently doing the resources, the way it works is for example: '100 logs per hour' or so, I'm doing per minute, so for example '10 logs per minute' or whatever, what would be the best way to do this so that it's live? so, if it was 10 logs per minute (thats 1 log per 6 seconds) how would I make it increment every 6 seconds efficiently without doing sooo many queries (cronjobs are out of the question) I'm thinking timestamps, I could do a timestamp system so it does it every minute, but how would I do it so it is live and you don't have to wait for a whole minute. I hope I have explained this enough, but if not here is a scenario:

User Z has 10 logs per minute, instead of them having to wait a whole minute for 10, it would instantly be updated +1 after 6 seconds.. but this would need to be flexible enough to any value (and instantly).. Sorry if this is confusing I really don't know how to explain this any better.

[Seker]

Okay, so I'm making a game like Tribal Wars/Travian, I'm currently doing the resources, the way it works is for example: '100 logs per hour' or so, I'm doing per minute, so for example '10 logs per minute' or whatever, what would be the best way to do this so that it's live? so, if it was 10 logs per minute (thats 1 log per 6 seconds) how would I make it increment every 6 seconds efficiently without doing sooo many queries (cronjobs are out of the question) I'm thinking timestamps, I could do a timestamp system so it does it every minute, but how would I do it so it is live and you don't have to wait for a whole minute. I hope I have explained this enough, but if not here is a scenario:

User Z has 10 logs per minute, instead of them having to wait a whole minute for 10, it would instantly be updated +1 after 6 seconds.. but this would need to be flexible enough to any value (and instantly).. Sorry if this is confusing I really don't know how to explain this any better.

AJAX, I'm guessing?

[Guest]

Might want to grab a clone that is already out there http://forum.ragezone.com/f582/

[bloodless2010]

AJAX, I'm guessing?

That would be to update live, and it would only be client side, I need it to be server side

 

Might want to grab a clone that is already out there http://forum.ragezone.com/f582/

I'm not looking to make an exact copy, I want to make my OWN village stragery game that's gonna be unique ;)

[Seker]

That would be to update live, and it would only be client side, I need it to be server side

I think you need to take another look at what AJAX is..

[bloodless2010]

I think you need to take another look at what AJAX is..

I know what AJAX is, but I don't think you understand my problem, the problem ISN'T making the value update dynamicly on the website, but it's to update the amount of resources in the database, not every minute, but every second without lag or something :/

Instead of having to wait a whole minute, for 60 logs, every second it would go up one (and still be stored in the DB)

[bloodless2010]

Okay, so I worked out how I'm going to do this but I'm not sure how I'm going to write this out..

I have a table in the DB of a UNIX timestamp (the last time it was updated) basically, I'll get the current timestamp and take away the time from the last update.

Now I have the seconds since the last update, and I'll times that by the resources per second (which I'll work out my side) and then add it to the current resources the user has..

[KyleMassacre]

$logs = mysql_query("select id, time, logs from log_table where time <= ".time() + 6."");
while($r = mysql_fetch_assoc($logs))
   $update = mysql_query("update logs_table set logs = logs + 10, time = ".time()." where id = ". $r['id']."") or die(mysql_error ());

something to that effect?

[bloodless2010]

$logs = mysql_query("select id, time, logs from log_table where time <= ".time() + 6."");
while($r = mysql_fetch_assoc($logs))
   $update = mysql_query("update logs_table set logs = logs + 10, time = ".time()." where id = ". $r['id']."") or die(mysql_error ());

something to that effect?

no, but I've kinda done it now :p Just took some brain fart for me to do it;

Here's my current code:

http://pastebin.com/0G1WChF6

How can this code be optimized? Is there any way? I'm not sure if it's the best it can be :p

Edited November 13, 2013 by bloodless2010

[Script47]

Why not bind the parameters?

[bloodless2010]

Why not bind the parameters?

What do you mean?

[Script47]

Are you using MySQLi or PDO?

[bloodless2010]

Are you using MySQLi or PDO?

I am using PDO

[bloodless2010]

/*
* htmlspecialchars() - Turn's all html in to it's entities
* trim() - Removes all whitespaces
*/
$username = htmlspecialchars(trim('SomeUsername'));

// You can bind all the parameters you have secures it a lot more
$selectUserExample = $PDO->prepare("SELECT * FROM `users` WHERE Username=?");
$selectUserExample->bindParam(1, $username, PDO::PARAM_STR);

if($selectUserExample->execute()) {
// Run if it has executed successfully 
} else {
// Run if query was not executed successfully
}

 

If you need any more help don't hesitate to ask me.

More on PDO binding

That just shows you didn't read the code I posted, let me repeat it; http://pastebin.com/YgRKWXkQ I am using prepared statements that have binded values...

[Script47]

That just shows you didn't read the code I posted, let me repeat it; http://pastebin.com/YgRKWXkQ I am using prepared statements that have binded values...

My bad, I skim read it and I never saw the array binding you did.

[bloodless2010]

My bad, I skim read it and I never saw the array binding you did.

Anyway, is that the most efficient it can be?

[KyleMassacre]

Anyway, is that the most efficient it can be?

The only inefficient thing I see is that you have 6 update queries updating 6 different columns in 1 table. You can consolidate those queries into 1 query

[bloodless2010]

The only inefficient thing I see is that you have 6 update queries updating 6 different columns in 1 table. You can consolidate those queries into 1 query

I'm not quite sure how to put them all into 1 query as I'm new to SQL. Could you maybe help me?

[KyleMassacre]

take a look at lines 30 to 51 in your pastebin I counted 7 queries updating the villiages table. You can do this:

$q = $db->prepare("update villages set this = this + ?, that = that + ? where this = ? and that = ?");
$array = array($var1, $var2, $var3, $var4);
$q = $db->execute($array);

This is of course not the right code so ill leave that up to you.

[Lucifer.iix]

Okay, so I'm making a game like Tribal Wars/Travian, I'm currently doing the resources, the way it works is for example: '100 logs per hour' or so, I'm doing per minute, so for example '10 logs per minute' or whatever, what would be the best way to do this so that it's live? so, if it was 10 logs per minute (thats 1 log per 6 seconds) how would I make it increment every 6 seconds efficiently without doing sooo many queries (cronjobs are out of the question) I'm thinking timestamps, I could do a timestamp system so it does it every minute, but how would I do it so it is live and you don't have to wait for a whole minute. I hope I have explained this enough, but if not here is a scenario:

User Z has 10 logs per minute, instead of them having to wait a whole minute for 10, it would instantly be updated +1 after 6 seconds.. but this would need to be flexible enough to any value (and instantly).. Sorry if this is confusing I really don't know how to explain this any better.

Running a script every 6 seconds to increment a counter in a table is maybe a little bit to mutch.

Maybe you can calculate the period until now.

Let's say person starts at time=0:00 and has 10 logs.

After 62 seconds, he will have (Now-StartTime) = (62 seconds div 6) = 10x a FULL period's. So that will make 10+10 = 20 logs.

You don't need to update a table for this every period of time. If you store the `formula` you only have to update the table when the formula changes and not the end result.

So amount of logs of a user = Amount in DB + Amount in Running Scripts.

 

Ps: If you want to update the web page (GUI on the client) then you can use AJAX if people already said. But that's just something totally different. That's about updating a small piece of your webside with HTML or parse HTML from XML into your webpage.

 

Happy Hacking: Roger Keulen.

 

Ps: Let me know if it works for you. Or need some extra help.