[dusk]

Hello,

I'm writting an inventory include and I want to give an optional MySQL saving.

Items are loaded when a player connects and saved when he disconnects. Loading is simple: a select query. Now with the saving, I'm not sure which one to send.

I used to send an update and then if there were no affected rows send an insert one. But I noticed if I try to UPDATE one column and its old value is the same as the one I'm setting it to it return affected rows - 0. And if that's the case, I send an insert query. So when this happens I have two rows for the same thing.

Ofcourse I could send a SELECT query, then check if it exists and update or insert a new one, but that sounds stupid and unefficient.

I considered using a primary key, but both the name and item name can already exist in the table. I do have an auto increment column, but I do now keep track of it in Pawn.

I also read a post about never using delete queries(by ****** I believe). I guess I could add another column for a timestamp and when selecting just select 1 and order by that column. But is it a good idea for an include to do such a thing?

All thoughts will be appreciated

[Vince]

You should set a composite UNIQUE key, e.g. userid + itemid. That's was that function was designed for. Then you can use the construct:

PHP код:

INSERT ... ON DUPLICATE KEY UPDATE ... 


Let's say that there is a row with userid = 1 and itemid = 1
Inserting the following will work:
userid 1 and itemid 2
userid 2 and itemid 1
But if you try to insert userid 1 and itemid 1 again, it will fail and use the update clause instead, e.g.

PHP код:

INSERT INTO inventory (userid, itemid, amount) VALUES (1,1,42) ON DUPLICATE KEY UPDATE amount = amount + 42 


Affected rows will then return: 1 if the row was inserted or 2 if an existing row was updated.

See also here: https://sampforum.blast.hk/showthread.php?tid=505081. Section 'Setting limits'.

[dusk]

Well the thing is that there is no item ID. I use text values, the item name. It's an include and all usable item names must be specified before using them.

I guess I could create a key from username + itemname, but I read a StackOverflow post that discourages usage of varchar keys.

EDIT: I just started loading the unique column I already had... It's still better then using varchar as a key I guess.