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
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
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.The easiest way I think is to create an index of the fields.
and the other way that I can think of is doing:
and validating it in PHP, verifying that the count is 0.
I hope it helps you 😉