Linux Phpmyadmin Command

[Vrotz]

Hi,

I need to change/reset the skill of specific vocations.

All knights back to skill 10 of Sword, Axe and Shield.
All paladins back to skill 10 of Dist and Shield.

(No command to change of all vocations).

Thanks.

 

[Ignazio]

What server do you use? Could you post a screenshot of the structure view of your players table?

Ignazio

 

[Vrotz]

sup!

 

[Ignazio]

First of all, export a backup of the table, if any of the commands you enter should end in faulty, you can revert back.

Second of all, could you post a screenshot of your player_skills table?

Ignazio

 

[Vrotz]

First of all, export a backup of the table, if any of the commands you enter should end in faulty, you can revert back.

Second of all, could you post a screenshot of your player_skills table?

Ignazio

Sure.

 

[Ignazio]

Try this query:

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 10 and `ps`.`tries` = 0
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))
OR ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Ignazio

 

[Vrotz]

Try this query:

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 10 and `ps`.`tries` = 0
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))
OR ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Ignazio

Thanks.

But I need change Knights to Sword/Axe & Shield to 80 and Paladins Dist 70 y Shield 70. Its possible?!

 

[Ignazio]

I divided them into two different queries

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 80 and `ps`.`tries` = 0
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 70 and `ps`.`tries` = 0
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Hope it works!

Ignazio

 

[Vrotz]

I divided them into two different queries

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 80 and `ps`.`tries` = 0
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 70 and `ps`.`tries` = 0
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Hope it works!

Ignazio

I get this at the two commands:
#1054 - Unknown column 'ps.tries' in 'field list'

 

[Ignazio]

Ops! Mistook count for tries, sorry, here are two new, hopefully working queries:

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 80 and `ps`.`count` = 0
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 70 and `ps`.`count` = 0
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Ignazio

 

[Vrotz]

Ops! Mistook count for tries, sorry, here are two new, hopefully working queries:

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 80 and `ps`.`count` = 0
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 70 and `ps`.`count` = 0
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Ignazio

Bro i dont know why but when i execute this command and login in an char the skill are zero!

 

[Ignazio]

All the skills are zero? How does it look in the player_skills table?

Maybe the count doesn't reset after an increase of skill, sounds weird though.

Ignazio

 

[Vrotz]

All the skills are zero? How does it look in the player_skills table?

Maybe the count doesn't reset after an increase of skill, sounds weird though.

Ignazio

Just skills that we are trying to change! Axe/Sword/Dist/Shield

 

[Ignazio]

Sounds weird, take a look at the entries in the "player_skills" table and find some row with more than 50 in `value`, what is the `count` there?

Try this too:

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 80 and `ps`.`count` = 50000
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 70 and `ps`.`count` = 50000
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Ignazio

 

[Vrotz]

Sounds weird, take a look at the entries in the "player_skills" table and find some row with more than 50 in `value`, what is the `count` there?

Try this too:

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 80 and `ps`.`count` = 50000
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = 70 and `ps`.`count` = 50000
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

Ignazio

Continue in zero.

I tried something about that commands;


UPDATE player_skills SET value=10, count=0 WHERE skillid=0;
UPDATE player_skills SET value=10, count=0 WHERE skillid=1;
UPDATE player_skills SET value=70, count=0 WHERE skillid=2;
UPDATE player_skills SET value=70, count=0 WHERE skillid=3;
UPDATE player_skills SET value=70, count=0 WHERE skillid=4;
UPDATE player_skills SET value=20, count=0 WHERE skillid=5;
UPDATE player_skills SET value=10, count=0 WHERE skillid=6;

But I want redirect the skill ID for vocation, u know/understand what I mean bro?

 

[Ignazio]

The query is perfectly fine, the problem is your outdated server, probably handling count as a constant value rather than resetting for each level.

Could you go to player_skills and "browse" and show me how it looks

Ignazio

 

[Vrotz]

The query is perfectly fine, the problem is your outdated server, probably handling count as a constant value rather than resetting for each level.

Could you go to player_skills and "browse" and show me how it looks

Ignazio

Something related yet?

 

[Ignazio]

What? Lawl something of yours is way outdated, why would it set 0 when I specifically told it to get 10? Maybe it complains about the spaces, hmm.

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = "80"
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = "70"
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

That might work, not sure what SQL / Phpmyadmin, php or forgotten server version you are using, makes things unpredictable

Ignazio

 

[Vrotz]

What? Lawl something of yours is way outdated, why would it set 0 when I specifically told it to get 10? Maybe it complains about the spaces, hmm.

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = "80"
WHERE ((`pl`.`vocation` = 4 or `pl`.`vocation` = 8) and (`ps`.`skillid` = 2 or `ps`.`skillid` = 3 or `ps`.`skillid` = 5))

UPDATE `player_skills` as `ps`
LEFT JOIN `players` AS `pl` ON `ps`.`player_id` = `pl`.`id`
SET `ps`.`value` = "70"
WHERE ((`pl`.`vocation` = 3 or `pl`.`vocation` = 7) and (`ps`.`skillid` = 4 or `ps`.`skillid` = 5))

That might work, not sure what SQL / Phpmyadmin, php or forgotten server version you are using, makes things unpredictable

Ignazio

Yes was the spaces.
Thanks a lot, Ignazio.

 

[Ignazio]

Always learn something new, quotes for older phpmyadmins, I see.

I'm glad it worked, yours

Ignazio