[GospodinX]

Hi guys,
I have few question about Inventory Saving.I want to make that player can save some items in bag.

I will create table 'inventory'

Quote:

userid, itemid, amount

Now i don't know how:
1) How to delete row if amount is 0(Or I need to check every time is amount empty and then delete?) Or there is way that all rows where amount = 0 need to be deleted?

2) How to "save" new item in inventroy.For example :

-If player already have food,I need just update row(where itemid = food)
-But if player don't have food,I need to insert new row.

So how to do it?Or i need every time check it?

3) What is best way to load it on server?
If i have 10 items,do this:
new item[10][MAX_PLAYERS] and then load or there is better way?(because players dont will have every time all items,so..There will be many times empty variables,i don't know is there better way.

Thanks
(sorry for my poorly english)

[GRiMMREAPER]

1. DELETE FROM `inventory` WHERE `amount` = 0;

2. Check if the player has food by running a SELECT * FROM `inventory` WHERE `playername` = %s query, where %s is the player's name; store the result with mysql_store_result();; create a variable called result[MAX_AMOUNT_OF_ITEMS] (make sure to replace MAX_AMOUNT_OF_ITEMS) and then:

pawn Код:

while(mysql_retrieve_row())
    {
        mysql_fetch_field_row(result, "amount");  
        // Run this query: UPDATE `inventory` SET `itemid` = `food`, `amount` = %i; , where %i is the new value for the amount of X item.
    }

3. Just load the ones that aren't empty?

[GospodinX]

1.

Код:

DELETE FROM `inventory` WHERE `amount` = 0;

I know for this.I'm think that i can do it in one query(To update,and if it's 0 ,delete it)
So i need to do this on this way:

Код:

if(amount > 0)
{
UPDATE...
}
else
{
DELETE..
}

2.But what if player don't have row for it item...

[GRiMMREAPER]

Quote:

Originally Posted by GospodinX 2.But what if player don't have row for it item...

I'm not at home at the moment, so I can't check if this works, but have a try:

pawn Код:

SELECT EXISTS(SELECT 1 FROM `inventory` WHERE `amount` >= 0);

[Calisthenics]

2) https://dev.mysql.com/doc/refman/8.0...duplicate.html

Does inventory table have a primary key? If not, set these two (userid, itemid) as PRIMARY KEY. Each player and item is a unique pair and use the syntax I linked above. It either inserts a new row or updates the row if exists.

[GospodinX]

Quote:

Originally Posted by Calisthenics 2) https://dev.mysql.com/doc/refman/8.0...duplicate.html Does inventory table have a primary key? If not, set these two (userid, itemid) as PRIMARY KEY. Each player and item is a unique pair and use the syntax I linked above. It either inserts a new row or updates the row if exists.

It's great.Is there anything for example that amount can't be 0?(if it's 0 delete row).I just need it now.

[Calisthenics]

Quote:

Originally Posted by GospodinX It's great.Is there anything for example that amount can't be 0?(if it's 0 delete row).I just need it now.

The only thing similar to this is triggers: https://dev.mysql.com/doc/refman/8.0...er-syntax.html
I never used them with INSERT .. ON DUPLICATE so I am not sure. An example:

pawn Код:

CREATE TRIGGER remove_item BEFORE INSERT ON inventory
  FOR EACH ROW
    DELETE FROM inventory WHERE amount = 0;

However, I find this method way not appropriate. You do know the amount of item before executing query so you can delete when it is 0 and insert/update when it is not.