How To Import Txt To Mysql Direct Or Through Php

I need to import my .txt to MySQL ` which has only one field like this I have tries this many way but failed :( Could you help me please If any have idea with php that is also

Solution 1:

You can use Mysql LOAD DATA LOCAL INFILE syntax

LOAD DATA LOCAL INFILE '/path/to/file.txt'INTOTABLE'table1'
    LINES TERMINATED BY'\n'

For this, make sure Mysql has access to /path/to/file.txt. Also the user who is executing the query must have FILE privilege.

With Pure PHP its easy. Read the file, build the query, execute it. You need to build the query so that you dont end up looping query which is slow.

$data = file("/path/to/file.txt", FILE_SKIP_EMPTY_LINES);

// make sure you have valid database connection prior to this point.// otherwise mysql_real_escape_string won't work$values = "('". implode("'), ('", array_map('mysql_real_escape_string', $data)). "')";

$query = "INSERT INTO `TABLE1` (`COLUMN1`) VALUES $values";

// Now just execute the query once.
mysql_query($query);

Solution 2:

Why not use some regex to basically split it up by new lines, ie.

$array= preg_split("/[\r\n]+/", file_get_contents("path/to/file.txt"));

and then do a foreach loop, ie:

foreach($arrayas$line){
   // insert into database
}

Then all you need to do is fill the line above where you insert it into the correct field in the database, line by line - but please sanitize each line, just so you don't inject the database with anything bad!

Solution 3:

Using csv import in phpMyAdmin

phpMyAdmin supports CSV files import, look at this article for more details (first result after: phpMyAdmin csv)

Using mysqlimport

Mysql provides CLI application, mysqlimport, example usage (again CSV):

mysqlimport --fields-optionally-enclosed-by='"' --fields-terminated-by=, \
    --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password \
    YOUR_DATABASE YOUR_TABLE.csv

Mysql LOAD DATA

Mysql itself (client, query) supports LOAD DATA INFILE command, example syntax:

LOAD DATA INFILE 'data.txt'INTOTABLE db2.my_table;

Build sql query with php (and insert manually)

You will parse text file in php and output will be one large INSERT statement (this will be useful when/if you cannot connect to mysql from the server where you're running the script:

// This will escape values correctlyfunctionescapeValues( &$val){
    // mysql_real_escape_string is not an option (remote server not accessible// in this case)return"'" . addslashes( $val) . "'"; 
}

$fp = fopen( $filename, 'r') ordie( 'Cannot open');
$result = array();
while( $row = fgets( $fp)){
    $values = explode( '/', $row);
    array_walk( $values, 'escapeValues');
    $results[] = '(' . implode( ', ', $values) . ')';
}

fclose( $fp);

if( !count( $results){
   die();
}

echo'INSERT INTO tableName (col1, col2, ... colN) VALUES ';
echo implode( ",\n", $results);
echo"\n";

Direct connection and direct import

This should be the best approach for you.

$conn = mysql_connect( ...) ordie(...);
mysql_select_db(...);

// Now we need to build small class which will allow us escape values properly// mysql_escape_string is DEPRECATED and mysql_real_escape_string// requires connection parameter// Ps: choose better nameclassCallbackHack{
    public$connection = null;
    publicfunctionescapeValue( &$val){
        $val = "'" . mysql_real_escape_string( $val, $this->connection) . "'";
    }
}
$hack = new CallbackHack();
$hack->connection = $conn;

$fp = fopen( $filename, 'r') ordie( 'Cannot open');
mysql_query( 'BEGIN TRANSACTION;'); // Increases insert performance for InnoDbwhile( $row = fgets( $fp)){
    $values = explode( '/', $row);
    array_walk( $values, array( $hack, 'escapeValue'));
    $sql = 'INSERT INTO tableName (col1, col2, ... colN) VALUES (' .
            implode( ', ', $values) . ');';
    mysql_query( $sql);
}

mysql_query( 'COMMIT;'); // Make sure it will run
fclose( $fp);

Solution 4:

<?php$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);


$file = fopen("welcome.txt", "r") orexit("Unable to open file!");
    //Output a line of the file until the end is reachedwhile(!feof($file))
      {

$data = mysql_real_excape_string(fgets($file));
mysql_query("INSERT INTO Persons (filedata)
VALUES ($data)");
//make sure above query is right according to your table structure
      }
    fclose($file);

mysql_close($con);
?>

Solution 5:

<?php$array = preg_split("[\r\n]+", file_get_contents("path/to/file.txt"))

foreach ($arrayas$line) {
mysql_query("INSERT INTO `dbname` (colmun1) VALUES ('$line')");
}
?>

Always sanitize too !