Avoid duplicates in Mysql through PHP

Question

I have the following table in my database:

CREATE TABLE subjects (
  subject_id int(11) NOT NULL AUTO_INCREMENT,
  subject text,
  PRIMARY KEY (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

I also insert the table data from this PHP and it works fine:

<?php
include('Conexion.php');
$subject = "";

if (isset($_POST['reg_subject'])) {
    $text = $_POST['text'];

        $query = "INSERT INTO subjects (subject) VALUES('$text')";
        mysqli_query($conn, $query); 
        header("Location: index.PHP");
}
?>

The problem is that the user can enter duplicate subjects and I would like to avoid that.

I have tried to show an alert message if the subject already exists, but it continues accepting duplicate subjects. How could I avoid duplicate subjects with the same name?

This is how I’m doing it:

<?php
include('Conexion.php');

$subject = "";

if (isset($_POST['reg_subject'])) {
    $text = $_POST['text'];

 $subject_check_query = "SELECT * FROM subjects WHERE subject='$subject'";
$result = mysqli_query($conn, $subject_check_query);
$text = mysqli_fetch_assoc($result);
$message = "Already Exists";

if ($text) { // if subject exists
    if ($text['text'] === $subject) {
        echo "<script type='text/javascript'>alert('$message');</script>";
    }
}else{

        $query = "INSERT INTO subjects (subject) VALUES('$text')";
        mysqli_query($conn, $query); 
        header("Location: index.php");}
}
?>

Make the column UNIQUE, then the record will not be inserted. If you want that the subject can be duplicated, but from other users make a constrait unique key with User_id and subject — Luís Chaves, May 10 '19 at 23:25
@LuísChaves I've tried using a constraint key with `ALTER TABLE subjects ADD CONSTRAINT constraint_subject UNIQUE KEY(subject(191));` but it's not working — ana, May 10 '19 at 23:27
@LuísChaves I've tried your code with but I get this error: `Error Code: 1062. Duplicate entry '' for key 'constraint_subject'` but I don't have any null values in my table :/ — ana, May 10 '19 at 23:31
You want to read about [SQL Injection](https://www.php.net/manual/en/security.database.sql-injection.php). — sticky bit, May 10 '19 at 23:57

score 0 · Answer 1 · answered May 10 '19 at 23:30

0

Add an if statement around the insert query if it returns a result back from your select query. Also it's an good idea to make use of xdebug this will tell you what happens in your code when you run it.

answered May 10 '19 at 23:30

disduivel

1
2

score 0 · Answer 2 · answered May 10 '19 at 23:34

0

Keep the insert query part in the else condition

answered May 10 '19 at 23:34

Man87

131
6

Add some more detail with sample query or point user where he is missing setup or some sort of code. – CodeChanger Jul 18 '19 at 05:16

score 0 · Answer 3 · answered May 11 '19 at 01:15

You can create a unique index for the subject_text column:

ALTER IGNORE TABLE subjects ADD UNIQUE (subject_text)

Then change your query to either of the following:

"INSERT IGNORE INTO subjects (subject_text) VALUES('$text')"

Or:

"INSERT INTO subjects (subject_text) VALUES('$text')
ON DUPLICATE KEY UPDATE subject_text = subject_text"

Note that this won't work if your DB already has duplicate values.

For an explanation of the difference between either of these answers, you can look at this question.

Avoid duplicates in Mysql through PHP

3 Answers3