SQL HELP!

[MxSoft]

Hello everyone, i was just wondering if there is a way to give all player an item in their depots?
Like something like this but for everyone?

INSERT INTO `player_depotitems`(`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`) VALUES (58059,102,101,itemid,2,0)

Ty people in advance

 

[slaw]

You can do it either with php or lua.

Here's php version:

$query = $db->query('SELECT `id` FROM `players`');
foreach($query->fetchAll() as $player) {
    $db->query('INSERT INTO `player_depotitems`(`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`) VALUES (' . $player['id'] . ',102,101,itemid,2,0)');
}

I'm not sure, however, how those sid and pid fields works, so you might need to modify it.

 

[MxSoft]

Well it worked for the character samples only

 

[Znote]

Select all players and insert in 1 query @slawkens approach can potentially do 10k separate insert queries (for each player) and timeout scripts.

Select all players and insert in 1 query:

INSERT INTO `player_depotitems`(`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`)
SELECT DISTINCT `id` as `player_id`, '102' as `sid`, '101' as `pid`, '2160' as `itemtype`, '2' as `count`, '0' as `attributes` from `players`

There is a problem with the code however.
The primary key is joint player_id and sid. This means that the static "102" sid cannot be static. Sid over 100 is the slot id in depot, and needs to be max + 1 of that player_id group

 

[MxSoft]

Select all players and insert in 1 query @slawkens approach can potentially do 10k separate insert queries (for each player) and timeout scripts.

Select all players and insert in 1 query:

INSERT INTO `player_depotitems`(`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`)
SELECT DISTINCT `id` as `player_id`, '102' as `sid`, '101' as `pid`, '2160' as `itemtype`, '2' as `count`, '0' as `attributes` from `players`

There is a problem with the code however.
The primary key is joint player_id and sid. This means that the static "102" sid cannot be static. Sid over 100 is the slot id in depot, and needs to be max + 1 of that player_id group

What i want to create is the box inside the depot, itemid 2594, i got this error: #1062 - Duplicate entry '58060-102' for key 'player_id_2'

 

[Znote]

What i want to create is the box inside the depot, itemid 2594, i got this error: #1062 - Duplicate entry '58060-102' for key 'player_id_2'

This works, but probably not the most optimized version:

INSERT INTO `player_depotitems` (`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`)
SELECT DISTINCT `p`.`id` as `player_id`,
(select MAX(`pd`.`sid`) as `sid` from `player_depotitems` as `pd` where `pd`.`player_id`=`p`.`id` LIMIT 1)+1 as `sid`,
'101' as `pid`,
'2160' as `itemtype`,
'2' as `count`,
'0' as `attributes`
from `players` as `p`

For players missing all depotitems it seems to add sid 0, should be 100+. Perhaps try MAX(MAX(`pd`.`sid`), '101') to get the highest value of sid with the lower value being 101?

 

[MxSoft]

This works, but probably not the most optimized version:

INSERT INTO `player_depotitems` (`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`)
SELECT DISTINCT `p`.`id` as `player_id`,
(select MAX(`pd`.`sid`) as `sid` from `player_depotitems` as `pd` where `pd`.`player_id`=`p`.`id` LIMIT 1)+1 as `sid`,
'101' as `pid`,
'2160' as `itemtype`,
'2' as `count`,
'0' as `attributes`
from `players` as `p`

For players missing all depotitems it seems to add sid 0, should be 100+. Perhaps try MAX(MAX(`pd`.`sid`), '101') to get the highest value of sid with the lower value being 101?

It inserted the items, but with sid= 3 i cant see the box inside depot

 

[Znote]

It inserted the items, but with sid= 3 i cant see the box inside depot

Lets try the GREATEST operation. (pun intended?)

INSERT INTO `player_depotitems` (`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`)
SELECT DISTINCT `p`.`id` AS `player_id`,
(SELECT GREATEST(MAX(`pd`.`sid`), '101') AS `sid` FROM `player_depotitems` AS `pd` WHERE `pd`.`player_id`=`p`.`id` LIMIT 1)+1 AS `sid`,
'101' AS `pid`,
'2160' AS `itemtype`,
'2' AS `count`,
'0' AS `attributes`
FROM `players` AS `p`

 

[MxSoft]

It created depot chest from cid 3 to cid 1899, there are some with 102 xd

 

[Znote]

Ok, I think the max operator don't work with a limit 1 query, screw max and just fetch the highest sid for each player_id...

INSERT INTO `player_depotitems` (`player_id`, `sid`, `pid`, `itemtype`, `count`, `attributes`)
SELECT DISTINCT `p`.`id` AS `player_id`,
(SELECT GREATEST(`pd`.`sid`, '101') AS `sid` FROM `player_depotitems` AS `pd` WHERE `pd`.`player_id`=`p`.`id` ORDER BY `pd`.`sid` DESC LIMIT 1)+1 AS `sid`,
'101' AS `pid`,
'2152' AS `itemtype`,
'1' AS `count`,
'0' AS `attributes`
FROM `players` AS `p`

Try another itemid so you don't mix these results with earlier results.

 

[MxSoft]

Other items works great, also i think i just can create a depot chest with sid 102 if player doesnt have any container inside their depots because i fixed 15120 players depot chests from 1610!
ty so much for your help