CODEWITHSUNDEEP
phpmysqlwampspreadsheetxlsx
Ole
Ole

Reputation: 41

Insert variable into MySQL DB table

I'm following this tutorial: https://itsolutionstuff.com/post/php-import-excel-file-into-mysql-database-tutorialexample.html

It uses this library: https://github.com/nuovo/spreadsheet-reader

Most of the code seems to work. I've created a page where you can select a file from your computer and "upload" it. We then create a table of the data in the file on our page.

The problem is at the last and most important function: importing the data to the local MySQL database.

My connection looks like this:

<?php
    $servername = "localhost";
    $dbname = "analyse";
    $password = "";
    $username = "root";
    $mysqli = new mysqli("localhost", "root", "", "analyse");
?>

I'm using the root account which have no password. The database is called "analyse". The table we are going to import data to is called "test". I've tried with another account I created which had the username "import" and password "123" and it didn't give any results either.

The rest of my PHP script looks like this:

<?php
require('library/php-excel-reader/excel_reader2.php');
require('library/SpreadsheetReader.php');
require('db_config.php');

if(isset($_POST['Submit'])){
  $ok = 1;
  $mimes = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.oasis.opendocument.spreadsheet'];
  if($ok = 1); {
    $uploadFilePath = 'uploads/'.basename($_FILES['file']['name']);
    move_uploaded_file($_FILES['file']['tmp_name'], $uploadFilePath);
    $Reader = new SpreadsheetReader($uploadFilePath);
    $totalSheet = count($Reader->sheets());
    echo "Du har ".$totalSheet." regneark".

    $html="<table border='1'>";
    $html.="<tr>
    <th>Account Number</th>
    <th>forstelisten</th>
    <th>andrelisten</th>
    <th>Account</th>
    </tr>";

    /* For Loop for all sheets */
    for($i=0;$i<$totalSheet;$i++){
      $Reader->ChangeSheet($i);
      foreach ($Reader as $Row)
      {
        $html.="<tr>";
        $accountnumber = isset($Row[0]) ? $Row[0] : '';
        $forstelisten = isset($Row[1]) ? $Row[1] : '';
        $andrelisten = isset($Row[2]) ? $Row[2] : '';
        $account = isset($Row[3]) ? $Row[3] : '';
        $html.="<td>".$accountnumber."</td>";
        $html.="<td>".$forstelisten."</td>";
        $html.="<td>".$andrelisten."</td>";
        $html.="<td>".$account."</td>";
        $html.="</tr>";

        $query = "
        INSERT INTO test(Account Number, forstelisten, andrelisten, Account) 
        VALUES('".$accountnumber."','".$forstelisten."','".$andrelisten."','".$account."')";
        $mysqli->query($query);
       }
    }
    $html.="</table>";
    echo $html;
    echo "<br />Data lagt inn i databasen.";
  }
}
?>

After running this I get the table and the sheet count and the message at the end but nothing shows up in MySQL database and there are no error messages.

The table we are trying to import to (test) have four colons: Account Number, forstelisten, andrelisten, Account. They are all int. The xlsx file we are trying to import is called test. It contains four colons and three rows. All these cells only have numbers in them (different numbers from 1 to 300, no decimals).

The MySQL database is running on WAMP server. PHP version is 7.1.9. The MySQL version is 5.7.19.

Please ask if you need any more information and thank you for your help.

EDIT:

It now works! Thank you everyone.

The working code looks like this:

<?php
require('library/php-excel-reader/excel_reader2.php');
require('library/SpreadsheetReader.php');
require('db_config.php');

if(isset($_POST['Submit'])){
  $ok = 1;
  $mimes = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.oasis.opendocument.spreadsheet'];
  if($ok == 1); {
    $uploadFilePath = 'uploads/'.basename($_FILES['file']['name']);
    move_uploaded_file($_FILES['file']['tmp_name'], $uploadFilePath);
    $Reader = new SpreadsheetReader($uploadFilePath);
    $totalSheet = count($Reader->sheets());
    echo "Du har ".$totalSheet." regneark".

    $html="<table border='1'>";
    $html.="<tr>
    <th>Account Number</th>
    <th>forstelisten</th>
    <th>andrelisten</th>
    <th>Account</th>
    </tr>";

    /* For Loop for all sheets */
    for($i=0;$i<$totalSheet;$i++){
      $Reader->ChangeSheet($i);
      foreach ($Reader as $Row)
      {
        $html.="<tr>";
        $accountnumber = isset($Row[0]) ? $Row[0] : '';
        $forstelisten = isset($Row[1]) ? $Row[1] : '';
        $andrelisten = isset($Row[2]) ? $Row[2] : '';
        $account = isset($Row[3]) ? $Row[3] : '';
        $html.="<td>".$accountnumber."</td>";
        $html.="<td>".$forstelisten."</td>";
        $html.="<td>".$andrelisten."</td>";
        $html.="<td>".$account."</td>";
        $html.="</tr>";

        $query = "
    INSERT INTO `analyse`.`test`(`Account Number`, `forstelisten`, `andrelisten`, `Account`) 
    VALUES('$accountnumber','$forstelisten','$andrelisten','$account')";
        $mysqli->query($query);
       }
    }
    $html.="</table>";
    echo $html;
    echo "<br />Data lagt inn i databasen.";
  }//else { 
    //die("<br/>Sorry, File type is not allowed. Only Excel file."); 
  //}
}
?>

Upvotes: 0

Views: 364

Answers (1)

shubhangee
shubhangee

Reputation: 539

I think your column name (Account Number) is in two words, so try with backquotes around non-conformant field names like below and change your condition ($ok = 1) with ($ok == 1)

$query = "INSERT INTO test(`Account Number`, forstelisten, andrelisten, Account) VALUES ('".$accountnumber."','".$forstelisten."','".$andrelisten."','".$account."')";

Upvotes: 2

Related Questions