通过PHP避免Mysql中的重复

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");}
}
?>

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.

Keep the insert query part in the else condition

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.