Load text file into mysql database and add timestamp to each row

Question

I have a text file that has serial numbers on each line such as:

7656393835734594
8457457435983945
3489534689856950
4596859684560498

I want to add this text file into a mysql table using PHP. My table looks like this:

serials table
    id           bigint(20) auto_increment
    serial       varchar(16)    
    timestamp    timestamp

How can I add each line from the text file into the serial column and also add the current timestamp to each row?

Here is what I have already tried:

$file = "serials.txt";
$conn = mysqli_connect('localhost','root','root');
mysqli_select_db($conn, 'myDB');
mysqli_query($conn, "LOAD DATA INFILE '".$file."' INTO TABLE serials");

This code does not do anything let alone add the data and the timestamp.

GMB · Accepted Answer · 2020-05-26 23:55:11Z

2

I think that the simplest approach is to define a default value for the timestamp column:

create table serials (
    id bigint primary key auto_increment,
    serial varchar(16),
    ts timestamp default current_timestamp
)

Note that I renamed to timestamp column to some name that does not conflict with a language keyword.

You can then use the load data syntax. An important thing is that you need to provide a column list, since not all table columns are given for insert:

load data infile 'myfile.txt' into table serials(serial)

On the other hand, if you can't modify the table definition for some reason, an alternative is to use the set clause to provide the derived value:

load data infile 'myfile.txt' into table serials(serial)
set ts = current_timestamp

answered May 26, 2020 at 23:55

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

danblack · Accepted Answer · 2020-05-27 00:00:25Z

0

if the file is local to the connection script, it should be LOAD DATA INFILE LOCAL and before the connection:

mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);

Check the server settings has:

local_infile=ON

Alternately if you want the server to load the file secure_file_priv needs to be the path where the file is.

answered May 27, 2020 at 0:00

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

Load text file into mysql database and add timestamp to each row

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