Information on how to duplicate check when inspecting MySQL with PHP (PDO)

Environment
Centos 6.9
PHP 7.0
MySQL 5.1

Thank you for your help.
As stated in the title, I am writing to let you know how to duplicate check when inspecting MySQL with PHP (PDO).

The flow is to upload the CSV file from the web and INSERT it to MySQL.
At that time, only the contents of a specific column ([filedata] below) are checked for duplication, and if there is a duplication, do not
(Duplicate files do not INSERT later)
Also, after INSERT is completed, I would like to display the duplicate contents on the web.

I made the following by referring to other sites, but it was made using [INSERT IGNORE]
This will ignore other errors and will not be able to check for duplicates (it may be possible just because I don't know).
HTError message display and group contents are omitted.

<?php
$groupname=$_POST ["groupname"];
$importdate=date('Y-m-d H:i:s');

    try{
            $pdo = new PDO(
            'mysql:dbname=dbname; host=localhost; charset=utf8',
            'user',
            'passwd',
            array(
                PDO::MYSQL_ATTR_INIT_COMMAND=>"SET SESSION sql_mode='TRADITIONAL',
                PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES=>false,
            )
        );
        } catch(Exception$e){
        $msg = array('red', $e->getMessage());
    }

if(isset($_FILES['upfile']['error'])&is_int($_FILES['upfile']['error'])){
    try{
        switch($_FILES['upfile']['error'){
            case UPLOAD_ERR_OK:
                break;
            case UPLOAD_ERR_NO_FILE:
                through new RuntimeException('File is not selected');
            case UPLOAD_ERR_INI_SIZE:
            case UPLOAD_ERR_FORM_SIZE:
                through new RuntimeException('File is too large');
            default:
                through new RuntimeException ('Unknown error');
        }

        $tmp_name = $_FILES ['upfile'] ['tmp_name'];
        $filename=$_FILES["upfile"]["name"];
        $detect_order='ASCII, JIS, UTF-8, CP51932, SJIS-win';
        setlocale(LC_ALL, 'ja_JP.UTF-8');

        $buffer=file_get_contents($tmp_name);
        if(!$encoding=mb_detect_encoding($buffer,$detect_order,true)){
            unset($buffer);
            through new RuntimeException('Character set detection failed');
        }
        file_put_contents($tmp_name,mb_convert_encoding($buffer, 'UTF-8', $encoding));
        unset($buffer);

        $stmt=$pdo->prepare("INSERT IGNORE INTO import (filedata, importdate, groupname, filename) VALUES(?, '$importdate', '$groupname', '$filename')");

        $pdo->beginTransaction();
        try{
            $fp = fopen($tmp_name, 'rb');
            while($row=fgetcsv($fp)){
                if($row===array(null)){
                    continue;
                }
                if(count($row)!==1){
                    through new RuntimeException('Invalid column detected');
                }
                $executed=$stmt->execute($row);
            }
            if(!feof($fp)){
                through new RuntimeException('CSV parsing error');
            }
            fclose($fp);
            $pdo->commit();
        } catch(Exception$e){
            fclose($fp);
            $pdo->rollBack();
            throw$e;
        }

        if(isset($executed)){
            $msg = array('green', 'import completed');
        } else{
            $msg = array('black', 'There are nothing to import');
        }

    } catch(Exception$e){

        $msg = array('red', $e->getMessage());

    }

}
?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head></head>
<body>
  <form enctype="multipart/form-data" method="post" action="">
<select name='groupname'required/>
<option value='>Select Group</option>
</select>
      <input type="file" name="upfile"/><br/>>br/>
      <input type="submit" value="Import"/>
    </fieldset>
  </form>
</body>
</html>

Thank you for your help.

php mysql

2022-09-30 17:11

1 Answers

$stmt->execute($row); and $stmt->errorInfo() after running.

print_r($stmt->errorInfo());
Runtime error present: Array([0]=>23000[1]=>1062[2]=>Duplicate entry'1' for key'PRIMARY')
Running normally: Array([0]=>00000[1]=>[2]=>)

2022-09-30 17:11

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656