skip to Main Content

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 DoneSubtitlesSubtitles;
$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; ?>

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 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 😉

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search