• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

PHP [phpMyAdmin] Commands to edit database.

Jester

Off
Senator
Joined
May 28, 2007
Messages
2,683
Reaction score
56
Location
Romania.
In this tutorial I will show you some commands to edit the database. (Players, accounts.. etc)
This is only for TheForgottenServer.
Phpmyadmin commands. Login on the database and press on sql tab (not needed to select any table)


  • Remove premium to every account from the database. (By Jester)

PHP:
UPDATE account SET premdays=0;
  • Give 7 days more of premium to everyone. (By Jonern)

PHP:
UPDATE accounts SET premdays = premdays + 7
  • Set all players one coordenate. (By Jester, Jonern)

PHP:
UPDATE players SET posx = 793, posy = 1014, posz = 7;
  • Set all group ids to 1. (By Jester)

PHP:
UPDATE groups SET flags=0,name="Player" WHERE id=1;
UPDATE players SET group_id=1;
DELETE FROM groups WHERE id<>1;
  • Create Account Manager. (From forgottenserver.sql)

PHP:
INSERT INTO `players` VALUES (1, 'Account Manager', 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 10, 10, 10, 0, 0);
  • Remove all red skulls and kills. (By Jester)

PHP:
UPDATE `players` SET `redskulltime`=0;
  • Give the 5 blessings to everyone. (By Jester)

PHP:
UPDATE `players` SET `blessings`=31;
  • Remove a specific item (here id 2160) completely from the game (depot, players, houses) (By Jonern, Macroman)

PHP:
DELETE FROM player_depotitems, player_items, tile_items WHERE itemtype = 2160;
  • Delete inactive players, here below lvl 50, 20 days of inactivity (By Jonern)

PHP:
DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60
  • Delete player items where the player has been deleted, but his items has not. It's just to show the way it works. Means that it will delete all rows that doesn't belong to any player. Change for your own need. (By Jonern)

PHP:
DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);
  • Delete all items from player id.
DELETE FROM `player_depotitems` , `player_items` WHERE `player_id` = '1234'

  • Delete all deaths produced before the time you put, in this case 1 july.
PHP:
DELETE FROM player_deaths WHERE time<1246468428;


Rep++ if helped :)
 
Last edited:
THanks
Jester is that passible to delete all quests all player have done in the database?
Yes.

howto.jpg
 
nice is there a way to deleted a certain item from everyones players and possibly even houses?
 
@Jester congratulations for this, many intersting, how to me make this for delet all players with X level?

See i want to delet all level 8 players... commando to add skill?? ML??? level and exp???

No more, thx...

F@bio
 
  • Set all players one coordenate. (By Jester)

PHP:
UPDATE players SET posx=793;
UPDATE players SET posy=1014;
UPDATE players SET posz=7;
And in just one query:
PHP:
UPDATE players SET posx=793, posy=1014, posz=7;

nice is there a way to deleted a certain item from everyones players and possibly even houses?
Code:
DELETE FROM player_depotitems WHERE itemtype = 2160;
DELETE FROM player_items WHERE itemtype = 2160;
DELETE FROM tile_items WHERE itemtype = 2160;
This will remove all crystal coins (itemid 2160) from the game.
You'll need to run all the queries, since I don't know how to make it just one :p

@Jester congratulations for this, many intersting, how to me make this for delet all players with X level?

See i want to delet all level 8 players... commando to add skill?? ML??? level and exp???

No more, thx...

F@bio
Delete all level 8 players:
Code:
DELETE FROM players WHERE level = 8;
Adding skills I would prefer to do ingame, unless you're giving everyone 100 in shielding or something.
Code:
UPDATE players SET level = level + 50 WHERE name = 'Fabio' LIMIT 1;
This would add 50 levels to the character Fabio. For ML and exp, just change level to maglevel or experience.
 
Last edited:
  • Create Account Manager. (By..Unknown)

PHP:
INSERT INTO `players` VALUES (1, 'Account Manager', 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 10, 10, 10, 0, 0);

If you read it through the "handy sql commands" document I gave you, feel free to give me credits for it <3

(even tho i copyed it from theforgottenserver.sql) xD

I used AFS-CMS admin tool and deleted all players at lower lvl than 30, and the account manager also went away, so needed a way to readd only account manager.

Btw, for AFS-CMS users of the newest version, you got the option to delete etc:

Players lvl 50-
who are inactive for 20 days

very handy :p So you only delete inactive chars.
 
Magic:
PHP:
DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;
Will do exactly what you said :p
(Delete all players under level 50 who has been inactive for 20 days.)

Don't forget to give rep ;)
 
Last edited:
Hey, when I'm creating my GM through the database, the character gets a gm outfit, and the highest access ofcourse but, I wanted to change that like to this: Group id: 3 (GM Outfit) Group id 4 (GM outfit) and the god access Group id: 5 (CM Outfit (266)


Everytime I try to change the looktype, it doesnt work. It still wear the GM outfit, anyone knows how to do?
 
Depends on how you manage your database, but you should be able to find a place to write your own queries in any of the SQL managers out there.

In phpMyAdmin there is a tab named SQL where you can input text and that's where you put the queries.

NOTE: SQL-queries can't be undone with an "undo"-button, this applies especially to DELETE queries.
 
@Jonern
DELETE FROM player_depotitems WHERE itemtype = 2160;
DELETE FROM player_items WHERE itemtype = 2160;
DELETE FROM tile_items WHERE itemtype = 2160;

How about doing something similar to this:
DELETE FROM player_depotitems, player_items, tile_items WHERE itemtype = 2160;

Can't see why it shouldn't work as it works in the SELECT query to select items from several tables. :p
 
Last edited:
Magic:
PHP:
DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;
Will do exactly what you said :p
(Delete all players under level 50 who has been inactive for 20 days.)

Will this also delete all related records? In player item, depot item, etc?
 
Back
Top