CODEWITHSUNDEEP
phpmysqlcsv
mohit
mohit

Reputation: 49

save file data in mysql database in php

i have a csv file contain 118350 lines,i want to save each line in my database table,i have read entire file in a array and parse every line for some modification and i have started to save file content in my database,i have a php program but the problem is that which save only 927 lines in one time so i have to run my php script again and again to save the further data in database.

my php code is ---

<?php
    $connection = mysql_connect('localhost', 'user', 'password') or die(mysql_error($connection));
    $select_db = mysql_select_db('dbname', $connection) or die(mysql_error($connection));

    $file = file('ip-to-country.csv');
    $data = str_replace("\"", "", $file, $j); //for removing ' " ' charactor from string
    $i = 0;
    for ($i = 0; $i < count($data); $i++) {
        $content = explode(',', $data[$i]);
        $ins = "insert into iplocation (startiprang,endiprang,concode,concode3,country) values ($content[0],$content[1],'$content[2]','$content[3]','$content[4]')";
        $result = mysql_query($ins, $connection);
    }
    echo "done";
?>

is there any function which can store all file data in an array without limitation. myfile size is approx 6 MB. thankx in advance.......i hope you understand what i want... Thanks in advance.

Upvotes: 0

Views: 2007

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56397

If you're using this csv

http://ip-to-country.webhosting.info/node/view/6

you can do in this way:

create table iplocation (
id int not null auto_increment primary key,
startiprang int unsigned,
endiprang int unsigned,
concode varchar(50),
concode3 varchar(50),
country varchar(150)
) engine = myisam;

load data infile 'c:/ip-to-country.csv'
into table iplocation 
fields terminated by ',"'
(@startiprang,@endiprang,@concode,@concode3,@country)
set 
startiprang = replace(@startiprang,'"',''),
endiprang = replace(@endiprang,'"',''),
concode = replace(@concode,'"',''),
concode3 = replace(@concode3,'"',''),
country = replace(@country,'"','')

Upvotes: 1

WASD42
WASD42

Reputation: 2389

Actually this not a PHP problem. It can be easily solved using LOAD DATA command in mySQL. It'll look like:

LOAD DATA INFILE LOCAL '\var\tmp\test.csv' INTO TABLE sometable FIELD TERMINATED BY ';' LINES TERMINATED BY '\n' (column1, column2);

But if you really want to solve it with PHP then just create SQL file with INSERT commands in it for each CSV line and execute it with mySQL client.

Upvotes: 0

Related Questions