Anti Gold Dupe

[Scura]

On 2/18/2024 at 6:50 PM, dethunter12 said:




 



something like this should work

 


-----------------
--add this in _db
------------------
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_gold_results
-- ----------------------------
DROP TABLE IF EXISTS `t_gold_results`;
CREATE TABLE `t_gold_results` (
`a_index` int(11) NOT NULL AUTO_INCREMENT,
`a_user_index` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0',
`a_nick` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
`a_nas` bigint(22) NULL DEFAULT NULL,
`a_gold_type` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
PRIMARY KEY (`a_index`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

#!/bin/sh
# Connection parameters for MySQL
sql_host="127.0.0.1"
slq_usuario="root"
sql_password="password"
sql_database="newproject_db"

# Config (Max gold amount)
MaxGold="21312321311"

# Connection parameters for mysql command
sql_args="-h $sql_host -u $slq_usuario -p$sql_password -D $sql_database -s -e"

# Insert into t_gold_results from t_characters
mysql $sql_args "INSERT INTO t_gold_results (a_user_index, a_nick, a_nas, a_gold_type) \
SELECT a_user_index, a_nick, a_nas, 'Character' AS a_gold_type \
FROM t_characters \
WHERE a_nas > $MaxGold;"

# Insert into t_gold_results from t_stash_money
mysql $sql_args "INSERT INTO t_gold_results (a_user_index, a_nick, a_nas, a_gold_type) \
SELECT t_stash_money.a_user_index, t_characters.a_nick, t_stash_money.a_stash_money, 'Stash' AS a_gold_type \
FROM t_stash_money \
JOIN t_characters ON t_stash_money.a_user_index = t_characters.a_user_index \
WHERE t_stash_money.a_stash_money > $MaxGold;"

# Update gold amount in t_stash_money and t_characters for records > $MaxGold
mysql $sql_args "UPDATE t_stash_money SET a_stash_money = $MaxGold WHERE a_stash_money > $MaxGold;"
mysql $sql_args "UPDATE t_characters SET a_nas = $MaxGold WHERE a_nas > $MaxGold;"


The part where you update the gold when found is not necessary up to the user if you just want to log it then you dont need to run the bottom 2 lines.



Here is example how it looks

2




Nice! I wanna point out just a simple consideration:

Remember that you can directly create a table with a select, wich means you don't have to perform 2 different mysql commands:

CREATE TABLE IF NOT EXISTS `t_gold_results` AS (
SELECT x, y
FROM k
LEFT JOIN ...);

In this case you can call it as (where MaxGold is an sql variable or bash var):

CREATE TABLE IF NOT EXISTS `t_gold_results` AS (
SELECT

a_user_index,
a_nick,
a_nas,
'Character' AS a_gold_type

FROM t_characters


WHERE a_nas > $MaxGold


UNION


SELECT

t_stash_money.a_user_index,
t_characters.a_nick,
t_stash_money.a_stash_money,
'Stash' AS a_gold_type

FROM t_stash_money

INNER JOIN t_characters
ON t_characters.a_user_index = t_stash_money.a_user_index

WHERE t_stash_money.a_stash_money > $MaxGold;
)