PHP [phpMyAdmin] Commands to edit database.

Discussion in 'Programming & Scripting' started by Jester, Jun 23, 2008.

  1. Jester

    Jester Off

    Joined:
    May 28, 2007
    Messages:
    2,660
    Likes Received:
    23
    Best Answers:
    0
    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:
    1. UPDATE account SET premdays=0;
    • Give 7 days more of premium to everyone. (By Jonern)

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

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

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

    PHP:
    1. 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:
    1. UPDATE `players` SET `redskulltime`=0;
    • Give the 5 blessings to everyone. (By Jester)

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

    PHP:
    1. 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:
    1. 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:
    1. DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);
    • Delete all items from player id.
    • Delete all deaths produced before the time you put, in this case 1 july.
    PHP:
    1. DELETE FROM player_deaths WHERE time<1246468428;

    Rep++ if helped :)
     
    Last edited: Jul 21, 2009
    demon088, Codex NG, Kuzyn and 5 others like this.
  2. habiba

    habiba New Member

    Joined:
    Aug 26, 2007
    Messages:
    586
    Likes Received:
    3
    Best Answers:
    0
    THanks
    Jester is that passible to delete all quests all player have done in the database?
     
  3. HeberPcL

    HeberPcL [PowerOT.com.br] Premium User

    Joined:
    Aug 21, 2007
    Messages:
    1,274
    Likes Received:
    38
    Best Answers:
    0
    roxx,
    thx !!
    =p
     
  4. Jester

    Jester Off

    Joined:
    May 28, 2007
    Messages:
    2,660
    Likes Received:
    23
    Best Answers:
    0
    Yes.

    [​IMG]
     
  5. clone

    clone New Member

    Joined:
    Jun 16, 2007
    Messages:
    413
    Likes Received:
    1
    Best Answers:
    0
    nice is there a way to deleted a certain item from everyones players and possibly even houses?
     
  6. @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]
     
  7. Jonern

    Jonern Retired

    Joined:
    May 31, 2007
    Messages:
    1,054
    Likes Received:
    8
    Best Answers:
    0
    And in just one query:
    PHP:
    1.  
    2. UPDATE players SET posx=793, posy=1014, posz=7;
    3.  
    Code (Text):
    1. DELETE FROM player_depotitems WHERE itemtype = 2160;
    2. DELETE FROM player_items WHERE itemtype = 2160;
    3. 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

    Delete all level 8 players:
    Code (Text):
    1. 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 (Text):
    1. 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: Jul 27, 2008
  8. Znote

    Znote <?php echo $title; ?> Staff Member Global Moderator Premium User

    Joined:
    Feb 14, 2008
    Messages:
    6,009
    Likes Received:
    838
    Best Answers:
    77
    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.
     
  9. Jonern

    Jonern Retired

    Joined:
    May 31, 2007
    Messages:
    1,054
    Likes Received:
    8
    Best Answers:
    0
    Magic:
    PHP:
    1. 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: Oct 11, 2008
  10. Oceanic

    Oceanic Php / C++ / MySQL

    Joined:
    May 7, 2008
    Messages:
    241
    Likes Received:
    1
    Best Answers:
    0
    Really nice :D
     
  11. Nikkster

    Nikkster Programmer

    Joined:
    May 9, 2008
    Messages:
    2,848
    Likes Received:
    7
    Best Answers:
    0
    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?
     
  12. koitsalu

    koitsalu Banned User

    Joined:
    Sep 27, 2007
    Messages:
    563
    Likes Received:
    1
    Best Answers:
    0
    This could be really helpful, but whre do I enter those commands?
     
  13. Jonern

    Jonern Retired

    Joined:
    May 31, 2007
    Messages:
    1,054
    Likes Received:
    8
    Best Answers:
    0
    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.
     
  14. Kask

    Kask Banned User

    Joined:
    Jan 3, 2008
    Messages:
    377
    Likes Received:
    1
    Best Answers:
    0
    and for give blessings free? to one person
     
  15. Chris

    Chris Inactive

    Joined:
    Aug 11, 2008
    Messages:
    2,628
    Likes Received:
    235
    Best Answers:
    2
    @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: Sep 18, 2008
  16. Denis Tacador De Runa

    Denis Tacador De Runa New Member

    Joined:
    Sep 6, 2007
    Messages:
    362
    Likes Received:
    3
    Best Answers:
    0
    Is it possible to use the commands in a talkaction script?
     
  17. Kiwi Dan

    Kiwi Dan Kiwi Server 3.0

    Joined:
    Jun 5, 2007
    Messages:
    618
    Likes Received:
    9
    Best Answers:
    0
    Helpful! Thanks to all who were involved.
     
  18. hesczu

    hesczu Member

    Joined:
    Jun 14, 2007
    Messages:
    148
    Likes Received:
    0
    Best Answers:
    0
    Sure, use LuaSQL
     
  19. GM Sp00n

    GM Sp00n New Member

    Joined:
    Sep 1, 2008
    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Will this also delete all related records? In player item, depot item, etc?
     

Share This Page

Loading...