Updating Tags values in database (it gets duplicated)

1
votes

I have this form that send tags into my database separated with comas when i click create POST $query = "INSERT INTO data(tags) VALUES ('$tags')";

and receive them when i click edit 

GET
$query = "SELECT * FROM data WHERE id = $id";
$edit = mysqli_query($dbc, $query);
 while($row = mysqli_fetch_assoc($edit)){$tags = $row['tags'];}
POST
$tags = implode(",",$_POST['tags'];
$query = "UPDATE data SET tags= '$tags' WHERE id = $id";

And here is the problem, When i click update button I send both previous selected tags with new selected tags and add them on the previous tags an example to understand what i typed:

  • Database Tags: tag1,tag2
  • Form Edit Tags: [tag1][tag2]
  • Form New Tags: [tag1][tag2][tag3][tag4]

  • Database Updated Tags: tag1,tag2,tag1,tag2,tag3,tag4

    <form method="post" action=""> <select id="tags" name="tags[]" multiple="multiple"> <?php foreach ($tags as $tag) {echo "<option value'$tag' selected>$tag</option>";} ?> <option>tag1</option> <option>tag2</option> <option>tag3</option> <option>tag4</option> </select> <button type="submit" name="update">Submit</button> </form>

2
phpmysqltags
$tags = implode(",",$_POST['tags'] problem is this section, as you are using $_POST so it going to send what ever in $_POST in short your form is the problem, can you post your form code as well - arif_suhail_123
@arif_suhail_123 just putted the form - J. 4RK

2 Answers

1
votes

Fix your data structure!

Don't store tags as a list of multiple values in a single column. You want a table that has a single row for each item and each tag:

create table ItemTags (
    ItemTagId int auto_increment primary key,
    ItemId int not null,
    Tag varchar(255) not null,
    constraint fk_ItemTags_ItemId foreign key (ItemId) references Items(Item)
);

Then, when you want to insert a tag, just insert a tag into this table.

Why are storing multiple tags in a single column a bad idea?

  • You cannot (easily) validate whether the tags are unique for a given item.
  • You cannot (easily) list all tags that are being used.
  • MySQL has poor string manipulation functions.
  • MySQL cannot (easily) optimize queries that search for items with a single tag.
0
votes

You need to build a function to check if Tag 1 equals the Form Tag 1. Repeat this für all tags found in the DB(foreach). Then Update on the fly with the update function if needed.

A better aproach is to use the Array $tag, and delete all duplicates in it.

Example:

$tagArrayUni = array_unique($tag);