Trying to insert UNIQUE values using PHP and mySQL into database if certain values do not already exist

Question

Currently Ive been just making a SQL column UNIQUE to block duplicate entries but now I need to allow duplicate entries if another column is different. I am not sure on the best way to do this. Essentially I would like to do something as follows but it just inserts the same values multiple times.

// $link  is being outputted from a external RSS feed //
// $name and $location are account holder names //

$name = 'Bob';

$sql = "SELECT link FROM `table` WHERE name='$name'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $db_link = $row['link'];

        if($link == $db_link){
            // Pass and do nothing
        }else{
            // Insert into database if 3 values do not exist already
            $sql2 = "INSERT INTO `table` (link, name) VALUES ('$link','$name')";

            if (mysqli_query($conn, $sql2)) {
                echo "record created successfully";
            } else {
                //echo "Error: " . $sql2 . "<br>" . mysqli_error($conn);
            }
        }
    }
}

So basically I am trying to avoid showing duplicates per account holder, but a different account holder can have the same link in the database. Sorry if this is confusing.. If the name is different then allow a link to be a duplicate. I have looked at similar questions asked but do not understand the methods explained and if they would be the right solution I am looking for. Thanks!

you can index more than one column at a time CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...); — user10051234
– user10051234, Commented Aug 12, 2019 at 21:26
If I were to make name and location UNIQUE with link wouldn't that only allow one name and location to be inserted? Maybe I am confused on how that would work — Ryan D
– Ryan D, Commented Aug 12, 2019 at 21:29
No, it makes each combination of name and location unique, not each column by itself. — Barmar
– Barmar, Commented Aug 12, 2019 at 21:50
That's the difference between a single-column index and a multi-column index. — Barmar
– Barmar, Commented Aug 12, 2019 at 21:51
That will work. Then you don't need the loop at all, just try to insert and check for an error. — Barmar
– Barmar, Commented Aug 12, 2019 at 21:59

score 1 · Accepted Answer · 2019-08-12 22:07:39Z

1

you want a unique index on multiple columns

CREATE UNIQUE INDEX index_name ON table_name (link,name);

or

ALTER TABLE table_name ADD UNIQUE (link, name);

edited Aug 12, 2019 at 22:07

answered Aug 12, 2019 at 22:06

user10051234

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan D Over a year ago

haha dang I was thinking Barmar gave that comment, my bad.. Thanks Tim!

Collectives™ on Stack Overflow

Trying to insert UNIQUE values using PHP and mySQL into database if certain values do not already exist

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related