MySQL check for duplicate records - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL check for duplicate records (/showthread.php?tid=513256)

---

MySQL check for duplicate records - dusk - 15.05.2014

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

---

Re: MySQL check for duplicate records - Vince - 15.05.2014

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:
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.
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'.

---

Re: MySQL check for duplicate records - dusk - 15.05.2014

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.

---