Is there any function/code to prevent saving duplicate values in the database?

Question

I have a website where subtitles are translated and where it is possible to save translated subtitles to mysql database.

In the database I have a table consisting of 2 significant columns: "name" and "langugage"

When a user who save their subtitle is obliged to enter the subtitle "name" and the "language" is selected automatically. So I need a code or function that I can insert via phpmyadmin or in my php file, so that when a user try to save subtitle in the "name" column that exists and when a language is selected in the "Language" column that already exists with the same "name" and in the same "language", a message appears to the user that it cannot save that subtitle because it already exists in the database and prevent user for saving the srt file.

php file:

ini_set('max_execution_time', 1000);
include "config.php";
include  'vendor/mantas-done/subtitles/src/Subtitles.php';
use \Done\Subtitles\Subtitles;
$subtitles = new Subtitles();

$data =  $_POST['data'];
$file_name =  $_POST['file'];
$subtitle =  $_POST['subtitle'];
$language =  $_POST['language'];
$author =  $_POST['author'];
$ip= $_SERVER['REMOTE_ADDR'];
if (array_key_exists('HTTP_X_FORWARDED_FOR', $_SERVER)) {
    $ip = array_pop(explode(',', $_SERVER['HTTP_X_FORWARDED_FOR']));
}


$data =json_decode($data, true);

foreach($data as $obj){

    $subtitles->add($obj['start'], $obj['end'],$obj['text']);
    $subtitles->save($file_name);
}
$ext = pathinfo($file_name, PATHINFO_EXTENSION);

$new_file_name_ext = time()."_".$subtitle."_".$language.".".$ext;
$new_file_name = time()."_".$subtitle."_".$language;
$query1 = "INSERT INTO movies (name , author, language, filename, user_ip)
            VALUES ('$subtitle','$author', '$language', '$new_file_name', '$ip')";
if(mysqli_query($conn, $query1)){
    copy("$file_name", "uploads_iota/$new_file_name_ext");
    //new GoodZipArchive('uploads_iota/$new_file_name',    'upload_iota/output_zip_file.zip') ;
    $data1 = "Successful, File saved to database.";

    $zip = new ZipArchive;
    $new_zip = time()."_".$subtitle."_".$language.".zip";
    if ($zip->open("uploads_zip/$new_zip", ZipArchive::CREATE) === TRUE)
    {
        $zip->addFile("uploads_iota/$new_file_name_ext", "$new_file_name_ext");
        $zip->close();
    }
} else{
    $data1 = mysqli_error();
}

echo $data1; ?>

Validation (on the PHP side) or unique keys (on the MySQL) or a combination of the two. — ceejayoz, Oct 25 '19 at 17:50
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 25 '19 at 17:51
You have an error. `mysqli_error()` needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 25 '19 at 17:51

score 3 · Answer 1 · answered Oct 25 '19 at 17:56

3

Generally you do this kind of thing by creating a unique index on the two columns where uniqueness is enforced. It's (name,language) in this case.

Try this command, when you create your table, to make this index

CREATE UNIQUE INDEX name_language_unique ON movies(name, language);

Then, your INSERT function generates an SQL error when your user tries to insert a forbidden duplicate. You can detect that error and inform the user.

answered Oct 25 '19 at 17:56

O. Jones

103,626
17
118
172

Worth to mention that composite index like this won't work for strings with sum length over 255 characters – rkosegi Oct 25 '19 at 19:34

score 0 · Answer 2 · answered Oct 25 '19 at 19:26

The easiest way I think is to create an index of the fields.

ALTER TABLE `your_table_name`
ADD UNIQUE INDEX `name_language` (`name`, `languaje`) USING BTREE;

and the other way that I can think of is doing:

SELECT COUNT(name) AS count WHERE name='your_value' AND languaje='your_languaje';

and validating it in PHP, verifying that the count is 0. I hope it helps you ;)

Is there any function/code to prevent saving duplicate values in the database?

2 Answers2