Save Script Runtime Data (Java Class + PHP + MySQL Included)

BrainDeadGenius · February 13, 2017

About:

This is a Java Class + PHP & MySQL website I wrote in order to save user data to your database for tracking purposes. I'll explain the files first, then you may view them below the explanation. Also, included at the very end is an example SQL file required to complete this project.

Usage (OSB Script):

First, in order to call the Class, you need to use the code below. I use it in the onExit function

log("Saving your runtime data.");
String updateUser = SaveUserData.saveUserDataMethod(accessKey, authToken, scriptName, username, startTime, String.valueOf(xpGained), String.valueOf(levelsGained), String.valueOf(fishCaught), String.valueOf(profit));
		
if (updateUser.equals("success")) {
	log("We have successfully updated your account statistics online.");
} else {
	log(updateUser);
}

SaveUserData Class Call Function:

I tried converting the values of the String.valueOf variables in the class itself, or ignoring it, but it was failing to execute properly. In any case, you must send the following 4 variables at a minimum in order to know where to send the statistic data. Other than that, the calling script here checks the response of the website to let the user know, via the log, if their data has been saved or not. This is called in my onExit function.

Required Data:

accessKey
- A randomly generated and encrypted key used to validate the script (mostly used just to prevent the PHP page from being access from a browser).
- This is generally a key that you could use for all your scripts.
- This is a global variable.
authToken
- A randomly generated and encrypted key used to validate the script (mostly used just to prevent the PHP page from being access from a browser).
- This is a unique authentication token for each script.
- This is a global variable.
scriptName
- This is the name of your script that will be saved in the database.
- NOTE: I used this variable in the URL of my Java class, so if you intend of doing that as well, make sure that it is the same value for the actual URL.
- This is a global variable.
username
- This is the user's account name which will be the unique identified that is used to save the data.
- This is set in the onStart function.

Statistical Data:

startTime
- This is used to calculate the runtime of the script.
- This is set in the onStart function.
xpGained
- Can be coded in however you like, but it should be a global variable.
- This is updated in my onPaint function.
levelsGained
- Can be coded in however you like, but it should be a global variable.
- This is updated in my onPaint function.
fishCaught
- Can be coded in however you like, but it should be a global variable.
- This is updated in my onPaint function.
profit
- Can be coded in however you like, but it should be a global variable.
- This is updated in the onMessage function, since this came from a fishing script.

SaveUserDataMethod (Java Class):

As I previously mentioned, the variable scriptName is utilized in my URL as a means of shortening the amount of things I must change between scripts (since I've only just started scripting and plan on making more). Keep that in mind if you decide to utilize it, and when you're making your file tree if you do. The website returns a JSON response in the format below. Instead of handling the data with a JSON parser, since I know the data has two defined results, I parse it as a regular string instead. I then return to the main function either a value of success or the error message.

On Success:

success
- true

On Failure:

success
- false
message
- Message based on the event that caused the failure.

index.php (PHP File):

This file includes the config, database, and encryption files. I have provided these with the exception of the encryption file. You may encrypt your data however you'd like. The file returns a JSON result as described in the SaveUserDataMethod above. Please note that the database file MUST be included after the config file. See the database.php section below.

File Handling:

The file first checks to insure the accessKey, authToken, scriptName, username, and other required variables are available to read before continuing.
After that, we set up our local variables.
We then check to make sure the accessKey and authToken sent match the ones defined in the config.
- Again, I use my own encryption class. You may use your own class to encrypt and decrypt this data.
- The accessKey validation is setup like this:
  - $encryption->decrypt256Bit($_REQUEST["accessKey"] == $encryption->decrypt256Bit($scriptAccessKey[$scriptName])
  - NOTE:
    - $scriptAccessKey[$scriptName]
    - scriptAccessKey is an array defined in the config, and its value is grabbed by using the scriptName as the key.
- The authToken validation is setup like this:
  - $encryption->decrypt256Bit($_REQUEST["accessKey"] == $encryption->decrypt256Bit($scriptAccessKey[$scriptName])
  - NOTE:
    - $scriptAuthToken[$scriptName]
    - scriptAuthToken is an array defined in the config, and its value is grabbed by using the scriptName as the key.
We then just check to make sure the scriptName is the correct one for the data trying to be saved.
- Again, this is more for preventing users from access the page outside of the client.
We then initiate the database class.
- See below for usage.
We then check to see if the user already has data saved under the specified username for the specified script.
- Required variables
  - scriptName
  - username
If the query executes, then we perform another check: Is there one row or two?
- The query will generally always execute, unless you have a database error, but I always check to insure it executes before I continue.
If there is already a user, then we update their information.
If the user does not exist, then we insert their information.
Both update and insert utilize the same variables.
- Required variables
  - username
- Statistical Variables
  - runtime
  - xpGained
  - levelsGained
  - profit
If the insert or update query is successful, then we update another table which is used for tracking all of the skill experience gained and levels gained.
- This is not required, and if you intend on not using this, you should remove the following code:
  - $db->beginTransaction();
  - $db->endTransaction();
  - $db->cancelTransaction();
- Those lines are not required in updating 1 table, however, if updating multiple tables, like I am, they should be used.
- Why are they used? Say you have 5 queries that need to be executed, and they're all tied to each other in terms of information. If one of the queries fails, you don't want the other 4 to execute. Well, these functions allow us to roll back the data in a previously executed query should one of the queries fail.
Finally, if the second table update is complete, we change the commit variable to true.
- Commit variable states
  - true
    - We end the database transaction which completes the update.
  - false
    - We cancel the database transaction which un-does any queries that executed successfully.
    - We then return any error message that indicates a failure to update the user's information.
- The returnArray is then displayed on the page for the SaveUserDataMethod functionto process.

database.php (PHP Class File):

This file is a class which I utilize in projects to make process database queries much easier. Be it noted that the config file MUST be included before this one in the main PHP handling file because this file sets global variables that are defined in the config file. I'm not going to go into too much detail with this file; you can read up about PHP and PDO methods online if you wish. However, I will make one thing clear.

Variable Names:

host
- This is defined as DB_HOST which is defined in the config file.
dbhname
- This is defined as DB_NAME which is defined in the config file.
user
- This is defined as DB_USERNAME which is defined in the config file.
pass
- This is defined as DB_PASSWORD which is defined in the config file.

config.php (PHP File):

This file is a configuration file I utilize to set up constant and global values. Typically used for things such as the database setup, among other things.

Variable Names:

DB_HOST
- This is the hostname used to access your SQL database, typically MySQL if using a website.
DB_NAME
- This is the database name you're trying to connect to which stores the tables for the data to be saved in.
DB_USERNAME
- This is the username that has access rights to DB_NAME.
DB_PASSWORD
- This is the password for DB_USERNAME on DB_NAME.
availableScripts
- This is utilized in my online users function used in my scripts, but it can be used for other purposes as well. It declares the script names available.
scriptAccessKey
- This is an array that holds a key value pair of the access keys authorized. Format as follows:
  - Key
    - The script name
  - Value
    - A randomly generated, encrypted, key utilized to authorize access to the PHP file (index.php in this case).
    - This must match with the value sent from the OSB client.
scriptAuthToken
- This is an array that holds a key value pair of the authorization tokens authorized. Format as follows:
  - Key
    - The script name
  - Value
    - A randomly generated, encrypted, key utilized to authorize access to the PHP file (index.php in this case).
    - This must match with the value sent from the OSB client.

Java Class

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;

class SaveUserData {
    public static String saveUserDataMethod(String accessKey, String authToken, String scriptName, String username, long startTime, 
			String xpGained, String levelsGained, String fishCaught, String profit) {
    	String returnedValue = "Saving your data failed. Unknown reason.";
    	try {
		    // open a connection to the site
    		StringBuilder sb = new StringBuilder("https://www.mmaengineer.com/app/osb/" + scriptName + "/index.php?accessKey=");
			sb.append(accessKey);
			sb.append("&authToken=" + authToken);
		    sb.append("&scriptName=" + scriptName);
		    sb.append("&username=" + username);
		    sb.append("&runtime=" + (System.currentTimeMillis() - startTime));
		    sb.append("&xpGained=" + xpGained);
		    sb.append("&levelsGained=" + levelsGained);
		    sb.append("&fishCaught=" + fishCaught);
		    sb.append("&profit=" + profit);
			InputStream inputStream = new URL(sb.toString()).openStream();
			InputStreamReader inputStreamReader = new InputStreamReader(inputStream);
			BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
			String line;
			String newString = null;
			
			while ((line = bufferedReader.readLine()) != null) {
				if (line.contains("{")) {
					sb = new StringBuilder(line);
					//Remove { and }
					sb.deleteCharAt(0);
					newString = sb.substring(0, sb.length()-1);
				}
			}
			
			newString = newString.replace("\"", "");
			
			if (newString.equals("success:true")) {
				returnedValue = "success";
			} else {
				String[] returned = newString.split("message:");
				returnedValue = returned[1];
			}
	    } catch (MalformedURLException e) {
	        e.printStackTrace();
	    } catch (IOException e) {
	        e.printStackTrace();
	    }
    	
		return returnedValue;
    }
}

index.php

<?php

require_once "../config.php";
require_once "../database.php";
require_once "../encryption.php";

$encryption = new encryption();
$returnArray["success"] = false; 

if (isset($_REQUEST["accessKey"], $_REQUEST["authToken"], $_REQUEST["scriptName"], $_REQUEST["username"], $_REQUEST["runtime"], $_REQUEST["xpGained"], $_REQUEST["levelsGained"], $_REQUEST["fishCaught"], $_REQUEST["profit"])) {
	$scriptName = $_REQUEST["scriptName"];
	$username = $_REQUEST["username"];
	$runtime = ($_REQUEST["runtime"] / 1000);
	$xpGained = $_REQUEST["xpGained"];
	$levelsGained = $_REQUEST["levelsGained"];
	$fishCaught = $_REQUEST["fishCaught"];
	$profit = $_REQUEST["profit"];
		
	if (($encryption->decrypt256Bit($_REQUEST["accessKey"]) == $encryption->decrypt256Bit($scriptAccessKey[$scriptName])) && ($encryption->decrypt256Bit($_REQUEST["authToken"]) == $encryption->decrypt256Bit($scriptAuthToken[$scriptName]))) {
		if ($scriptName == "engineerFishing") {
			$db = new database();
			$commit = false;
			
			$db->query("SELECT `id` FROM `engineerFishing` WHERE `username` = :username");
			$db->bind(":username", $username);
				
			if ($db->execute()) {
				$rows = $db->rowCount();
				$db->beginTransaction();

				if ($rows == 1) {
					$db->query("UPDATE `engineerFishing` SET `runtime` = runtime+:runtime, `xp_gained` = xp_gained+:xp_gained, `levels_gained` = levels_gained+:levels_gained, `fish_caught` = fish_caught+:fish_caught, `profit` = profit+:profit WHERE `username` = :username");
				} else {
					$db->query("INSERT INTO `engineerFishing` (`id`, `username`, `runtime`, `xp_gained`, `levels_gained`, `fish_caught`, `profit`) VALUES(0, :username, :runtime, :xp_gained, :levels_gained, :fish_caught, :profit)");
				}
				
				$db->bind(":username", $username);
				$db->bind(":runtime", $runtime);
				$db->bind(":xp_gained", $xpGained);
				$db->bind(":levels_gained", $levelsGained);
				$db->bind(":fish_caught", $fishCaught);
				$db->bind(":profit", $profit);
				
				if ($db->execute()) {
					$db->query("UPDATE `skills` SET `fishing_xp` = fishing_xp+:fishing_xp, `fishing_levels` = fishing_levels+:fishing_levels WHERE `id` = 1");
					$db->bind(":fishing_xp", $xpGained);
					$db->bind(":fishing_levels", $levelsGained);
					
					if ($db->execute()) {
						$commit = true;
					}
				}
						
				if ($commit) {
					$returnArray["success"] = true; 
					$db->endTransaction();
				} else {
					$returnArray["message"] = "Failed to save your data for the Engineer Fishing script."; 
					$db->cancelTransaction();
				}
			} else {
				$returnArray["message"] = "Failed to access our database to save your data for the Engineer Fishing script."; 
			}
		} else {
			$returnArray["message"] = "The script you are trying to save your data to does not match our database."; 
		}
	} else {
		$returnArray["message"] = "Bad access key or authorization token for utilizing the online users function."; 
	}
} else {
	$returnArray["access"] = "We did not receive all of the parameters to save your data for the Engineer Fishing script."; 
}

echo json_encode($returnArray);

?>

database.php

<?php
if (count(get_included_files()) <= 1) {
	exit;
}

class database {
	private $host      	= DB_HOST;
	private $dbhname    = DB_NAME;
	private $user      	= DB_USERNAME;
	private $pass      	= DB_PASSWORD;
 
	private $dbh;
	private $stmt;
	private $error;
	
	public function __construct() {
		// Set Connection
		$con = 'mysql:host=' . $this->host . ';dbname=' . $this->dbhname;
		
		// Set options
		// If you want to display errors, use the following values
		// true 
		// ERRMODE_EXCEPTION
		$options = array(
			PDO::ATTR_PERSISTENT    => false,
			PDO::ATTR_ERRMODE       => PDO::ERRMODE_SILENT
		);
		
		// Create a new PDO instanace
		try {
			$this->dbh = new PDO($con, $this->user, $this->pass, $options);
		} catch(PDOException $e) {
			$this->error = $e->getMessage();
		}
	}
	
	public function query($query) {
		$this->stmt = $this->dbh->prepare($query);
	}
	
	public function bind($param, $value, $type = null) {
		if (is_null($type)) {
			switch (true) {
				case is_int($value):
					$type = PDO::PARAM_INT;
					break;
				case is_bool($value):
					$type = PDO::PARAM_BOOL;
					break;
				case is_null($value):
					$type = PDO::PARAM_NULL;
					break;
				default:
					$type = PDO::PARAM_STR;
			}
		}
		
		$this->stmt->bindValue($param, $value, $type);
	}
	
	public function execute() {
		return $this->stmt->execute();
	}
	
	public function resultSet() {
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
	}
	
	public function single() {
		$this->execute();
		return $this->stmt->fetch(PDO::FETCH_ASSOC);
	}
	
	public function rowCount() {
		return $this->stmt->rowCount();
	}
	
	public function lastInsertId() {
		return $this->dbh->lastInsertId();
	}
	
	public function beginTransaction() {
		return $this->dbh->beginTransaction();
	}
	
	public function endTransaction() {
		return $this->dbh->commit();
	}
	
	public function cancelTransaction() {
		return $this->dbh->rollBack();
	}
	
	public function debugDumpParams() {
		return $this->stmt->debugDumpParams();
	}
}
?>

config.php

<?php
define("DB_HOST", "localhost");
define("DB_NAME", "databaseName");
define("DB_USERNAME", "databaseUsername");
define("DB_PASSWORD", "databasePassword");

$availableScripts = array(
	1 => "testPlatform",
	2 => "engineerFishing",
	3 => "engineerPickpocket"
);

$scriptAccessKey = array(
	"testPlatform" => "scriptAccessKey",
	"engineerFishing" => "scriptAccessKey",
	"engineerPickpocket" => "scriptAccessKey"
);

$scriptAuthToken = array(
	"testPlatform" => "scriptAuthToken",
	"engineerFishing" => "scriptAuthToken",
	"engineerPickpocket" => "scriptAuthToken"
);

?>

engineerFishing.sql

--
-- Table structure for table `engineerFishing`
--

CREATE TABLE `engineerFishing` (
  `id` int(8) NOT NULL,
  `username` varchar(100) NOT NULL,
  `runtime` bigint(35) NOT NULL,
  `xp_gained` bigint(35) NOT NULL,
  `levels_gained` int(8) NOT NULL,
  `fish_caught` int(8) NOT NULL,
  `profit` bigint(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `engineerFishing`
--
ALTER TABLE `engineerFishing`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `engineerFishing`
--
ALTER TABLE `engineerFishing`
  MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

skills.sql

--
-- Table structure for table `skills`
--

CREATE TABLE `skills` (
  `id` int(8) NOT NULL,
  `agility_xp` bigint(35) NOT NULL,
  `agility_levels` int(8) NOT NULL,
  `attack_xp` bigint(35) NOT NULL,
  `attack_levels` int(8) NOT NULL,
  `construction_xp` bigint(35) NOT NULL,
  `construction_levels` int(8) NOT NULL,
  `cooking_xp` bigint(35) NOT NULL,
  `cooking_levels` int(8) NOT NULL,
  `crafting_xp` bigint(35) NOT NULL,
  `crafting_levels` int(8) NOT NULL,
  `defense_xp` bigint(35) NOT NULL,
  `defense_levels` int(8) NOT NULL,
  `farming_xp` bigint(35) NOT NULL,
  `farming_levels` int(8) NOT NULL,
  `firemaking_xp` bigint(35) NOT NULL,
  `firemaking_levels` int(8) NOT NULL,
  `fishing_xp` bigint(35) NOT NULL,
  `fishing_levels` int(8) NOT NULL,
  `fletching_xp` bigint(35) NOT NULL,
  `fletching_levels` int(8) NOT NULL,
  `herblore_xp` bigint(35) NOT NULL,
  `herblore_levels` int(8) NOT NULL,
  `hitpoints_xp` bigint(35) NOT NULL,
  `hitpoints_levels` int(8) NOT NULL,
  `hunter_xp` bigint(35) NOT NULL,
  `hunter_levels` int(8) NOT NULL,
  `magic_xp` bigint(35) NOT NULL,
  `magic_levels` int(8) NOT NULL,
  `mining_xp` bigint(35) NOT NULL,
  `mining_levels` int(8) NOT NULL,
  `prayer_xp` bigint(35) NOT NULL,
  `prayer_levels` int(8) NOT NULL,
  `ranged_xp` bigint(35) NOT NULL,
  `ranged_levels` int(8) NOT NULL,
  `slayer_xp` bigint(35) NOT NULL,
  `slayer_levels` int(8) NOT NULL,
  `smithing_xp` bigint(35) NOT NULL,
  `smithing_levels` int(8) NOT NULL,
  `strength_xp` bigint(35) NOT NULL,
  `strength_levels` int(8) NOT NULL,
  `thieving_xp` bigint(35) NOT NULL,
  `thieving_levels` int(8) NOT NULL,
  `woodcutting_xp` bigint(35) NOT NULL,
  `woodcutting_levels` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `skills`
--
ALTER TABLE `skills`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `skills`
--
ALTER TABLE `skills`
  MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Edited February 14, 2017 by BrainDeadGenius

Polymorphism · February 13, 2017

This isn't a bad write up, very descriptive too

There are many people who lack to knowledge on how data tracking works and this will certainly assist them

BrainDeadGenius · February 13, 2017

11 minutes ago, Polymorphism said:

This isn't a bad write up, very descriptive too

There are many people who lack to knowledge on how data tracking works and this will certainly assist them

I have a problem with not commenting in my files, so it's all in the thread instead. And I'd have used spoilers, but they're fucked up. But I do have it set it fairly cleanly (the thread and the code). So I figured it'd help out someone at the least.

whipz · February 13, 2017

1 hour ago, BrainDeadGenius said:

I have a problem with not commenting in my files, so it's all in the thread instead. And I'd have used spoilers, but they're fucked up. But I do have it set it fairly cleanly (the thread and the code). So I figured it'd help out someone at the least.

Nice write up man !

But spoliers do work ;)

Whats up man ?

I can do this all day

Edited February 13, 2017 by whipz

BrainDeadGenius · February 13, 2017

6 minutes ago, whipz said:

Nice write up man !

But spoliers do work ;)

Reveal hidden contents

Whats up man ?

Reveal hidden contents

I can do this all day

I know they work. But back to back, with text inbetween, they don't work properly. But I'll give it another shot anyway.

whipz · February 13, 2017

1 minute ago, BrainDeadGenius said:

I know they work. But back to back, with text inbetween, they don't work properly. But I'll give it another shot anyway.

you have to use shift enter for some reason its the only way I can get it to work anyways ):

BrainDeadGenius · February 20, 2017

On 2/13/2017 at 5:46 PM, whipz said:

you have to use shift enter for some reason its the only way I can get it to work anyways ):

Doesn't work. Not with the content I have anyway. The BBCode is too messed up.

*Booleans YAY* · March 27, 2017

Thanks for this, might actually use this for my personal website statistics.

Also had to do this locally cause bothered me enough

log(updateUser.equals("success") ? "We have successfully updated your account statistics online." : updateUser);

Valid · September 19, 2019

Awesome , exactly what I was looking for.

Sign In

Save Script Runtime Data (Java Class + PHP + MySQL Included)

Recommended Posts

BrainDeadGenius

Link to comment

Share on other sites

Polymorphism

Link to comment

Share on other sites

BrainDeadGenius

Link to comment

Share on other sites

whipz

Link to comment

Share on other sites

BrainDeadGenius

Link to comment

Share on other sites

whipz

Link to comment

Share on other sites

BrainDeadGenius

Link to comment

Share on other sites

Booleans YAY

Link to comment

Share on other sites

Valid

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Browse

Store

Search