Large numbers and PHP/MySQL

[phpscriptcoder]

I'm trying to fix someone's game script. It has some code which inserts a new row into the database, and 3 of the fields can be very large (in the quadrillions). But when the row is inserted, the 3 large fields are set to 0. The field types are BIGINT unsigned with length 21. Before being inserted into the database, the number is added to a variable which is often 0, if that matters.

 

Thanks for helping!

[samshel]

could u post some code ?

[corbin]

Considering bigints can go to about 10^19 and quadrillions are 10^15 through 10^16-1, it must be an error with code.

[phpscriptcoder]

Here is some code.

 

			$Mining['metal']   = 0;
		$Mining['crystal'] = 0;
		$Mining['deuter']  = 0;

...

			if ($FleetStorage > 0) {
				$metal   = $TargetPlanet['metal'] / 2;
				$crystal = $TargetPlanet['crystal'] / 2;
				$deuter  = $TargetPlanet["deuterium"] / 2;
				if (($metal) > $FleetStorage / 3) {
					$Mining['metal']   = $FleetStorage / 3;
					$FleetStorage      = $FleetStorage - $Mining['metal'];
				} else {
					$Mining['metal']   = $metal;
					$FleetStorage      = $FleetStorage - $Mining['metal'];
				}

				if (($crystal) > $FleetStorage / 2) {
					$Mining['crystal'] = $FleetStorage / 2;
					$FleetStorage      = $FleetStorage - $Mining['crystal'];
				} else {
					$Mining['crystal'] = $crystal;
					$FleetStorage      = $FleetStorage - $Mining['crystal'];
				}

				if (($deuter) > $FleetStorage) {
					$Mining['deuter']  = $FleetStorage;
					$FleetStorage      = $FleetStorage - $Mining['deuter'];
				} else {
					$Mining['deuter']  = $deuter;
					$FleetStorage      = $FleetStorage - $Mining['deuter'];
				}
			}
...
		$Mining['metal']   = round($Mining['metal']);
		$Mining['crystal'] = round($Mining['crystal']);
		$Mining['deuter']  = round($Mining['deuter']);

...

		$Mining['metal']   = $Mining['metal']   + $FleetRow["fleet_resource_metal"];
		$Mining['crystal'] = $Mining['crystal'] + $FleetRow["fleet_resource_crystal"];
		$Mining['deuter']  = $Mining['deuter']  + $FleetRow["fleet_resource_deuterium"];

		$QryUpdateFleet  = "UPDATE {{table}} SET ";
		$QryUpdateFleet .= "`fleet_amount` = '". $FleetAmount ."', ";
		$QryUpdateFleet .= "`fleet_array` = '". $FleetArray ."', ";
		$QryUpdateFleet .= "`fleet_mess` = '1', ";
		$QryUpdateFleet .= "`fleet_resource_metal` = '". $Mining['metal'] ."', ";
		$QryUpdateFleet .= "`fleet_resource_crystal` = '". $Mining['crystal'] ."', ";
		$QryUpdateFleet .= "`fleet_resource_deuterium` = '". $Mining['deuter'] ."' ";
		$QryUpdateFleet .= "WHERE fleet_id = '". $FleetRow['fleet_id'] ."' ";
		$QryUpdateFleet .= "LIMIT 1 ;";
		doquery( $QryUpdateFleet , 'fleets');

[Mchl]

Add

echo $QryUpdateFleet;

 

before

doquery( $QryUpdateFleet , 'fleets');

 

this will show if the problem is with how query is formed, or maybe it's in MySQL

[phpscriptcoder]

UPDATE {{table}} SET `fleet_amount` = '290401619', `fleet_array` = '203,290401619;', `fleet_mess` = '1', `fleet_resource_metal` = '2.42001349167E+15', `fleet_resource_crystal` = '2.42001349167E+15', `fleet_resource_deuterium` = '2.42001349167E+15' WHERE fleet_id = '6772' LIMIT 1 ; 

 

I'm not sure if that's the problem, because it works for smaller numbers.

[kenrbnsn]

The problem is that you're quoting the numbers which makes them into strings. Putting a string into a numeric field will cause a zero to be entered. Remove the quotes.

 

Ken

[Mchl]

The problem is that you're quoting the numbers which makes them into strings. Putting a string into a numeric field will cause a zero to be entered.

 

Nope. Unless there is some SQL compatibility mode that enforces that.

[kenrbnsn]

Sorry, my mistake.

 

Ken

[samshel]

'2.42001349167E+15' is not recognized as integer by MySQL. Try to format the  number properly.

[kenrbnsn]

Actually, it is. I just tried it via phpMyAdmin and it worked fine.

 

Ken

[phpscriptcoder]

PHP is changing the number to be formatted that way.

 

Also, I noticed that sometimes the number is set to 2 or 3 or 4, not only 0.