Import multiple rows of data in excel to mysql using php

Question

I tried importing excel to mysql table with different data type, inclusive a date time column, but the code only seems to pick the first row of the excel data and the date is displayed as: 0000-00-00. How do I also ensure the file does not upload if trade_number($emapData[2]) is repeated as this is a primary key in my database table? Please I do need assistance to rectify this problems.

My HTML is this:

<form enctype="multipart/form-data" method="post" role="form">
        <div class="form-content">
            <h1>Traded Securities Data Upload</h1>

            <div><label for="exampleInputFile">File Upload: </label><br><input type="file" name="file" id="file"><p style="font-size: .8em; color: rgb(51,0,255);">Only Excel/CSV File to be uploaded.</p></div>
            <div><button type="submit" id="Import" class="btn btn-default" name="Import" value="Import">Upload</button></div>

          </div><!--end of form content div--><br class="clear-fix">
            </form>

while my PHP code block is

    <?php 
    if(isset($_POST["Import"])){
    // Connnection string to the database
    mysql_select_db($database_connect, $connect);
        echo $filename=$_FILES["file"]["tmp_name"];
        if($_FILES["file"]["size"] > 0){
            $file = fopen($filename, "r");
            $count = 0;                                         // add this line
            while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE){
                //print_r($emapData);
                //exit();
                $count++;// add this line

                if($count>1){// add this line
                  $sql = mysql_query("INSERT into trades_data(symbol,trade_date,trade_number,buy_order_date,buy_order_number,sell_order_date,sell_order_number,sell_account,buy_account,buy_firm,sell_firm,buy_firm_name,sell_firm_name,entry_time,price,quantity,settle_date,trade_value,settlement_value,updated) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]','$emapData[8]','$emapData[9]','$emapData[10]','$emapData[11]','$emapData[12]','$emapData[13]','$emapData[14]','$emapData[15]','$emapData[16]','$emapData[17]','$emapData[18]',now())") or die (mysql_error());
                  mysql_query($sql);
                }  // if count closed                                            // add this line
            } //while loop closed

            fclose($file);
            echo 'CSV File has been successfully Imported';
            header('Location: ../edit_pages.php');
        }
            else{
            echo 'Invalid File:Please Upload CSV File';}
    } // close first if
?>

Your code is vulnerable as hell for SQL injection, sanitize/check your user's input first (or use prepared statement), avoid using mysql_ extensions anymore(they are officially obsolete) and never, ever, ever use INSERT statements in the loop! — Bud Damyanov
– Bud Damyanov, Commented May 8, 2014 at 13:09

Oscar M. · Accepted Answer · 2014-05-08 13:46:11Z

1

USE PDO or mysqli and prepared queries. Not only will this gaurd against SQL injection attacks or other errors (like a cell having an apostrophe in it) but your inserts will be faster in the loop.
Ensure the dates that you are inserting are in a format that Mysql expects, safest is YYYY-MM-DD. Either update your spreadsheet to use that format, or transform the date value when you import it using PHP's DateTime class and its format() method

answered May 8, 2014 at 13:46

Oscar M.

1,0867 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hara Prasad · Accepted Answer · 2014-10-13 14:02:48Z

<form enctype="multipart/form-data" method="post" role="form">
                        <div class="form-group">
                                 <label for="exampleInputFile">File Upload</label>
                                 <input type="file" name="file" id="file" size="150">
                                <p class="help-block">
                                        Only Excel/CSV File Import.
                                </p>
                        </div>
                         <button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button>


                 </form>

And my PHP code as like, I used PHP function fgetcsv()

<?php 
if(isset($_POST["Import"]))
{
//First we need to make a connection with the database
$host='localhost'; // Host Name.
$db_user= 'root'; //User Name
$db_password= '';
$db= 'product_record'; // Database Name.
$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//print_r($emapData);
//exit();
$sql = "INSERT into prod_list_1(p_bench,p_name,p_price,p_reason) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
mysql_query($sql);
}
fclose($file);
echo 'CSV File has been successfully Imported';
header('Location: index.php');
}
else
echo 'Invalid File:Please Upload CSV File';
}
?>

Collectives™ on Stack Overflow

Import multiple rows of data in excel to mysql using php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related