Importing .csv file into MySql

Beginners - Learning PHP
#1

Hi, just registered to this forum. I am newbie in PHP. I want to write a php script which will do-

  1. upload csv file from local disk.
  2. import all data to a mysql table which will created by script.
  3. if there is header file in csv then then these will assigned as table column name.

Please explain in details with simple code.

#2

Um, no. Thats not the way it works. You post the code you attempted when you get stuck and we will help you, otherwise google is your friend right now. We dont do your homework for you.

#3

Hi, here is the code. I can not understand the query part. If my .csv file have ID, Name, Email, Subject how i put this into query? Please help me out.

[code]$columnArray = array();
$dataArray = array();
$firstRule = true;

while ($data = fgetcsv ($handle, 1000, “,”))
{
if($firstRule)
{
foreach($data as $columnName)
{
$columnArray[] = $columnName;
}

    $firstRule = false;
}
else
{
    $rule = array();
    for($i = 0; $i < count($data) ; $i++)
    {
        $rule[$columnArray[$i]] = $data[$i];
    }
    $dataArray[] = $rule;
}

}

foreach($dataArray as $data)
{
$query=“INSERT INTO Import_CopiersandPrinters_Canon_Raw_Data set Account Name
= '”.htmlspecialchars($data[‘Account Name’],ENT_QUOTES)."’,
Account Number = ‘".htmlspecialchars($data[1],ENT_QUOTES)."’,
Does Business As (DBA) = ‘".htmlspecialchars($data[‘Does Business As (DBA)’],ENT_QUOTES)."’,
Shipping City = ‘".htmlspecialchars($data[‘Shipping City’],ENT_QUOTES)."’,
…
…
…
file_name = ‘".$new_file_name."’,
file_date = ‘".date(‘Y-m-d H:i:s’)."’";

mysql_query($query) or die(mysql_error());

//Note that this would't reach when an error occured, because you used `or die`. You should use `or print` when you want to continue your code
if (mysql_error() <> "")
{             
    fputcsv($output, $data);
    $error_count++;
}
else
{
    $row_count = $row_count + 1;
}

$total = $total+1;

}[/code]

#4

You are using deprecated code that will not work at all in the latest version of Php. You need to use PDO with prepared statements. Dont even waste your time with this code.

Try here:

http://crewow.com/PHP-MySQL-Easy-CSV-Data-Insertion-in-Bootstrap-Using-PDO.php

#5

Hi i am try to learn PDO. I tried the following code. Is there any error. Cause when i run this in my browser i get Notice. is it normal?

[code]<?php

class Db_connect {
public $hostname = ‘localhost’;
public $username = ‘root’;
public $password = ‘’;
public $dbname = ‘db_student’;

public function __construct() {
    try {
        $conn = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
        echo 'Connected';
    } 
    catch (PDOException $e) {
        echo $e->getMessage();
    }
    
}

}
$obj = new Db_connect();

[/code]

Result:

Notice: Undefined variable: hostname in C:\xampp\htdocs\pdo_cruding\includes\db_connect.php on line 12

Notice: Undefined variable: dbname in C:\xampp\htdocs\pdo_cruding\includes\db_connect.php on line 12

Notice: Undefined variable: username in C:\xampp\htdocs\pdo_cruding\includes\db_connect.php on line 12

Notice: Undefined variable: password in C:\xampp\htdocs\pdo_cruding\includes\db_connect.php on line 12
Connected
#6

Let’s make this simple, download the PDO bump start database in my signature link and study that.

Sponsor our Newsletter | Privacy Policy | Terms of Service