Insert multiple rows and columns into MySQL while ignoring duplicates in one column

I want to insert rows into MySQL for three columns, but ignore the insert if ALL THREE COLUMNS are duplicates

Here's my current sql statement in PHP (example):

$sql = "INSERT INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john');";

This works fine, however if there was already an id_number of '1234' in the database, it will now have two such entries.

I would like 1234 to be ignored, but ONLY if:
1234 is already present in a row
user id 10 is already present in same row
username john is already present in same row

Is this possible in one SQL statement?

Thanks,
-Carlos-
solrac149Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JB04Commented:
which dont you use an auto increment column ?

that way all id_number records will be unique
twstdpairsCommented:
I agree with JB04.  That will definitely be good practice I'd say - setting the id_number as an auto_increment index.  You could also incorporate some error checking that will supply the user with a message.

After looking at your post, and forgive me if I interpret incorrectly, but it looks like you mean to check duplication on the user_id field and if so, not generate a new entry with a new id_number.  Is that what you are trying to do?
Muhammad WasifCommented:
Either you can use  INSERT IGNORE

$sql = "INSERT IGNORE INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john')";

But to use this, you must need to set the columns to unique. For more details http://dev.mysql.com/doc/refman/4.1/en/insert.html
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Muhammad WasifCommented:
No need to use ; in the query like you did in your first query
$qry = "INSERT IGNORE into test (column1) values('value1'), ('value2');";
                                                                      ^
It should be like this
$qry = "INSERT IGNORE into test (column1) values('value1'), ('value2')";
                                                                      ^
Note the difference of ;
solrac149Author Commented:
Re: Wasifg
Thanks for your comment!
In needing to remove the ; (semicolon) from the end of the query
Not true: My sql query is running inside of a loop in PHP, therefore the semicolon at the end of the query is necessary

Re: Twstdpairs and JB04
Thanks for your comments!
The id_number field is a value imported from another set of data. It is not to be auto-generated by mySQL

Re: Wasifg
Thanks for your tip!
The INSERT IGNORE seems like it might work... but the INDIVIDUAL columns are not unique --

If I have a row like this:
'3456', '10', 'John'

Then I can NOT have another row like this:
'3456', '10', 'John'

But I CAN have another row like this:
'3456', '5', 'Bob'

Will the IGNORE statement still work this way? Thanks for all your help so far!
Muhammad WasifCommented:
If your id_number column is unqiue, then it will not insert this row.
solrac149Author Commented:
It didn't work that way, I tried it...

if id_number column is unique, then the row:
'3456', '5', 'Bob'

Will not be inserted either.

It needs to be some kind of ... if ALL columns are identical

Something like this:

$sql = "INSERT IGNORE {{{{ALL COLUMNS}}}} INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john')";

Then it will insert EVERYTHING EXCEPT those where all three values are identical to an already existing row. Is this possible?

Thanks so much for any help!

-Carlos-
Muhammad WasifCommented:
For this you need to create a combine UNIQUE index on all the columns in this manner
ALTER TABLE `test` ADD UNIQUE unique_index (id_number,user_id,username);

Now try your query

$sql = "INSERT INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john')";

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
solrac149Author Commented:
Thank you for that info, wasifg!
This works but results in an error when duplicates are found, instead of ignoring them

But INSERT IGNORE INTO test

worked

This is the solution!! I will add another 50 points and accept. Thanks!
Muhammad WasifCommented:
oooppps

I forgot to add IGNORE in the query, but you are wise enough;) Thanx for the points

Muhammad Wasif
solrac149Author Commented:
No problem
I am not wise enough to have known all this on my own
but at least wise enough to put the last piece together haha
Thanks again
-Carlos-
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.