Jump to content

INV Sys


ahmedo01

Recommended Posts

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.

Link to comment

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

Link to comment

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.

Link to comment

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.

Link to comment

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.

Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...