The table is not loaded into a DB?

G

German Zuiakov2021-07-24 13:06:59

PHP

German Zuiakov, 2021-07-24 13:06:59

Data is not loaded into the database I
use the PHPExcel
index.php library:

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Demo example on Read excel file and import data into MySQL database using PHPExcel | Mitrajit's Tech Blog</title>
  <script src="js/jquery.min.js"></script>
  </head>
  <body>
  <style>
  span { clear:both; display:block; margin-bottom:30px; }
  span a { font-weight:bold; color:#0099FF; }
  
  

  table { border:1px solid #ccc; color:#fff; margin-top:20px; }
  table th {
    background-color:#0099FF;
    color:#fff;
  }
  td {
    background-color:#00CCFF;
    height:15px;
    text-align:center;
  }
  
  span.msg {
    display:block;
    margin-top:20px;
  }
  h4 {
    border-bottom:1px solid #000;
    margin-top:30px;
  }
  </style>
  <body>
    <div class="wrapperDiv">
      <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="multipart/form-data">
        Загрузите файл : 
        <input type="file" name="uploadFile" value="" />
        <input type="submit" name="submit" value="Загрузить" />
      </form>
        
      <?php
      if(isset($_POST['submit'])) {
        if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {
          $allowedExtensions = array("xls","xlsx");
          $ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);
          if(in_array($ext, $allowedExtensions)) {
            $file_size = $_FILES['uploadFile']['size'] / 1024;
            if($file_size < 50) {
              $file = "uploads/".$_FILES['uploadFile']['name'];
              $isUploaded = copy($_FILES['uploadFile']['tmp_name'], $file);
              if($isUploaded) {
                include("db.php");
                include("Classes/PHPExcel/IOFactory.php");
                try {
                  //Load the excel(.xls/.xlsx) file
                  $objPHPExcel = PHPExcel_IOFactory::load($file);
                } catch (Exception $e) {
                  die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
                }
                  
                //An excel file may contains many sheets, so you have to specify which one you need to read or work with.
                $sheet = $objPHPExcel->getSheet(0);
                //It returns the highest number of rows
                $total_rows = $sheet->getHighestRow();
                //It returns the highest number of columns
                $highest_column = $sheet->getHighestColumn();
                
                echo '<h4>Data from excel file</h4>';
                echo '<table cellpadding="5" cellspacing="1" border="1" class="responsive">';
                
                $query = "insert into `user_details` (`id`, `name`, `mobile`, `country`) VALUES ";
                //Loop through each row of the worksheet
                for($row =2; $row <= $total_rows; $row++) {
                  //Read a single row of data and store it as a array.
                  //This line of code selects range of the cells like A1:D1
                  $single_row = $sheet->rangeToArray('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                  echo "<tr>";
                  //Creating a dynamic query based on the rows from the excel file
                  $query .= "(";
                  //Print each cell of the current row
                  foreach($single_row[0] as $key=>$value) {
                    echo "<td>".$value."</td>";
                    $query .= "'".mysqli_real_escape_string($con, $value)."',";
                  }
                  $query = substr($query, 0, -1);
                  $query .= "),";
                  echo "</tr>";
                }
                $query = substr($query, 0, -1);
                echo '</table>';
                
                // At last we will execute the dynamically created query an save it into the database
                mysqli_query($con, $query);
                if(mysqli_affected_rows($con) > 0) {
                  echo '<span class="msg">БД одновлена</span>';
                } else {
                  echo '<span class="msg">Не возможно обновить БД!</span>';
                }
                // Finally we will remove the file from the uploads folder (optional) 
                unlink($file);
              } else {
                echo '<span class="msg">Файл не загружен!</span>';
              }
            } else {
              echo '<span class="msg">Максимальный размер файла 50кб!</span>';	
            }
          } else {
            echo '<span class="msg">Этот тип файлов не поддержывается!</span>';
          }
        } else {
          echo '<span class="msg">Выберите excel файл!</span>';
        }
      }
      ?>
  </div>
</body>

db.php:

<?php
define('HOSTNAME','127.0.0.1');
define('DB_USERNAME','root');
define('DB_PASSWORD','');
define('DB_NAME', 'importing');

//global $con;
$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
// Check connection
if ($con -> connect_errno) {
  echo "Failed to connect to MySQL: " . $con -> connect_error;
  exit();
}
?>

sql:

CREATE TABLE IF NOT EXISTS `excel` (
  `action` varchar(255) NOT NULL,
  `name` varchar(50) NOT NULL,
  `date` date(8) NOT NULL,
  `etap` varchar(255) NOT NULL
);

Reply

Answer the question

In order to leave comments, you need to log in

2 answer(s)

G

Gennady S, 2021-07-24
@gscraft

Oh heaven. Try to separate the code and design, let a separate PHP file handle the logic, and another one for the output. What table are you inserting records into? `excel` or `user_details`?
Specifically for the problem, look at what you have in $query, output it in any convenient way (debugger, console, die($query), etc.), and see what's wrong there. You can try this query in a MySQL client (PHPMyAdmin, HeidiSQL, MySQL Workbench, Valentina Studio, mysql.exe, etc).

T

ThunderCat, 2021-07-24
@ThunderCat

What is the `excel` table here? you with the user it seems work?
Output the request, include errors, check that the request is executed after execution , and in general - connect in an object style, process everything else in a procedural ... porridge from php and html ... nightmares ...