PHP [phpMyAdmin] Commands to edit database.

Jester

Off
Joined
May 28, 2007
Messages
2,659
Best answers
0
Reaction score
28
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:

habiba

New Member
Joined
Aug 26, 2007
Messages
586
Best answers
0
Reaction score
3
Location
Örebro, Sweden
THanks
Jester is that passible to delete all quests all player have done in the database?
 

clone

New Member
Joined
Jun 16, 2007
Messages
412
Best answers
0
Reaction score
1
nice is there a way to deleted a certain item from everyones players and possibly even houses?
 

[email protected]

Fudera Otserver
Joined
Jul 10, 2007
Messages
206
Best answers
0
Reaction score
0
Location
Brasil
@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...

[email protected]
 

Jonern

Retired
Joined
May 31, 2007
Messages
1,054
Best answers
0
Reaction score
8
Location
Norway
  • 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...

[email protected]
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:

Znote

<?php echo $title; ?>
Staff member
Global Moderator
Premium User
Joined
Feb 14, 2008
Messages
6,609
Best answers
187
Reaction score
1,332
Location
Norway, Oslo
  • 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.
 

Jonern

Retired
Joined
May 31, 2007
Messages
1,054
Best answers
0
Reaction score
8
Location
Norway
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:

Nikkster

Programmer
Joined
May 9, 2008
Messages
2,848
Best answers
0
Reaction score
7
Location
Confidential
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?
 

koitsalu

Banned User
Joined
Sep 27, 2007
Messages
562
Best answers
0
Reaction score
1
This could be really helpful, but whre do I enter those commands?
 

Jonern

Retired
Joined
May 31, 2007
Messages
1,054
Best answers
0
Reaction score
8
Location
Norway
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.
 

Kask

Banned User
Joined
Jan 3, 2008
Messages
376
Best answers
0
Reaction score
1
and for give blessings free? to one person
 

Chris

Inactive
Joined
Aug 11, 2008
Messages
2,628
Best answers
2
Reaction score
228
@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:

GM Sp00n

New Member
Joined
Sep 1, 2008
Messages
15
Best answers
0
Reaction score
0
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?
 
Top