adding items to the database

[Achmed]

When adding stuff to the database i use

INSERT INTO `traders_data` (`item`,`qty`,`buy`,`sell`,`order`,`tid`,`afile`) VALUES

however if the item already exists it causes an error and then stops.

 

Is it possible to do it where if it exists it just skips it and continues?

[Snowmobil]

You can use INSERT IGNORE:

 

http://dev.mysql.com/doc/refman/5.5/en/insert.html

[Achmed]

so would that just be added after insert so would be

INSERT IGNORE INTO `traders_data` (`item`,`qty`,`buy`,`sell`,`order`,`tid`,`afile`) VALUES

?

[Snowmobil]

Yes, that way all errors caused by the insert statement are ignored.

[Achmed]

Thanks.

[Tactical Ex]

Check on this before you take my word for it but if you are CHANGING something in the db its better to use "UPDATE" rather than insert.

 

UPDATE traders_data

SET (`item=____`,`qty=____`,`buy=____`,`sell=____`,`order=____`,`tid=____`,`afile=____`)

WHERE ('item=____')

 

The above assuming that the field "ITEM" is the key field or unique identifier for the record and "traders_data" is the name of the table.

 

Edit: just re-read the original post ... you probably are already doing this but you are inserting objects in batches and don't know if the object was added already or not which is causing the errors.  In that case what was prescribed before my comment should work for you.  I guess the only advice is that I would write your script to ignore and record the record it tried to insert into a temp table and at the end of your insert statement pull the info from the temp table into an update statement and push all your changes in one execution. :wacko: 

[Achmed]

ok i have no clue what you said there lol.

 

Im adding things to my db in batches but making a sql file that i can run after updates etc to save adding them all again. the problem is i have to make it many of times and add them all into the one file. would be much easier if i just had the one file

[Tactical Ex]

ok i have no clue what you said there lol.

 

Im adding things to my db in batches but making a sql file that i can run after updates etc to save adding them all again. the problem is i have to make it many of times and add them all into the one file. would be much easier if i just had the one file

Sorry for the confusion. What you are trying to do is a great idea.  The method in which you are doing it is probably fine if you haven't run into problems before now.  I was just trying to suggest a cleaner and quicker approach.

 

I'm not 100% sure about all of this as I use SQL Server Management Studio for a different kind of db for my job and not a MySQL db, but they should be virtually the same.

 

By using the "INSERT INTO IGNORE" command while trying to update/create multiple records/rows in the table you will ADD anything new but for example if you are trying to change the QTY of an existing item using that command it will skip it because the key or unique ID of that row (probably the "item" column) already exists. After this happens you have 2 choices, log the errors (digital log or writing down on paper...w/e) and use that info to create a NEW script that will UPDATE existing entries OR write a script that will identify if the key exists and automatically switch to an UPDATE method for each records/row. You will write twice the code but it will be nice in the long run.

 

If you want to INSERT and UPDATE safely in a single script I suggest reading this http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html it will explain it much better.

 

If you get this working for you I'd like to see it! :D

[FOXHOUND1776]

question about that , would it be possable to put military vehicals on the traders to be bought or sold and if sellin  would the, say a tank come with a key ?

[Achmed]

Thanks tactical i get what you mean now.

Insert ignore ahould work fine for me as im only looking to add new items to the database and where they get added over time the ignore part will skip what has already been added

[MrTesla]

question about that , would it be possable to put military vehicals on the traders to be bought or sold and if sellin  would the, say a tank come with a key ?

It should. I haven't tried all vehicles, but the BTR90 and Mi-24s worked.

[FOXHOUND1776]

sweet  ty ill give it a shot friday nite and see if it works :)