How to Insert Large Files in MySQL Db Using PHP

How can I insert large files in MySQL db using PHP?

As far as I know it's generally quicker and better practice not to store the file in the db as it will get massive very quickly and slow it down. It's best to make a way of storing the file in a directory and then just store the location of the file in the db.

We do it for images/pdfs/mpegs etc in the CMS we have at work by creating a folder for the file named from the url-safe filename and storing the folder name in the db. It's easy just to write out the url of it in the presentation layer then.

insert large data from txt to mysql

I don't understand why you don't just use the "load data infile" function that mysql provides if the file is already on your server.

LOAD DATA INFILE

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

As for uniqueness, why don't you just add the UNIQUE constraint to your mysql table? That way you don't need to check for unique constraints?

I haven't tried this myself but I would try do it like that.

Importing larger SQL files into MySQL

You can import large files this command line way:

mysql -h yourhostname -u username -p databasename < yoursqlfile.sql

Best way of importing a large data file to MysQL

I wrote my own dump script, based on the same lines as BigDump. It takes three command line argumens:

Fully qualified file name which is to be dumped
Table in which to be dumped
After how many rows are loaded should the transaction be commited.

<?php
    function GetCommaSeparatedValues($row) //Creates a comma separated list of all the column - values in the row
    {
        $str = '';
        $str =  '\'' . addslashes(substr($row,0,12)) . '\',' . //addslashes() escapes special characters with a '\' 
                '\'' . addslashes(substr($row,12,12)) . '\',' .
                '\'' . addslashes(substr($row,24,2)) . '\',' .
                '\'' . addslashes(substr($row,26,24)) . '\',' .
                '\'' . addslashes(substr($row,50,05)) . '\'';
        return str;
    }

    function print_error($err, $linenumber, $pk)
    {
        echo $err . ' at line number: ' . $linenumber . PHP_EOL;
    }

    ini_set('display_errors',1); 
    ini_set('display_startup_errors',1);
    error_reporting(E_ERROR); //Report only those errors that are fatal runtime errors
    ini_set('memory_limit', '-1'); //Allow script to use maximum available memory
    $query="";
    $totalqueries=$argv[3]; //Commit after loading how many lines
    $linenumber=0;
    $currenttotal=0;
    $handle = fopen($argv[1], "r"); //File to dump in database
    $tablename = $argv[2]; //table name of the db in which to dump file

    $con=mysqli_connect("db1","root","N3@k83@rd","pov2013p_pov");

    $lno = 0;
    $tempquery = 'SET @@global.max_allowed_packet = ' . 1 * 1024 * 1024 * 1024;
    mysqli_query($con,$tempquery);
    mysqli_autocommit($con,FALSE);
    if ($handle)
    {
            echo `date` . 'Importing file: ' . $argv[1] . PHP_EOL ;
            echo 'Commit after every '. $totalqueries . ' lines. ' . PHP_EOL;
            echo 'Dumping into table '. $tablename . PHP_EOL . PHP_EOL;

            while (($dumpline = fgets($handle)) !== false)
            {
                                            $lno++;
                    if($currenttotal == $totalqueries)
                    {
                            echo `date` . 'Commiting at line number: ' . $linenumber . PHP_EOL . PHP_EOL;
                                                            mysqli_commit($con);
                            $currenttotal=0;
                            if($temp)
                            {
                                    while(mysqli_next_result($con))
                                    {
                                            if($result = mysqli_store_result($con))
                                            {
                                                    while($r = mysqli_fetch_row($result))
                                                    {
                                                            echo('*');
                                                    }
                                            }
                                    }
                            }

                    }
                    $commaseparated = commaseparatedvalues($dumpline);
                    $query = 'INSERT INTO pov2013p_pov.'.$tablename.' VALUES ('. $commaseparated .');';

                    mysqli_query($con,$query) or print_error(mysqli_error($con),$lno,substr($dumpline,0,12));
                    $currenttotal++;
                    $linenumber++;

            }
            if($query!='')
            {
                    echo `date` . 'Commiting at line number: ' . $linenumber . PHP_EOL;
                    mysqli_commit($con);
            }

            echo `date` . 'Finished Import.' . PHP_EOL;

    }
    else
    {
            echo "Cannot open file";
    }
?>

How to insert large amount of data in a MySQL table without LOAD DATA INFILE?

Following steps solved this issue:

Change table engine from InnoDB to MyISAM
disable the keys
insert data
re-enable the keys

Import large file on MySQL DB

Here is the code you need, now prettified! =D

<?php

include('config.php');

$file = @fopen('country.txt', 'r');

if ($file)
{
    while (!feof($file))
    {
        $line = trim(fgets($file));
        $flag = mysql_query($line);

        if (isset($flag))
        {
            echo 'Insert Successfully<br />';
        }

        else
        {
            echo mysql_error() . '<br/>';
        }

        flush();
    }

    fclose($file);
}

echo '<br />End of File';

?>

Basically it's a less greedy version of your code, instead of opening the whole file in memory it reads and executes small chunks (one liners) of SQL statements.