Importing csv file into mysql using PHP

Question

I am currently trying to import data into mysql through a csv file using PHP, while doing so i get the following error:

upload file name: test.csv You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name,Country,Currency) values('Company name','Country','Currency')' at line 1

So please help me out in solving the above error Thanks in advance

This is the code which i am using

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = '';

$database = 'automation';
$table = 'company';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");


    if(isset($_POST['submit']))
    {
         $fname = $_FILES['sel_file']['name'];
         echo 'upload file name: '.$fname.' ';
         $chk_ext = explode(".",$fname);

         if(strtolower(end($chk_ext)) == "csv")
         {

             $filename = $_FILES['sel_file']['tmp_name'];
             $handle = fopen($filename, "r");

             while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
             {
                $sql = "INSERT into company(Company name,Country,Currency) values('$data[0]','$data[1]','$data[2]')";
                mysql_query($sql) or die(mysql_error());
             }

             fclose($handle);
             echo "Successfully Imported";

         }
         else
         {
             echo "Invalid File";
         }   
    }

    ?>
    <h1>Import CSV file</h1>
    <form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
        Import File : <input type='file' name='sel_file' size='100'>
        <input type='submit' name='submit' value='submit'>
    </form>

Problem is with space in column name Company name you need to keep inside into backtick — Saty
– Saty, Commented Apr 18, 2016 at 12:08
This part INSERT into company(Company name,Country,Currency) do you want it like ....(Company, name.... OR ....(`Company name`.... — Sagar Guhe
– Sagar Guhe, Commented Apr 18, 2016 at 12:08
The field name specified in the table in "Company name" and it is one field — Vinayaka Singh
– Vinayaka Singh, Commented Apr 18, 2016 at 12:11
hahahaha @Pamblam I just did the same as you thought.. I just escaped it with `\` ;) — Sagar Guhe
– Sagar Guhe, Commented Apr 18, 2016 at 12:30

I wrestled a bear once. · Accepted Answer · 2016-04-18 12:16:21Z

1

If the column company name is two words you have to wrap it in backticks.

If there are quotes in the data (or god forbid, some kind of SQL injection) you're basically doomed.

mysql_* is deprecated and not safe to use. You should look into PDO or MySQLi prepared statements.

And, if this is a one-time upload consider using phpMyAdmin to import your CSV.

answered Apr 18, 2016 at 12:16

I wrestled a bear once.

23.5k20 gold badges74 silver badges122 bronze badges

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

Collectives™ on Stack Overflow

Importing csv file into mysql using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related