Php/MySQL Prevent Duplicate Entries Over Multiple Columns

php/mysql prevent duplicate entries over multiple columns

You can simply create a multiple-column index on these columns and enforce uniqueness: see the MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html.

For example, in a table with columns id (unique primary key), colA and colB, you run:

ALTER TABLE table ADD UNIQUE KEY (colA,colB)

This is it: any INSERTs leading to a duplicate entry in these two columns combined will now return a MySQL error instead of going through. If you use INSERT IGNORE, no MySQL error will be thrown if executing it would violate this unique constraint, and your INSERT statement would be quietly disregarded.

How can I prevent duplicate entries over multiple columns?

If you're using MySQL 8.0.16 or newer, you can use a CHECK constraint.

CREATE TABLE `FOLLOWERS` (
    `FOLLOWER_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
    `FOLLOWING_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
    `FOLLOWING_IN` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT CHECK (FOLLOWER_ID != FOLLOWING_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

If you're using an older version, see Can a MySQL trigger simulate a CHECK constraint? for how to emulate check constraints with triggers.

Prevent duplicate data PHP Mysql

You can add a UNIQUE constraint to the email field. This will be done by running the following query:

ALTER TABLE `event` ADD UNIQUE (email);

After that, when you want to insert a new row with an email that already exists in your table, you'll get an error like this: #1062 - Duplicate entry ...

You can catch that exception and react on it in your PHP code like this:

<?php
// perform INSERT query...
if (mysqli_errno($link) == 1062) {
    print 'An error occured';
}

how to avoid duplicate records insertion in php mysqli?

Below code will help you inserting unique data:

$result = mysqli_query($conn, "SELECT nama, member, peserta, tour FROM gathering where nama = '$nama' and member = '$member' and peserta='$peserta' and tour='$tour'")
$total = mysqli_num_rows($result);

if($total==0)
{
    mysqli_query($conn,"insert into gathering (nama, member, peserta, tour) 
values ('$nama', '$member', '$peserta', '$tour')");
} 

Avoiding duplicate entries in an mySQL table with non unique columns

You should review your table definition.

You can (from best to worst):

1. Add a composite primary key on (article_id and tag_id) and remove auto_increment (previous primary key)
2. Add an index (UNIQUE) on (article_id and tag_id) and keep your auto_increment primary key
3. Select distinct in php: SELECT DISTINCT(article_id, tag_id) FROM
... without changing anything in your table

Right now, your table is defined as something like this:

CREATE TABLE IF NOT EXISTS `article_tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The best solution (option 1) would be to remove your current (auto_increment) primary key and add a primary key (composite) on columns article_id and tag_id:

CREATE TABLE IF NOT EXISTS `article_tags` (
  `article_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`article_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

But (option 2) if you absolutely want to keep your auto_increment primary key, add an index (unique) on your columns:

CREATE TABLE IF NOT EXISTS `article_tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Anyway, if you don't want to change your table definition, you could always use DISTINCT in your php query:

SELECT DISTINCT(article_id, tag_id) FROM article_tags

Prevent duplicate values in database - mysql

You want a unique index on event_id and day:

create unique index idx_recurrence_event_day on recurrence(event_id, day)

This will prevent the duplication that you do not want.

If you already have duplicates there are various ways you can get rid of them. However, it would be easier if you had a unique id for each row, which one reason why an auto-incremented primary key is useful in all tables.

One way to get rid of them even without a unique id is to use:

alter ignore table recurrence add unique (event_id, day);

Personally, I don't like this method because adding an index shouldn't delete rows, but this is a valid MySQL extension.

How to prevent duplicate through grouping two columns? MYSQL/Codeigniter

you could try select distinct, but you'd need to narrow it down to just that field. In other words, try something like this:

Edited for codeigniter method for distinct()

$this->db->distinct();
$this->db->select ('reference_id');
$this->db->from('wall_posts AS p');
$this->db->join('users AS k', 'p.wall_poster = k.user_id AND p.wall_owner = k.user_id', 'left');
$this->db->group_by('p.wall_id');
$this->db->where('p.wall_poster', $pid); // $pid = given user id
$this->db->or_where('p.wall_owner', $pid);
$this->db->order_by('p.wall_id', 'desc');

Alternately you could try:

$this->db->select('DISTINCT(reference_id)');

How to prevent duplicate values from inserting mySQL considering two columns?

Thats a bit nasty, a commutative relationship between the 2 fields, but a unique index will not help given the values can be either way around.

If you could alter the code / data to ensure that the lower value of the ids was always placed in the user_1 field, that would at least then let the unique index work - but its a bit nasty.

Alternatively if the insertion is set based (e.g. not a row at a time but a set of rows) you could join to the existing data and anti-join based on both ways round e.g. :

(existing.user_1 = new.user_1 and existing.user_2 = new user_2)
 OR (existing.user_1 = new.user_2 and existing.user_2 = new user_1)

and in the where clause check to ensure no match was made (the anti part of the join)

where existing.link_id is null

That wouldn't be efficient for row at a time insertion though.

---

Related Topics