php - 使用 PHP 将数据插入 mysql 之前截断

Question

我正在尝试使用 PHP 将 Excel 文件上传到 mysql 数据库。我已经完成了这个工作。但是，如果我刷新页面或再次上传另一个文件，它就会重复。

我想在上传新文件之前清除(截断)表，然后插入新数据。

Bue，如果单击“提交”按钮且在插入数据之前，我无法找到放置 TRUNCATE TABLE exitencias_2018; 的位置或方式。

另一个问题是令人耳目一新的事情。这是我上传数据后停止刷新的方法吗？或者刷新不会重复它的方式？

总而言之，我需要的帮助是:

• 在哪里放置以及如何截断表存在encias_2018;。
• 如果页面刷新，则停止复制数据。

这是我的代码:

<?php
$conn = mysqli_connect("localhost","root","","papa");
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.php');

if (isset($_POST["import"])){
    $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
    if(in_array($_FILES["file"]["type"],$allowedFileType)){
        $targetPath = 'uploads/'.$_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
        $Reader = new SpreadsheetReader($targetPath);
        $sheetCount = count($Reader->sheets());
        for($i=0;$i<$sheetCount;$i++){
            $Reader->ChangeSheet($i);
            foreach ($Reader as $Row){
                $model = "";
                if(isset($Row[0])) {
                    $model = mysqli_real_escape_string($conn,$Row[0]);
                }
                $cup = "";
                if(isset($Row[1])) {
                    $cup = mysqli_real_escape_string($conn,$Row[1]);
                }
                $color = "";
                if(isset($Row[1])) {
                    $color = mysqli_real_escape_string($conn,$Row[2]);
                }
                $description = "";
                if(isset($Row[1])) {
                    $description = mysqli_real_escape_string($conn,$Row[3]);
                }
                $size36 = "";
                if(isset($Row[1])) {
                    $size36 = mysqli_real_escape_string($conn,$Row[4]);
                }
                $size38 = "";
                if(isset($Row[1])) {
                    $size38 = mysqli_real_escape_string($conn,$Row[5]);
                }
                $size40 = "";
                if(isset($Row[1])) {
                    $size40 = mysqli_real_escape_string($conn,$Row[6]);
                }
                $size42 = "";
                if(isset($Row[1])) {
                    $size42 = mysqli_real_escape_string($conn,$Row[7]);
                }
                $size44 = "";
                if(isset($Row[1])) {
                    $size44 = mysqli_real_escape_string($conn,$Row[8]);
                }
                $size46 = "";
                if(isset($Row[1])) {
                    $size46 = mysqli_real_escape_string($conn,$Row[9]);
                }
                $size48 = "";
                if(isset($Row[1])) {
                    $size48 = mysqli_real_escape_string($conn,$Row[10]);
                }
                $size50 = "";
                if(isset($Row[1])) {
                    $size50 = mysqli_real_escape_string($conn,$Row[11]);
                }
                $size52 = "";
                if(isset($Row[1])) {
                    $size52 = mysqli_real_escape_string($conn,$Row[12]);
                }
                $size54 = "";
                if(isset($Row[1])) {
                    $size54 = mysqli_real_escape_string($conn,$Row[13]);
                }
                if (!empty($model) || !empty($cup) || !empty($color) || !empty($description) || !empty($size36) || !empty($size38) || !empty($size40) || !empty($size42) || !empty($size44) || !empty($size46) || !empty($size48) || !empty($size50) || !empty($size52) || !empty($size54)) {
                    $query = "insert into existencias_2018(model,cup,color,description,size36,size38,size40,size42,size44,size46,size48,size50,size52,size54) values('".$model."','".$cup."','".$color."','".$description."','".$size36."','".$size38."','".$size40."','".$size42."','".$size44."','".$size46."','".$size48."','".$size50."','".$size52."','".$size54."')";
                    $result = mysqli_query($conn, $query);
                    if (! empty($result)) {
                        $type = "success";
                        $message = "Datos de Excel importados en la base de datos satisfactoriamente";
                    } else {
                        $type = "error";
                        $message = "Ha habido un problema al importar los datos de Excel";
                    }
                }
            }
        }
    }else{ 
        $type = "error";
        $message = "Tipo de archivo invalido. Suba un archivo de Excel.";
    }
}
?>
<!DOCTYPE html>
<html>    
    <head>
        <style>    
            body {
                font-family: Arial;
                width: 1000px;
            }
            .outer-container {
                background: #F0F0F0;
                border: #e0dfdf 1px solid;
                padding: 40px 20px;
                border-radius: 2px;
            }
            .btn-submit {
                background: #333;
                border: #1d1d1d 1px solid;
                border-radius: 2px;
                color: #f0f0f0;
                cursor: pointer;
                padding: 5px 20px;
                font-size:0.9em;
            }
            .tutorial-table {
                margin-top: 40px;
                font-size: 0.8em;
                border-collapse: collapse;
                width: 100%;
            }
            .tutorial-table th {
                background: #f0f0f0;
                border-bottom: 1px solid #dddddd;
                padding: 8px;
                text-align: left;
            }
            .tutorial-table td {
                background: #FFF;
                border-bottom: 1px solid #dddddd;
                padding: 8px;
                text-align: left;
            }
            #response {
                padding: 10px;
                margin-top: 10px;
                border-radius: 2px;
                display:none;
            }
            .success {
                background: #c7efd9;
                border: #bbe2cd 1px solid;
            }
            .error {
                background: #fbcfcf;
                border: #f3c6c7 1px solid;
            }
            div#response.display-block {
                display: block;
            }
        </style>
    </head>
    <body>
        <h2>Importar existencias actualizadas</h2>
        <div class="outer-container">
            <form action="" method="post"
            name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
                <div>
                    <label>Buscar archivo Excel</label>
                    <input type="file" name="file" id="file" accept=".xls,.xlsx">
                    <button type="submit" id="submit" name="import" class="btn-submit">Importar</button>
                </div>
            </form>
        </div>
       <div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
<?php
$sqlSelect = "SELECT * FROM existencias_2018";
$result = mysqli_query($conn, $sqlSelect);
if (mysqli_num_rows($result) > 0){
?>
       <table class='tutorial-table'>
           <thead>
               <tr>
                   <th>Modelo</th>
                   <th>Copa</th>
                   <th>Color</th>
                   <th>Descripcion</th>
                   <th>36</th>
                   <th>38</th>
                   <th>40</th>
                   <th>42</th>
                   <th>44</th>
                   <th>46</th>
                   <th>48</th>
                   <th>50</th>
                   <th>52</th>
                   <th>54</th>
               </tr>
           </thead>
<?php
$sql = "TRUNCATE TABLE existencias_2018";
while ($row = mysqli_fetch_array($result)) {
?>                  
           <tbody>
               <tr>
                   <td><?php  echo $row['model']; ?></td>
                   <td><?php  echo $row['cup']; ?></td>
                   <td><?php  echo $row['color']; ?></td>
                   <td><?php  echo $row['description']; ?></td>
                   <td><?php  echo $row['size36']; ?></td>
                   <td><?php  echo $row['size38']; ?></td>
                   <td><?php  echo $row['size40']; ?></td>
                   <td><?php  echo $row['size42']; ?></td>
                   <td><?php  echo $row['size44']; ?></td>
                   <td><?php  echo $row['size46']; ?></td>
                   <td><?php  echo $row['size48']; ?></td>
                   <td><?php  echo $row['size50']; ?></td>
                   <td><?php  echo $row['size52']; ?></td>
                   <td><?php  echo $row['size54']; ?></td>
               </tr>
<?php
}
?>
           </tbody>
       </table>
<?php 
} 
?>
    </body>
</html>

Answer 1

这就是你想要的吗？

<?php
$conn = mysqli_connect("localhost","root","","papa");
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.php');

if (isset($_POST["import"])){
    $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
    if(in_array($_FILES["file"]["type"],$allowedFileType)){

        // Looks like we have a correct file to upload.
        // Let's TRUNCATE the table first :

        $query = "TRUNCATE TABLE existencias_2018;";
        mysqli_query($conn, $query);

       // OK, table is empty, proceed with upload....       

        $targetPath = 'uploads/'.$_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
        $Reader = new SpreadsheetReader($targetPath);
        $sheetCount = count($Reader->sheets());
        for($i=0;$i<$sheetCount;$i++){
            $Reader->ChangeSheet($i);
            foreach ($Reader as $Row){
                $model = "";
                if(isset($Row[0])) {
                    $model = mysqli_real_escape_string($conn,$Row[0]);
                }
        // and so on...