I want to insert about 50,000 mysql query for 'insert' in mysql db, for this i have 2 options,

1- Directly import the (.sql) file: Following error is occur " You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit. "

2- Use php code to insert these queries in form of different chunks from the (.sql) file. here is my code:

<?php

// Configure DB
include "config.php";

// Get file data
$file = file('country.txt'); 

// Set pointers & position variables
$position = 0;
$eof = 0; 

while ($eof < sizeof($file))
{
    for ($i = $position; $i < ($position + 2); $i++)
    {
        if ($i < sizeof($file))
        {
            $flag = mysql_query($file[$i]);

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

            else
            {
                echo mysql_error() . "<br>\n";
            }
        }

        else
        {
            echo "<br />End of File";
            break;
        }
    }

    $eof++;
}

?>

But memory size error is occur however i have extend memory limit from 128M to 256M or even 512M.

Then i think that if i could be able to load a limited rows from (.sql) file like 1000 at a time and execute mysql query then it may be import all records from file to db. But here i dont have any idea for how to handle file start location to end and how can i update the start and end location, so that it will not fetch the previously fetched rows from .sql file.

Comments

did you restart the services and try it again upon changing the memory_limit

Written by Treby

Have you "refer[ed] to documentation for ways to workaround this limit"?

Written by Boldewyn

Accepted Answer

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.

Written by Alix Axel
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki