Best Practice: Import MySQL File in PHP; Split Queries

Best practice: Import mySQL file in PHP; split queries

Here is a memory-friendly function that should be able to split a big file in individual queries without needing to open the whole file at once:

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;
}

I tested it on a big phpMyAdmin SQL dump and it worked just fine.

---

Some test data:

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;

And the respective 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;

How to run a set of SQL queries from a file, in PHP?

1. Don't use file() unless you really want the data line-by-line. file_get_contents() is better.
2. $query==file($file_name); I don't think you want to do a comparison here.
3. mysql_query will only ever execute a single query at once. You'll have to come up with some way of separating your queries in the file and run them one-by-one.

How to export and import mysql database with its data using php script?

http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx

either

$tableName  = 'mypet';
$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

or

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip >     $backupFile";
system($command);

Loading .sql files from within PHP

I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.

Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.

Import MySQL file in PHP

Load the file into a string and then pass it to mysqli::multi_query().

Standard disclaimers regarding untrusted sources for data/queries/executable-code et cetera apply.

php code to mysql database export

exec("mysqldump database -u username -p password  > output.sql");

http://ca3.php.net/manual/en/function.exec.php

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

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.

---

Related Topics