Efrem Posted July 25, 2008 Share Posted July 25, 2008 Ok well I took on a project, for learning purposes, to make a Text-Based RPG Game. So far I have the database completely setup and everything is working fine. But what I am searching for is how to update a value in a MySql DB every x amount of minutes. Now whether this is a Php question or a Sql question stumped me, because I just have no idea how I would do it, so I posted it here. Any help is appreciated! Thanks in advance. - Efrem Quote Link to comment Share on other sites More sharing options...
mmarif4u Posted July 25, 2008 Share Posted July 25, 2008 For this one,you create a script and run it by cron tab. Hope you know what is cronjob. Quote Link to comment Share on other sites More sharing options...
Efrem Posted July 25, 2008 Author Share Posted July 25, 2008 Mmmk ty. Another question mysql -h hostname -u username -p password -e "update mytable set status = archive where status = filed" I have to double check that command line for Cron ^ Uhh now if that is correct how would i go about doing addition in that command line.. like... 100 Users Every 10 Mins i want to add 10 turns to all of them. I dont know Cron Job but google helped me out a bit, but not enough. - Efrem Edit - I was trying things in my phpmyadmin.. so far i have this UPDATE turns SET turns = turns + 10 FROM chivalry_rpg.characters WHERE turns >= 0 but it doesnt work for whatever reason, tried it like this too UPDATE turns SET turns = turns + 10 FROM chivalry_rpg.characters Ok i got it working, gonna try the cron command now Quote Link to comment Share on other sites More sharing options...
mmarif4u Posted July 25, 2008 Share Posted July 25, 2008 Your query is wrong. try like this: UPDATE turns SET turns = turns + 10 WHERE turns >= 0 Of course turns will be the table name. Now about cronjob. Can you provide more details. 1-Are you have Linux installed and want to test on that. 2-Are you have hosting which is in Linux. 3-You want to do this on server OR on your local PC. 4-Are you windows user. Quote Link to comment Share on other sites More sharing options...
Efrem Posted July 25, 2008 Author Share Posted July 25, 2008 I put my email into the cron job editor feature on my host, got this error: Enter password: ERROR 1045 (28000): Access denied for user 'my_user'@'localhost' (using password: NO) This is my Cron Command: mysql -h localhost -u my_user -p my_password -e "UPDATE chivalry_rpg.characters SET turns = turns + 10" @mmarif4u 1. I dont have linux 2. Not entirely sure if my host has Linux installed? I can run the Cron Job though it just shoots that error at me ^ 3. On my host 4. Yes Quote Link to comment Share on other sites More sharing options...
Efrem Posted July 25, 2008 Author Share Posted July 25, 2008 Ok i got it to work. But now here is 1 last question. Lets say i wanted to add a Cap to it like... Turns cant be more than Level * 100... how would i go about doing that?.. like lets say if.. Person X | Turns = 321 | Level = 4 | Cap Turns = 400 Person Y | Turns = 192 | Level = 2 | Cap Turns = 200 Now with the Cron it will constantly add 10 Turns every 10 Mins. How would i make it so that it just equals the Cap if its gonna go over it? Cap Turns Is Not a value in my Table. - Efrem Quote Link to comment Share on other sites More sharing options...
mmarif4u Posted July 25, 2008 Share Posted July 25, 2008 i didnot understand your question clearly. But if you want to limit the Cap.You can use > OR < OR >= .something like which will check the Cap field. Quote Link to comment Share on other sites More sharing options...
Efrem Posted July 25, 2008 Author Share Posted July 25, 2008 i didnot understand your question clearly. But if you want to limit the Cap.You can use > OR < OR >= .something like which will check the Cap field. Well I dont have the Cap Field created as i want it to be dynamic without having to do much work. So ill rephrase the question.. i want to do something like this: if (turns + 10 >= level * 100) THEN SET turns = level*100 ELSE SET turns = turns +10 now i know you can use IF Else statements in sql commands.. but i have no idea how to do it correctly - efrem Quote Link to comment Share on other sites More sharing options...
samshel Posted July 25, 2008 Share Posted July 25, 2008 try using 2 queries... "UPDATE chivalry_rpg.characters SET turns = turns + 10" and "UPDATE chivalry_rpg.characters SET turns = level * 100 WHERE turns > level * 100" If you want to use single query, you can use IF() but that is not very performance friendly. Quote Link to comment Share on other sites More sharing options...
mmarif4u Posted July 25, 2008 Share Posted July 25, 2008 For this option better you do: 1- Retrieve the records from the table. 2-compare it in the if statement as samshel mentioned,put the query there, if if statement fails, 3-write else statement for that and put the query there to update the table. Quote Link to comment Share on other sites More sharing options...
samshel Posted July 25, 2008 Share Posted July 25, 2008 as mmarif4u suggested it will work, but what i intended , was not to retrieve the records but fire these queries back to back without checking anything. This way it will update turns + 10. in some case it may go beyond levels * 100, but the second query will update those records to levels * 100 this means you need only total 2 queries.. if you select records and fire update for each seperately depending on the condition, it will require a lot of queries... Quote Link to comment Share on other sites More sharing options...
mmarif4u Posted July 25, 2008 Share Posted July 25, 2008 samshel is correct. I modified his code like: "UPDATE chivalry_rpg.characters SET turns = level * 100 WHERE turns > level * 100" and "UPDATE chivalry_rpg.characters SET turns = turns + 10" In my point of view it depends on the recordset in the table. Quote Link to comment Share on other sites More sharing options...
samshel Posted July 25, 2008 Share Posted July 25, 2008 i think the order of the queries should be "UPDATE chivalry_rpg.characters SET turns = turns + 10" and "UPDATE chivalry_rpg.characters SET turns = level * 100 WHERE turns > level * 100" sorry if i misunderstood your order. Quote Link to comment Share on other sites More sharing options...
Efrem Posted July 25, 2008 Author Share Posted July 25, 2008 Awesome . Works perfectly! Thank you very much guys. Quote Link to comment Share on other sites More sharing options...
mmarif4u Posted July 25, 2008 Share Posted July 25, 2008 yeh you are right.in both it will run.i was thinking about if condition. Anyway glad that it works for you. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.