importing an .sql.gz file into mysql using php

Question

I am currently working on an script that will easily import an backup in 2 clicks, but i can't find any information on how to import and .gz file. and .sql file works great but if im going to decompress it i can just aswell just import it using phpmyadmin.

when i currently try to import the .gz file i just get one massive error of unreadable characters.

How can i make it so i can import an .gz file

The point here is that it is an WEB based importer

$userName = "root";
$password = "";
$dbName = "test";

    $conn = @new mysqli($serverName,$userName,$password,$dbName);

    if ($conn->connect_errno) {
    echo "Failed to connect to MySQL: " . $con->connect_errno;
    echo "<br/>Error: " . $con->connect_error;}
    

    $fileName = "189_dump.sql.gz";
    $temp = "";
    $lines = file($fileName);

    foreach($lines as $line){
        if(substr($line, 0, 2) == '--' || $line == '')
        continue;

        $temp .= $line;

        if(substr(trim($line), -1, 1) == ';'){
            $conn->query($temp) or print('Error performing query \'<strong>' . $temp . '\': '. '<br /><br />');
            $temp = "";
        }
    }
    echo "imported db (or not)";


?>

Obviously you should perform [decompression](https://www.php.net/manual/en/function.gzdecode.php) before reading file line by line. — Dmitry, Aug 27 '20 at 09:24
I understand that but i cant find any usefull information as of how to include and use it in my code, decrompressing is fun and all but if i can't fit it in or make it work its useless. — ThomSos, Aug 27 '20 at 09:28
Still the same problem, i just can't find any information as of how to include the decrompressed file into my code. thats the question i asked — ThomSos, Aug 27 '20 at 09:51
You probably should just use `shell_exec()` to gunzip and pipe to the mysql client. Don't try to duplicate the capability to run an SQL script in PHP. See my answer to https://stackoverflow.com/a/4028289/20860 — Bill Karwin, Aug 28 '20 at 07:05

ThomSos · Accepted Answer · 2020-08-28T07:29:50.290

This is how i fixed it:

$lines = gzfile($fileName);

<?php

class reader{
    
    public $dbName;
    public $fileName;
    
    public function import($dbName, $fileName){
        $serverName = "localhost";
        $userName = "root";
        $password = "";

        $conn = new mysqli($serverName,$userName,$password,$dbName);

        $temp = "";
        $lines = gzfile($fileName);

        foreach($lines as $key => $line ){
            if(substr($line, 0, 2) == '--' || $line == '') continue;

            $temp .= $line;

            if(substr(trim($line), -1, 1) == ';'){
                $conn->query($temp) or print('Error performing query \'<strong>' . $temp . '\': '. '<br /><br />');
                $temp = "";}
        
        }
        echo "<script>alert('Database Imported')</script>";
    }
}
$bestand = new reader();
#               dbname  |  file name
$bestand->import("test", "testdb.sql.gz");

?>

score 0 · Answer 2 · answered Dec 05 '20 at 18:36

0

If you have system() allowed you can:

<?php
system("gzip -dc < ".$file." | mysql -u $dbuser -p$dbpassword $dbname");

answered Dec 05 '20 at 18:36

Martin Zvarík

2,120
22
27

importing an .sql.gz file into mysql using php

2 Answers2