I'm trying to transfer the contents of multiple text files into a MySQL. These files are sequentially named 1.txt, 2.txt, 3.txt ... 13381.txt. Basically, each file is an HTML document without all the headers, meta, etc. that's been stripped away and only the content is left.
I've also made an table with a columns ID and CONTENT. ID is auto-incrementing. I'd like the contents of the files to go into the CONTENT column's cells like this:
1.txt -> row 1
2.txt -> row 2
3.txt -> row 3
etc.
Searching around only leads me to importing data from CSVs and other tabular forms of data. I could always just open each file and copy-paste from there into something like phpmyadmin but that seems like a tedious thing to do given that there's thousands of data files to go through.
I was wondering if there was an easy way to do this using PHP and some kind of recursive loop that gets file contents and then UPDATEs the database. I've experimented a bit but I've seem to come against a wall beyond my mediocre programming skills. Anyone care to help? Thanks in advance :)
Something like this using PDO...
<?php
$host = 'localhost'; $db = 'database-name'; $user = 'database-user-name'; $pw = 'database-password';
$conn = new PDO('mysql:host='.$host.';dbname='.$db.';charset=utf8', $user, $pw);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$i = 0;
do {
$i = $i+1;
$txt = file_get_contents("$i.txt");
try {
$sql = "INSERT INTO table (CONTENT) VALUES (:txt)";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':txt', $txt, PDO::PARAM_STR);
$stmt->execute();
} catch (PDOException $e) {
die("Could not complete the insert for ".$i.".txt " . $e->getMessage()); // this will tell you which of your text files caused the error
}
} while ($i <= 13381); // close for loop
echo('All Done!');
?>
Hope this helps.
<?php
try {
$instance = new PDO("mysql:host=127.0.0.1;dbname=nameofdatabase;charset=utf8", $user, $pass, array(PDO::ATTR_PERSISTENT => true));
$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $instance->prepare('INSERT INTO tablename SET CONTENT=:con');
for ($i = 1; $i <= 13381; $i++)
{
$con = file_get_contents("/path/to/files_dir/$i.txt");
$stmt->bindValue(':con', $con, PDO::PARAM_STR);
$stmt->execute();
}
}
catch(PDOException $ecx) {}
OR
you can use mysql LOAD DATA INFILE statement in php loop
