• 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.

Help plz:
i am using this query:
DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players)

But nothing is deletd:
0 row(s) deleted.

i had deleted a lot of players.
if this heapends because some trigger ploblem, is there some way to fix it ?

Perhaps, you don't have deleted characters. :0
 
will this command
Lua:
UPDATE `players` SET `level`=?, `health`=?, `healthmax`=?, `mana`=?, `manamax`=?, `cap`=?
delete storage id "11551" ??
 
Ehh, nope.

PHP:
DELETE FROM `player_storage` WHERE `key` = '11551';

It will delete it from player_storage table.

If you want to remove a global_storage use:
PHP:
DELETE FROM `global_storage` WHERE `key` = '11551';
 
Code:
DELETE FROM players WHERE deleted = 1 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60

Add this to the list?

Delete players whit 20 days of inactivity and with deleted status
 
I heave a question.
I will try to explain.

Ex:

Player_storage table.
Code:
player_id     key    value
    1          10      10
    2          10      100
    3          10      20
    4          10      60
    5          10      30
    6          10      80
    7          10      70
    8          10      40
    9          10      90 
   10          10      40

There is some query to select the 3 players who heave the highers values ?
 
Is there anything I can do to delete a item that a player got in his backpack/depot/house.

Like remove all "magic swords" from all players."
 
NICEST QUERYS EVER EXISTED:

- Give 1 premium day to all players from guild_id 3.
PHP:
update accounts set premdays = premdays + 1 where accounts.id in (select players.account_id from players inner join guild_ranks on guild_ranks.id = players.rank_id where guild_ranks.guild_id = 3)

- HP or MP bug? -- Set the right HP formula to all knights... Modify as you want it.
PHP:
UPDATE players SET healthmax = ((players.level - 8) * 15 + 185) WHERE vocation = 4

- Reward players that bought a knight axe with 600 premium points.
PHP:
UPDATE accounts SET premium_points = premium_points + 600 WHERE id IN (SELECT from_account FROM z_shop_history_item WHERE offer_id = 'knight axe')

- Reset by db * it doesnt change skills and remove items
PHP:
UPDATE players SET promotion =0, level =1, vocation =0, health =150, healthmax =150, experience =0, maglevel =0, mana =0, manamax =0, manaspent =0, town_id =18, posx =2110, posy =1069, posz =7, cap =420, loss_experience =100, loss_mana =100, loss_skills =100, loss_containers =100, loss_items =100

- Change player outfits.
PHP:
UPDATE players SET lookbody =69, lookfeet =95, lookhead =115, looktype =128
 
How can I reset the skills of only the characters who have vocation id x?
 
DELETE FROM `player_depotitems` , `player_items` , `tile_items` WHERE itemtype = 2165
DELETE FROM player_depotitems, player_items, tile_items WHERE itemtype = 2165;
they both give me the same error
#1046 - No database selected
DELETE FROM `player_depotitems` ,
`player_items` ,
`tile_items` WHERE itemtype =2165
whats wrong?
 
Last edited:
@ Znote
PHP:
UPDATE `player_skills` SET `value` = '0' WHERE `skillid` IN (0, 1, 2, 3, 4, 5, 6, 7) AND `player_id` IN (SELECT `id` FROM `players`
WHERE `vocation` = 0)
@
soul4soul
PHP:
DELETE FROM `DATABASENAME`.`player_depotitems` , `DATABASENAME`.`player_items` , `DATABASENAME`.`tile_items` WHERE "itemtype" = 2165
change databasename to yours
 
Last edited:
@up
when i tried yours i got an error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE "itemtype" = 2165' at line 1
DELETE FROM `mydbname`.`player_depotitems` ,
`mydbname`.`player_items` ,
`mydbname`.`tile_items` WHERE "itemtype" =2165

i took yours and messed around and after like 4 tries i got this to work.
DELETE FROM `player_depotitems` WHERE `itemtype` = 2165
and i had to change the table name each time.

thanks =]
 
@up
when i tried yours i got an error


i took yours and messed around and after like 4 tries i got this to work.

and i had to change the table name each time.

thanks =]

you can make this too
PHP:
DELETE FROM `player_depotitems` WHERE `itemtype` = 2165;
DELETE FROM `player_items` WHERE `itemtype` = 2165;
DELETE FROM `tile_items` WHERE `itemtype` = 2165;
 
PHP:
UPDATE `player_depotitems`, `player_items`, `tile_items` SET "itemtype" = 1988 WHERE "itemtype" = 11241;


change one item for another. ;p
 
Last edited:
Hi, how to give x "item" to all players?

I want give 1cc of reward to all players.
 
Back
Top