CODEWITHSUNDEEP
phpmysql
Shams
Shams

Reputation: 65

insert in mysql table from .txt file

i have a situation where i have to insert in mysql table from a .txt file. and data is in format of below. both the data from left and right show seperatly insert into 2 columns. and the 3rd colum show be id

koijjh12 : 12
lkoiujjf : 12
uoytresf : 15
kjhgfd56 : 50

can we use php script to import that .txt file to the mysql database.

Upvotes: 2

Views: 7423

Answers (2)

Fluffeh
Fluffeh

Reputation: 33542

You certainly can do it via a PHP script, but the best way is to use the Mysql load data infile syntax like this:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n';

In you case, it looks like the fields are terminated by : so you can change it easily enough like this:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ' : '  LINES TERMINATED BY '\n';

You can run this command from any mysql query function by inserting it as the query. This will be much faster than anything you can do in PHP manually (as in reading the text into PHP, then inserting it into the database).

Edit: Be aware that if you use this in a string, you will need to escape the \ in the newline character with another \ like this: LINES TERMINATED BY '\\n';

Upvotes: 10

Dmytro Zarezenko
Dmytro Zarezenko

Reputation: 10686

Something like that:

<?php

$content = file_get_contents("a.txt");
$lines = explode("\n", $content);
foreach ($lines as $line) {
    $row = explode(":", $line);
    $query = "INSERT INTO tablename SET val1 = '" . trim($row[0]) . "', val2 = '" . trim($row[1]) . "'";
    mysql_query($query);
}

?>

Upvotes: 6

Related Questions