I am new to MySQL. I am getting a user phonebook from their mobile device, and I am saving into a database.

Table Phonebook (User ID is INT)

INSERT INTO `Phonebook`
            (`user_id`, `first_name`, `mobile`)
    VALUES(4,'tom',' 919810012345');

It works fine, but if user again upload his phonebook then I want to skip users existing contacts and add only new one from his/her phonebook and the data can be compare with user_id, first_name, mobile and these columns can't be duplicate.

If the user has 919810012345 and the next time 9810012345/9810-0123-45 then add again to the database.

I tried this, but it's not working:

INSERT Phonebook (`user_id`, `first_name`, `mobile`)
    Values (SELECT `user_id`, `first_name`, `mobile`
            FROM Phonebook
            where `user_id` != $user_id
            AND `first_name` !=$name
            AND `mobile` != $phn)

Table Schema

CREATE TABLE `Phonebook` (
 `id` int NOT NULL AUTO_INCREMENT,
 `user_id` int NOT NULL,
 `first_name` varchar(250) NOT NULL,
 `mobile` varchar(250) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3520 DEFAULT CHARSET=latin1

Phone Book Table

Table Name: PhoneBook

id  user_id first_name   mobile
1   100      John         91981000000
2   100      Tom         91981000001
3   100      Ron         9810-000-02
4   100      Mat         91981000003
5   100      Miley       981000004

CodePudding user response：

Add a UNIQUE constraint

CREATE TABLE `Phonebook` (
 `id` int NOT NULL AUTO_INCREMENT,
 `user_id` int NOT NULL,
 `first_name` varchar(250) NOT NULL,
 `mobile` varchar(250) NOT NULL,
 PRIMARY KEY (`id`),
  UNIQUE (`user_id`, `first_name`, `mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=3520 DEFAULT CHARSET=latin1

INSERT INTO `Phonebook`
            (`user_id`, `first_name`, `mobile`)
    VALUES(4,'tom',' 919810012345');

SELECT * FROM `Phonebook`

INSERT INTO `Phonebook`
            (`user_id`, `first_name`, `mobile`)
    VALUES(4,'tom',' 919810012345')
  ON DUPLICATE KEY UPDATE user_id = user_id;

SELECT * FROM `Phonebook`

fiddle