PAGE-4: How To Make A Value In The Database Raise Every Minute. related to Update, Temphealth, Mysql, Query

vujsa

Feb 11 2008, 02:43 AM

Keep in mind that this entire method only works after you request the script. So, the script should be placed near the top of the master file for the system...

For example, if you use the usual index.php file as the traffic cop that controls the flow of data through your script, then somewhere near the beginning, after your configuration information, place either this code or an include for the file that this code is in.

The script should update everyone's hp when someone accesses the game and the proper amount of time has passed since the last update.

Okay, lets try and see if we missed something else. Some debugging could be in order:

Try something like this:

CODE

mysql_query("<<<INSERT YOUR QUERY HERE>>>") or die('Query failed: ' . mysql_error());

This is a good idea any time you are building a script since it prevents the system from continuing on and it lets you know that there is a propblem.

Another method we can use to check what is going on it by echoing the querys like so:

CODE

$query = "<<<INSERT YOUR QUERY HERE>>>";
mysql_query($query) or die('Query failed: ' . mysql_error());
echo $query;

If you want a fancier method of echoing your queries, you can concatenate (link together) all of your queies then output them at the end like so:

CODE

$some_count = 1;
$query = "<<<INSERT YOUR QUERY HERE>>>";
mysql_query($query) or die('Query failed: ' . mysql_error());

$queries .= "$some_count: <pre>$query</pre> ";
$some_count++;

....
MORE CODE
....

echo $queries;

This would give you very easy to read query debug information.

Anyway, back to the main event.
It is possible that there is a problem with your PHP code that is breaking your queries in which you might see some strange data in your queries. If this is the case, then try and figure out which section of code generated that query and try and determine why your script is malfunctioning there.
It is also possible that the SQL queries that I have given you are bad! Since I didn't go to the trouble to create the required tables to do a live test with these queries, there could be an error. The error may not cause an error message to be shown if the query just doesn't actually match any of the records as it is written. So, you should copy and paste the queries into phpMyAdmin and manually test each one.

You may need to give us some database structure information so that we can see if there is something we are just overlooking.
Going into your database without selecting any tables to view, select Export and be sure to uncheck the Data checkbox and click Go. All of the other default settings will be fine and this will display the structure of all of your tables in that database. If you copy and paste that in your next reply, then I could see if there is something that I missed in your previous posts about your database. If I can't see it, I can actually use the export data to recreate the structure of your database on my account.

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 11 2008, 11:05 AM

CODE

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `dbname`
--

-- --------------------------------------------------------

--
-- Table structure for table `characters`
--

CREATE TABLE `characters` (
  `user` varchar(32) NOT NULL,
  `temphealth` varchar(225) NOT NULL default '100',
  `attack` int(11) NOT NULL,
  `exp` int(225) NOT NULL default '0',
  `level` int(11) NOT NULL default '1',
  `lastlogin` varchar(50) NOT NULL,
  `maxhp` int(11) NOT NULL,
  `expaw` int(11) NOT NULL,
  `hptime` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Or maybe I've done something wrong.. I dunno, but here it is.

Comment/Reply (w/o sign-up)

vujsa

Feb 12 2008, 05:26 AM

QUOTE(Feelay @ Feb 11 2008, 06:05 AM)

CODE

Or maybe I've done something wrong.. I dunno, but here it is.

Well, initially, I don't see a field for 'hp'. I don't know what you use the other fields for. Also, where are you storing the value for the last update? If you have a configuration table, that would be a good place to store your last update value or you can just create a new table just for that! Remember, the method I showed you will update every account at the same time and you only need to store one value for the last update time.

So, either you need to adjust your table to include the 'hp' field or modify the query to use the correct field where 'hp' currently resides.

So here is what I think needs to be done. Change 'hp' to 'temphealth' and 'user' to 'characters' in the queries:

CODE

$multiplier = 1 / 5;
$frequency = 3600 * $multiplier;
$increment = 5 * $multiplier;

$query_count = 1;
$query_debug = 1; // Set this to 0 to turn the query echo off!

$current_time = time(); // get the current server time in second
$last_update_time = mysql_result(mysql_query("SELECT update_time FROM update_table"), 0); // This could be anywhere even in a separate text file!
$time_difference = $current_time - $last_update_time;
$hours_passed = $time_difference / $frequency; // convert sec to min then to hours --> 3600 second / 60 = 60 minutes / 60 = 1 hour
$full_hours_passed = floor($hours_passed); // round the number of decimal hours down to the nearest whole hour
$remaining_seconds = $time_difference % $frequency; // number of sec left when time difference is divided by 3600 sec --> 7 % 3 = 1 and 6 % 3 = 0
if($full_hours_passed > 0){
 $new_update_time = $current_time + $remaining_seconds;
 $hp_increase = $full_hours_passed * $increment;
 $query = "UPDATE characters SET temphealth = CASE WHEN temphealth + $hp_increase <= 100 THEN temphealth + $hp_increase ELSE 100 WHERE temphealth < 100";
 mysql_query($query) or die('Query failed: ' . mysql_error());
 $queries .= "$query_count: <pre>$query</pre> ";
 $query_count++;

 $query = "UPDATE update_table SET update_time = $new_update_time";
 mysql_query($query) or die('Query failed: ' . mysql_error());
 $queries .= "$query_count: <pre>$query</pre> ";
 $query_count++;
}

if($query_debug == 1){
 echo $queries;
}

Please look at your queries! They have to match the data in your database. For example, have you created a table to file to hold the global value to last update time? You don't seem to need the field in the characters table named 'hptime'! Also, temphealth doesn't seem to need to be a varchar, I thin you should set this to int(11) since I think it should be a numeric value there. I coulld be wrong, I don't know how the rest of your system functions.

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 12 2008, 10:36 AM

When you gave me the code, I changed the queries. I changed hp to temphealth. And i changed user to characters.
the 'hptime' is the colum storing the last update. you arew right, temphealth should be int, but that don't matter. the value is still not changing =/

Comment/Reply (w/o sign-up)

vujsa

Feb 12 2008, 06:00 PM

QUOTE(Feelay @ Feb 12 2008, 05:36 AM)

Well, this is getting a little frustrating!!!

First, does the query being used work?

Echo your queries as explained previously then plug those into phpMyAdmin and see it they work.
If they work in phpMyAdmin, then the trouble is in the code.
If they don't work, then we need to fix the queries.

Post the queries in your next reply.

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 12 2008, 06:12 PM

the Select query worked. I have no idea if the time query worked, because the code died here:

CODE

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE temphealth < maxhp' at line 1

this is the query:

CODE

UPDATE characters SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp

all the colums are right.. I have no idea whats wrong here..

Comment/Reply (w/o sign-up)

vujsa

Feb 12 2008, 07:15 PM

QUOTE(Feelay @ Feb 12 2008, 01:12 PM)

the Select query worked. I have no idea if the time query worked, because the code died here:

CODE

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE temphealth < maxhp' at line 1

this is the query:

CODE

UPDATE characters SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp

all the colums are right.. I have no idea whats wrong here..

Still need those query echos.
And check those queries as I explained in phpMyAdmin. It usually gives a little more information about errors.

vujsa

Lets try this:

Change your query to this:

CODE

UPDATE characters SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp END CASE WHERE temphealth < maxhp

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 12 2008, 07:20 PM

this is what php my admin says (exactly the same thing?)

CODE

SQL query:

UPDATE characters SET temphealth = CASE WHEN temphealth + $hp_increase <= maxhp THEN temphealth + $hp_increase ELSE maxhp WHERE temphealth < maxhp

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE temphealth < maxhp' at line 1

if I am doing it the wrong way, please tell me.

Comment/Reply (w/o sign-up)

vujsa

Feb 13 2008, 05:22 AM

Okay, my fault...

Since I didn't d any queries myself, I forgot a couple of things.

Anyhow, I created a table on my account and used some sample data to run queries against and found the problem. "Quotes"!!!!!

Here is a working query for you:

CODE

UPDATE `characters` SET `temphealth` = CASE WHEN `temphealth` + $hp_increase <= `maxhp` THEN `temphealth` + $hp_increase ELSE `maxhp` END WHERE `temphealth` < `maxhp`

As long as you don't have any other problems in the code, this should work now.

vujsa

Comment/Reply (w/o sign-up)

Feelay

Feb 13 2008, 12:48 PM

=/ now all the quotes is working fine.. but the value is still not updating =/

here is the code:

CODE

<?php
$multiplier = 1 / 5;
$frequency = 3600 * $multiplier;
$increment = 5 * $multiplier;

$current_time = time(); // get the current server time in second
$last_update_time = mysql_result(mysql_query("SELECT hptime FROM characters"), 0); // This could be anywhere even in a separate text file!
$time_difference = $current_time - $last_update_time;
$hours_passed = $time_difference / $frequency; // convert sec to min then to hours --> 3600 second / 60 = 60 minutes / 60 = 1 hour
$full_hours_passed = floor($hours_passed); // round the number of decimal hours down to the nearest whole hour
$remaining_seconds = $time_difference % $frequency; // number of sec left when time difference is divided by 3600 sec --> 7 % 3 = 1 and 6 % 3 = 0
if($full_hours_passed > 0){
 $new_update_time = $current_time + $remaining_seconds;
 $hp_increase = $full_hours_passed * $increment;

mysql_query("UPDATE `characters` SET `temphealth` = CASE WHEN `temphealth` + $hp_increase <= `maxhp` THEN `temphealth` + $hp_increase ELSE `maxhp` END WHERE `temphealth` > `maxhp`");

mysql_query("UPDATE characters SET hptime = $new_update_time");
}
?>

I am not sure whats wrong, but i think it has something with the time to do..
sorry for asking so much vujsa

but i don't know how this time() thing works yet..

Comment/Reply (w/o sign-up)