INV Sys

[ahmedo01]

I want to add to table if not exists else update but i dont know how to do it. I tried this.

  
function addItem(plr,cmd,accountname,itemname,friendlyname,amount,itemtype) 
    if plr and cmd and accountname and itemname and friendlyname and itemtype then 
        dbExec( con, "IF EXISTS(SELECT * FROM envanter WHERE accountname='?' AND itemname='?') THEN UPDATE envanter SET amount='1' WHERE accountname='?' AND itemname='?' ELSE INSERT INTO envanter (id,accountname,itemname,friendlyname,amount,itemtype) VALUES(NULL,'1','1','1','1','1'",accountname,itemname,accountname,itemname)       
    end 
end 
addCommandHandler("additem",addItem) 

Connection working

I did it for testing.

[MIKI785]

I dont think this is right at all (but i might be wrong im no SQL expert), the way i do it is that i select the row and check if row count is 1, if it is update it or add it otherwise.

[Addlibs]

If you have a unique primary key column assigned, you can perform a standard INSERT statement followed with ON DUPLICATE KEY UPDATE

INSERT INTO tablename (col1, col2, col3) 
VALUES(?, ?, ?) 
ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2), col3 = VALUES(col3); //Remember that these are column names, nothing related to the values on this line. 

Source: http://stackoverflow.com/questions/1263 ... ntax-error

[JR10]

You can use "INSERT OR REPLACE". At least one column must be unique or primary key, or else it will just keep inserting.

Here is an example:

INSERT OR REPLACE INTO table (name, age) VALUES ("Test", 15) 

If the name column is unique and a row exists with "Test", and any other age, it will update the age to 15. If no row exists with "Test", it will insert one.

[ahmedo01]

Can u give example for WHERE accountname and itemname?

[JR10]

You need to set both of the accountname and itemname to unique. It's simple really:

INSERT OR REPLACE INTO evanter (accountname, itemname, friendlyname, amount, itemtype) VALUES (ACCOUNTNAME, ITEMNAME, FRIENDLYNAME, AMOUNT, TYPE) 

Also, in your original code, this query string is wrong:

INSERT INTO envanter (id,accountname,itemname,friendlyname,amount,itemtype) VALUES(NULL,'1','1','1','1','1'",accountname,itemname,accountname,itemname)    

You selected 6 columns to insert a value for, yet you send 10 values.

[ahmedo01]

But the problem is, i cant set itemname and accountname as unique. because every player can have same item. anyone give example for this?

if row count is 1, if it is update it or add it otherwise

[Addlibs]

Then just use the simple way.

SELECT * FROM envanter WHERE accountname = ? AND itemname = ?

if #result >= 1 then

UPDATE envanter SET something = ?, something_else = ? WHERE accountname = ? AND itemname = ?

else

INSERT INTO envanter (something, something_else, accountname, itemname) VALUES(?, ?, ?, ?)

end

Change accordingly to what you actually need to insert/update.

[ahmedo01]

Thanks for answer, i already did it.