MySQL Script not entering correctly

Question

I have a MySQL and PHP script to enter a row when it doesn't exist.

INSERT INTO `tblPlayerAchievements` (HS_ID, AchievementID, AchievementProgress) 
SELECT * FROM (SELECT ".$userData['HS_ID'].", ".$achievementID.", 0) AS tmp 
WHERE NOT EXISTS (SELECT * FROM `tblPlayerAchievements` 
WHERE HS_ID=".$userData['HS_ID']." AND AchievementID=".$achievementID.") 
LIMIT 1

This works perfectly until both $userData['HS_ID'] and $achievementID are the same value and then it skips that one. How do I fix this?

This sounds like a very convoluted way of doing something very simple. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Dec 6, 2014 at 0:12
I need to enter the row if it doesn't exist. How else do I do it, if not this way? — Tom Hanson
– Tom Hanson, Commented Dec 6, 2014 at 0:16
Once its entered into the database I can then use that info. I'm storing achievement progress in the database. I can then reload that achievement progress later down the line. — Tom Hanson
– Tom Hanson, Commented Dec 6, 2014 at 0:21
why don't u check if the record doesn't exist first before insert it? — DarkBee
– DarkBee, Commented Dec 6, 2014 at 0:24

Ignacio Vazquez-Abrams · Accepted Answer · 2014-12-06 00:26:35Z

1

Since you haven't bothered answering the question about what happens if the row exists, I'm going to assume that you want the INSERT to fail. In that case, all you need to do is create a UNIQUE index on (HS_ID, AchievementID). I also recommend that you set a default value of 0 on AchievementProgress so that your query only needs to specify the IDs to create the row.

answered Dec 6, 2014 at 0:26

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Hanson Over a year ago

I did answer why I needed it inserted. So I could recall the information later. -- EDIT Oh sorry I misread you're comment. Yeah I need it to fail if it does exist.

Tom Hanson Over a year ago

HS_ID and AchievementID are concatenated primary key. It works on ALL other entries until HS_ID and AchievementID are the same value. That insert then fails.

Ignacio Vazquez-Abrams Over a year ago

So then... what exactly do you want the query to do?

Tom Hanson Over a year ago

I want it to insert the query if it does not exist. If it does exist, it should fail. I already have HS_ID and achievementID as concatenated primary keys. I don't know why its skipping when HS_ID and AchievementID are the same value.

Ignacio Vazquez-Abrams Over a year ago

I don't understand why you're doing that huge mass of a query then. An INSERT will fail if the fields in a unique key have values that already exist in the table.

Collectives™ on Stack Overflow

MySQL Script not entering correctly

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related