Insertion based on some condition (Mysql)

Question

I have table tags with field id,name

On front end the user enter tags comma separated in a textarea associated with the corresponding product

so during insertion i want to add the tags to the tag table that are not in the tag table.

so all working fine but the problem is i want to add the specific tags to the table that are not in the tag table

e.g: Consider the form

Name of product: Orange Juice Description: description of orange juice. Tags: Water,juices,drinks

so i want to add those tags to the tag table that are not present in the table. e.g water and drink is in the table and juice is not so only add juice to the tag table.

possible duplicate of [Best way to avoid duplicate entry into mysql database](http://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database) — MrCode, Jun 05 '13 at 11:29
What is field id? Is the tag name unique to the table or unique to the field id? — Expedito, Jun 05 '13 at 11:31
In any event, I would follow the link that MrCode posted, because using INSERT with ON DUPLICATE KEY UPDATE is what I was going to recommend, because you can add multiple items with one query (no looping) and at the same time avoid duplicates. — Expedito, Jun 05 '13 at 11:38

score 3 · Answer 1 · answered Jun 05 '13 at 11:41

You could have a temporary table called lets say, temptags, insert there the tags you entered in the textarea, and then insert there the tags temporary to inser them after in the tags table checking that they doesn't exists, something like this: http://sqlfiddle.com/#!2/c2855/1

CREATE TABLE tags (
  tag VARCHAR(32)
);

INSERT INTO tags VALUES
('Water'),
('Drinks');


CREATE TABLE temptags (
  tag VARCHAR(32)
);

INSERT INTO temptags VALUES
('Water'),
('drinks'),
('juices');


INSERT INTO tags SELECT tag FROM temptags WHERE UCASE(tag) NOT IN (SELECT UCASE(tag) FROM tags);

score 2 · Answer 2 · answered Jun 05 '13 at 11:27

2

If you make the column 'name' unique, then you could use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE ...

answered Jun 05 '13 at 11:27

Pudge601

2,048
1
12
11

score 2 · Answer 3 · answered Jun 05 '13 at 11:28

2

You can use explode your form tags to have those as an array and then comparing each value of your array against tag already stored. So you will be able to find new ones and INSERT them in your table.

answered Jun 05 '13 at 11:28

Fabio

23,183
12
55
64

score 2 · Answer 4 · answered Jun 05 '13 at 11:45

Try this

$part=substr($string,strripos($string,"Tags:")+5); // to get the tags list after "Tags:". 

$part=explode(",",$part);


$con=mysqli_connect("host", "user", "password", "db");

$query="select tag from tag"; 

if(!($result=mysqli_query($query))){
    //error
}
$table_tags=array();
while($row=mysqli_fetch_assoc($result)){
    $table_tags[]=$row['tag'];
}

$new_tags=array_diff($part,$table_tags);

//prepare query
$query="insert into tags values ";
foreach($new_tags as $tag){
    $query=."(".$tag."), ";
}
$query=substr($query,0,-1); //remove the last comma

if(!mysqli_query($con,$query)){
    //error
}
//else success

score 1 · Accepted Answer · answered May 09 '14 at 07:58

1

use INSERT IGNORE INTO table

see http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

there's also INSERT … ON DUPLICATE KEY UPDATE syntax, you can find explanations on dev.mysql.com

answered May 09 '14 at 07:58

Zain ud din

28
3

Insertion based on some condition (Mysql)

5 Answers5