How to use "Insert Ignore Into" Mysql query

Question

I have three tables :

user_details
user_badges
badges

I have a query where I use "insert ignore into" command to insert the badge id from badges and user id from user_details into user_badge.

$username=$_SESSION['username'];
$getlife="SELECT * FROM user_details WHERE username='$username'";
$getlifedb=mysqli_query($db, $getlife);
while ($row=mysqli_fetch_array($getlifedb)){
    if($row['fullife'] == '1'){
       $full="INSERT IGNORE INTO user_badge(user_id, badge_id) 
                SELECT u.user_id, b.badge_id 
                FROM user_details as u 
                    CROSS JOIN badges as b 
                WHERE u.user_id='$id' 
                AND b.badge_id='8'";
        mysqli_query($db, $full) or die(mysqli_error($db));
   }
}

Below is the picture of user_badge table:

But every time I execute the command, the insert ignore doesnt work, but instead it just insert a field again. Can someone help me fix this issue.

Please explain what you thought you were using IGNORE to achieve — RiggsFolly, Sep 01 '20 at 17:47
If you know the values of `user_id` and `badge_id` **What is the point** of running a query to select them?? You could write a simple query inserting the values into `user_badges` :) _I dont see where `$id` is actually given a value, but I am assuming it exists or the query makes even less sense — RiggsFolly, Sep 01 '20 at 17:50
@RiggsFolly with IGNORE , i thought i dont need to insert again the same field again in user_badge table, for example if the badge_id 8 is already present in the table , the next insert ignore should ignore the insert operation — Uviiii, Sep 01 '20 at 17:51
So what are the rules for Users and Badges? How many badges can a user have? — RiggsFolly, Sep 01 '20 at 17:52
@RiggsFolly there are 8 badges and the user can have each badge only once — Uviiii, Sep 01 '20 at 18:02
@RiggsFolly i have edited the question and added the picture of the user_badge table, if you see it you will understand . If i add unique key to user_id or badge _id . Then it will not allow the same user_id to have another badge_id — Uviiii, Sep 01 '20 at 18:10
Make the Unique Key BOTH COLUMNS See this [for an example](https://stackoverflow.com/a/635943/2310830) or look up [Composite Index](https://www.mysqltutorial.org/mysql-index/mysql-composite-index/) — RiggsFolly, Sep 01 '20 at 18:14
@RiggsFolly will it be possible to use update query instead of insert ignore into — Uviiii, Sep 01 '20 at 18:59
Not unless the row is already there and you just want to amend (UPDATE) it — RiggsFolly, Sep 01 '20 at 19:04
@RiggsFolly can u show me how to update the user_badge table with user_details and badges — Uviiii, Sep 01 '20 at 19:08
`CREATE INDEX \`user_badge_idx\` ON \`user_badge\` (\`user_id\`,\`badge_id\`);` — RiggsFolly, Sep 01 '20 at 19:10

Flash Thunder · Accepted Answer · 2020-09-02T09:45:14.573

2

INSERT IGNORE only works when a duplicating field in table is being set as UNIQUE. user_id in table user_badge is not set to UNIQUE, that's why it duplicates it.

If you need to have multiple badges for an user, but those are not supposed to be duplicated, you need to set the pair to be UNIQUE, -> UNIQUE(user_id,user_badge)

Without UNIQUE, INSERT IGNORE doesn't make any sense, as it doesn't throw any errors when inserting the same records.

edited Sep 02 '20 at 09:45

answered Sep 01 '20 at 17:44

Flash Thunder

10,029
6
39
82

but if i set the user _id has unique, then the same user_id will not be able to get other badges from badge id – Uviiii Sep 01 '20 at 17:48
yes ok, so you need to set `UNIQUE(user_id,user_badge)`, I edited my answer – Flash Thunder Sep 02 '20 at 09:43

score 0 · Answer 2 · answered Sep 01 '20 at 19:38

0

From msql documentation "Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error." I agree fully with @Flush Thunder. If you do not care having duplicate entries just use INSERT instead, but for what you´re writing this is not the case. Apparently, your user must have a unique id, while may have several badges which is consistent with a unique user.

answered Sep 01 '20 at 19:38

Erick

164
3
10

if i make the user_id unique then the same user_id cant have another badge _id – Uviiii Sep 01 '20 at 20:15
Yes Uviii, you may have has many badges and badge_ids as you want. For the info you provided it seems you're dealing with a one-to_many relationship where a user may have several badges ( and each badge it´s supposed to have an id). Otherwise, you may set up a one to one relationship where a user may have one and only one badge. – Erick Sep 01 '20 at 22:16
If this were the case (one - to one) your user_badge table is redundant. You may deleted and and place the pertinent fields in your user_details table. – Erick Sep 01 '20 at 22:19

How to use "Insert Ignore Into" Mysql query

2 Answers2