save file data in mysql database in php

Question

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.

Take a look at load data syntax of mysql. It's much faster and easier. If you post a sample (few rows) of your csv and possibly the output of show create table iplocation, I'll write you the right syntax. — Nicola Cossu
– Nicola Cossu, Commented Aug 13, 2011 at 8:37

Nicola Cossu · Accepted Answer · 2011-08-13 10:20:35Z

1

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,'"','')

answered Aug 13, 2011 at 10:20

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

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

4 Comments

WASD42 Over a year ago

Didn't you forget about LOCAL keyword?

Nicola Cossu Over a year ago

I don't know it's contest. He can adapt it easily to his needs. I tested my code before posting it on my own computer. :)

WASD42 Over a year ago

This will work only if you run mySQL server and client on same machine :]

Nicola Cossu Over a year ago

I know it. I already wrote that my code must be adapted to his case but I've done 99% of work for the user. Moreover load data local could be even disabled. He can always upload the csv and specify its path.

WASD42 · Accepted Answer · 2011-08-13 09:26:15Z

0

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.

answered Aug 13, 2011 at 9:26

WASD42

2,4096 gold badges29 silver badges41 bronze badges

Collectives™ on Stack Overflow

save file data in mysql database in php

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related