CODEWITHSUNDEEP
phpmysql
Andrei Oniga
Andrei Oniga

Reputation: 8559

How to prevent duplication of values for a columns pair when doing a MySQL INSERT?

A table in my DB has the following columns: ID, campaign_id, opt_out_day The method that inserts a set of "opt-out-days" - an array of DATETIME values - is as follows:

/**
 * @param  Integer $campaignId  
 * @param  Array   $optOutDays  Array of DATETIME values
 */
public function addCampaignOptOutDays($campaignId, $optOutDays) {
        $query = "INSERT INTO `".self::$tblOptOutDays."`
                  (campaign_id, opt_out_day)
                  VALUES
                  (:campaign_id, :opt_out_day)";

        try {
            $this->connection->beginTransaction();
            $query = $this->connection->prepare($query);
            $query->bindParam(':campaign_id', $campaignId, \PDO::PARAM_INT);
            foreach ($optOutDays as $day) {
                $query->bindParam(':opt_out_day', $day, \PDO::PARAM_STR);
                $query->execute();
            }
            $this->connection->commit();
        } catch (\PDOException $e) {
            $this->connection->rollback();
            throw new \Models\Database\DatabaseException($e->getMessage());
        }
    }

How can I modify the query in order to prevent duplicating the same opt-out-day for a campaign? In other words, multiple rows with the same opt-out-day can exist in this table, as long as they have different a 'campaign-id'. Adding a unique key for the (campaign_id, opt_out_day) is not an option though, as I don't want to throw exceptions when such situations occur, I just want to not add the pair again.

Upvotes: 2

Views: 143

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53830

As Jakub Kania commented, you could use a unique index and use INSERT IGNORE:

With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

Then, simply check the affected rows value to see if the insert was successful.

So, other errors can still be detected, as warnings. I think INSERT ... ON DUPLICATE KEY UPDATE is a better solution, given you add the unique index because checking warnings can be a pain.

Upvotes: 0

Paul Negoescu
Paul Negoescu

Reputation: 93

Try using the INSERT ... ON DUPLICATE KEY UPDATE id=id

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

This will avoid an extra SELECT query but will also avoid an error if you have duplicate keys.

There's also INSERT IGNORE ... but that might ignore other errors as well

Upvotes: 2

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Im not familiair with php, but what if you select from the table and check if that returns a value, if not then insert?

Try

$checkquery = "SELECT 1 FROM `".self::$tblOptOutDays."` 
WHERE campaign_id=:campaign_id AND opt_out_day=:opt_out_day)";

Then

IF $checkquery <> 1 THEN
// run insert

Upvotes: -1

Related Questions