PHP [phpMyAdmin] Commands to edit database.

[Jonern]

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

You're right. But usually the WHERE column in the where clause is different from table to table so it isn't often you can do it that way.

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

Triggers has been made to take care of that.

 

[clone]

where are these triggers omg its driving me crazy! ive searched everywhere...

 

[christiandb]

really nice

 

[GM Sp00n]

Is there also a command to delete items/depot items etc. WHERE player ID doesnt excist in player table ?

Cuz I just did the delete from players where level <50 and timstamp etc. and it only removed the players, not the rest. (did it in mysql.exe due to timeout otherwise)

 

[Sharp]

thnx needed this

 

[Nikkster]

It helped me very well. =)

 

[Jonern]

Is there also a command to delete items/depot items etc. WHERE player ID doesnt excist in player table ?

Cuz I just did the delete from players where level <50 and timstamp etc. and it only removed the players, not the rest. (did it in mysql.exe due to timeout otherwise)

Triggers should have fixed that when you deleted it. So guess your triggers were wrong or something.

When it comes to your problem, I've tried to figure it out (I've read the manual ), but before you run this query, I want you to make a BACKUP of your database.

DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);

This code is UNTESTED and I don't know if it will do what I want.

Please, do this on the backup you made of your database and see if it works, and give me the results.

 

[d0ri0]

Great tips! Wonder if its possible to delete all items from players backpack that have action id 200? Thanks for help.

 

[GM Sp00n]

mysql> DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;
Query OK, 7499 rows affected (2 min 14.56 sec)

mysql> DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);
Query OK, 0 rows affected (1 min 9.55 sec)

Does this mean it deleted all player_items already ? (I dont think so )

 

[Luke sky walker]

DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;

hmmm and table skills?, storages, etc?

 

[Jonern]

mysql> DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;
Query OK, 7499 rows affected (2 min 14.56 sec)

mysql> DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);
Query OK, 0 rows affected (1 min 9.55 sec)

Does this mean it deleted all player_items already ? (I dont think so )

If your triggers are right, it should have been deleted.

But it's clearly that the last query I made didn't do anything, so I'll have to figure that out later. My laptop has just arrived in the mail, so I'll be able to experiment by my self now, and give you working queries.

You can try this btw, to see if you have to deleted them or not:
SELECT COUNT(*) FROM players; ---> Note the number down
SELECT COUNT(*) FROM player_skills; ---> Note the number and divide it by the number of different skills (10?). Compare the numbers. If one is bigger than the other, you triggers are not working.

DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;

hmmm and table skills?, storages, etc?

Again, triggers will fix that.

 

[Zerville]

Oh... this pwns thx guys!

 

[Jonern]

Triggers should have fixed that when you deleted it. So guess your triggers were wrong or something.

When it comes to your problem, I've tried to figure it out (I've read the manual ), but before you run this query, I want you to make a BACKUP of your database.

DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);

This code is UNTESTED and I don't know if it will do what I want.

Please, do this on the backup you made of your database and see if it works, and give me the results.

I've tested that "NOT IN" means exactly what it says. I haven't tested it on exactly that case, but I've tested it on a similar case and it works they way I predicted. You can always test the code to see what you will delete by replacing "DELETE" with "SELECT *".

mysql> DELETE FROM players WHERE level < 50 AND lastlogin < UNIX_TIMESTAMP() - 20*24*60*60;
Query OK, 7499 rows affected (2 min 14.56 sec)

mysql> DELETE FROM player_items WHERE player_id NOT IN (SELECT id FROM players);
Query OK, 0 rows affected (1 min 9.55 sec)

Does this mean it deleted all player_items already ? (I dont think so )

Yes, it does mean that.

 

[GM Sp00n]

Ok, thnx for the help!

 

[Jonern]

First post updated with stuff posted later in this thread.

 

[Jgarder]

alright im getting angry.. i just cant get this perfect.. i want to do

UPDATE `players` SET `redskulltime`=0 WHERE name = "Jgarder";


but its not working at all.. i know its some small Syntax error. but i cant fix it.

 

[Chris]

UPDATE `players` SET `redskulltime`= '0' WHERE name = 'Jgarder'

That should definately do. : )

 

[Gregor]

If the players have premium, but i want add to everyone 3 days free premium(becuase server was offline) what command should i use?
example. if player have now 7 days premium he will have 10 days premium etc

 

[Chris]

I'm not sure if there's a field called "premdays" but I'm guessing there is.

<?PHP
mysql_connect ( "localhost" , "root" , "password" );
mysql_select_db ( "theforgottenserver" );

$newPrem = 3;

$result = mysql_query ( "SELECT `premdays` FROM `accounts`" );
while ( $row = mysql_fetch_array ( $result ) )
{
     if ( $row['premdays'] > 0 )
     {

          $newPrem = $row["premdays"] + $newPrem';

          $query = "UPDATE `accounts` SET `premdays` = '$newPrem'";
          mysql_query ( $query );
     }
}
?>

Save it as like newprem.php and then run it :] Remember to change the database connection data. This would give every player who already has premium 3 days extra premium.

 

[Jonern]

If the players have premium, but i want add to everyone 3 days free premium(becuase server was offline) what command should i use?
example. if player have now 7 days premium he will have 10 days premium etc

Try this, it's much simpler

UPDATE accounts SET premdays = premdays + 3 WHERE premdays > 0;