How to Import Large MySQL Files into a Shared Hosting Environment Using PHP?

Optimizing MySQL Database Import in PHP: Splitting Queries Reliably

In the realm of web development, scenarios arise where updating websites hosted on shared servers necessitates database updates. However, the inability to access MySQL externally poses a challenge. To address this, importing a MySQL file via PHP becomes necessary. This article explores an efficient solution to split large MySQL database files into individual queries and import them using PHP.

Problem Statement

The objective is to develop a reliable method for splitting a large database file into individual queries without opening the entire file simultaneously. Constraints include the unavailability of the MySQL command line function, LOAD DATA INFILE, phpMyAdmin, and mysqli_multi_query().

Solution: SplitSQL Function

The solution lies in a memory-friendly function called SplitSQL, which iterates through the file line by line and identifies query delimiter characters. It accumulates lines into an array until a delimiter is encountered, forming a complete query. The function then executes the query and provides feedback on its success or failure.

Implementation

<code class="php">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;
}</code>

Testing and Output

Upon testing with a large phpMyAdmin SQL dump, the SplitSQL function yielded successful results. Sample input and output are as follows:

Test Data

<code class="sql">CREATE TABLE IF NOT EXISTS "test" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT,
    "description" TEXT
);

BEGIN;
    INSERT INTO "test" ("name", "description")
    VALUES (";;;", "something for you mind; body; soul");
COMMIT;

UPDATE "test"
    SET "name" = "; "
    WHERE "id" = 1;</code>

Output

SUCCESS: CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT );
SUCCESS: BEGIN;
SUCCESS: INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul");
SUCCESS: COMMIT;
SUCCESS: UPDATE "test" SET "name" = "; " WHERE "id" = 1;

Conclusion

The SplitSQL function provides a reliable solution for splitting large MySQL database files into individual queries and executing them using PHP. It effectively addresses the aforementioned constraints and enables the automated import of database updates. This approach ensures seamless database maintenance in shared hosting environments.

The above is the detailed content of How to Import Large MySQL Files into a Shared Hosting Environment Using PHP?. For more information, please follow other related articles on the PHP Chinese website!