Efficient MySQL File Import in PHP: Splitting Queries for Shared Hosting

In the realm of web development, the need to import large database files while using shared hosting providers commonly arises. Unfortunately, accessing MySQL via the command line may be restricted, necessitating a PHP-based solution for parsing and executing queries.

To address this challenge, a robust function known as SplitSQL() has been developed to reliably split a database file into individual queries without consuming excessive memory.

How it Works

SplitSQL() leverages a file-reading approach, iterating through the file line by line. It identifies queries by detecting the specified delimiter (; by default) at the end of a line. Once a complete query is assembled, it's executed immediately using mysql_query().

Key Features

• Memory-efficient: By avoiding the need to load the entire file into memory, SplitSQL() is suitable for handling large files without performance issues.
• Fault-tolerance: If a query fails, error messages are displayed for debugging purposes.
• Output buffering: To prevent memory exhaustion, SplitSQL() flushes output after each query execution.

Usage Example

function SplitSQL($file, $delimiter = ';')
{
    set_time_limit(0);

    if (is_file($file) === true)
    {
        $file = fopen($file, 'r');

        if (is_resource($file) === true)
        {
            $query = array();

            while (feof($file) === false)
            {
                $query[] = fgets($file);

                if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1)
                {
                    $query = trim(implode('', $query));

                    if (mysql_query($query) === false)
                    {
                        echo '<h3>ERROR: ' . $query . '</h3>' . "\n";
                    }

                    else
                    {
                        echo '<h3>SUCCESS: ' . $query . '</h3>' . "\n";
                    }

                    while (ob_get_level() > 0)
                    {
                        ob_end_flush();
                    }

                    flush();
                }

                if (is_string($query) === true)
                {
                    $query = array();
                }
            }

            return fclose($file);
        }
    }

    return false;
}

// Test data
$file = '/path/to/db_dump.sql';
SplitSQL($file);