CODEWITHSUNDEEP
phpmysqldatabase
KingOfDog
KingOfDog

Reputation: 65

MySQL INSERT if record not exists

I want to insert a new record into a MySQL Table, but it should only be inserted if there isn't already a record with the same user_id. Besides I want to get a returned boolean which shows if a new record was inserted or not.

The table  
like_id | user_id | post_id  
1 | 1 | 3  
2 | 5 | 3  
3 | 12 | 5

Examples
This query INSERT INTO blog_post_likes (post_id, user_id) VALUES (3, 1) should return 'false'/'0'/or something else with the same meaning, because there is already a record with the same user_id. But this query INSERT INTO blog_post_likes (post_id, user_id) VALUES (3, 34) should return 'true'/1/or something else with the same beaning, because a new record was inserted.

How can I do that?

Upvotes: 1

Views: 1223

Answers (3)

jophab
jophab

Reputation: 5529

Before you insert the data, Use

SELECT * FROM blog_post_likes WHERE post_id=give_postid AND user_id=give_userid;

If the execution of this query returns a row, that means the user had already liked that post. So do not execute the insert query.

If no row is returned, execute the Insert query.

You can use mysqli_num_rows() to find the number of rows returned.

Upvotes: 0

CodeL
CodeL

Reputation: 191

You have to create a UNIQUE CONSTRAINT in the column you want to be unique. In this case we mention to the column user_id..

SQL COMMAND: 

CREATE TABLE blog_post_likes
(
post_id INT,
like_id INT,
user_id INT NOT NULL UNIQUE,
)

Of course you can alter your existing table to avoid loss your existing data in your table.

ALTER TABLE blog_post_likes ADD CONSTRAINT user_id UNIQUE (user_id)

Upvotes: 1

rai
rai

Reputation: 519

You must define both columns as primary key. That way, if you insert the same record, MySQL should return a 'Duplicate entry' error. You should check for error code 1062 as referenced here:

https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

Upvotes: 0

Related Questions