mysqli multi_query only works in XAMPP?

Question

I am working on two platforms. One is a Windows 10 computer loaded with XAMPP, and the other is a Mac OS X El Capitan loaded with the default Apache, PHP and MySQL. The goal is to import an .sql file downloaded from a remote server to the local server. The codes are as follows:

$dbhost = 'localhost';
$dbuser = 'someuser';
$dbpass = 'somepassword';
$dbname = 'somedb';

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_error) { 
    die('Error : ('. $mysqli->connect_errno .') '.  $mysqli->connect_error); 
}

$filename = "somesql.sql";
$sqlSource = file_get_contents($filename);
$cmp = $mysqli->multi_query($sqlSource);
if($cmp){ $message = 'Import Successful'; }
else{ $message = 'Import Unsuccessful | '.$mysqli->error; }

However, the above codes work in XAMPP on the Windows 10 computer but not on the Mac OS X. In the Mac OS X, it somehow only executes the CREATE TABLE tablename statement from the .sql file but not the INSERT INTO tablename VALUES bits. It does not yield any errors.

It also does not work in Windows with Apache, PHP and MySQL separately installed.

Is there something I missed or is there a configuration that I need to tweak to make it work on the Mac OS X?

Edit: Below is the generated sql file, somesql.sql:

CREATE TABLE `sometable` (
`id` smallint(9) unsigned NOT NULL AUTO_INCREMENT,
`var_1` varchar(200) DEFAULT NULL,
`var_2` varchar(200) DEFAULT NULL,
`var_3` varchar(200) DEFAULT NULL,
`int_1` int(100) DEFAULT NULL,
`int_2` int(100) DEFAULT NULL,
`int_3` int(100) DEFAULT NULL,
`text_1` text COMMENT 'depot address',
`text_2` text,
`text_3` text,
`decimal_1` decimal(10,2) DEFAULT NULL,
`decimal_2` decimal(10,2) DEFAULT NULL,
`decimal_3` decimal(10,2) DEFAULT NULL,
`datetime_1` datetime DEFAULT NULL,
`datetime_2` datetime DEFAULT NULL,
`datetime_3` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `var_1` (`var_1`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;


INSERT INTO sometable VALUES
("2","BN","","","","","","Some details","","","","","","","",""),
("3","MK","","","","","","Some other details","","","","","","","",""),
("4","CH","","","","","","Some other other details","","","","","","","","");

@Saty I have edited the post to include the generated sql file. — ddz1507, Apr 11 '16 at 10:47

score 0 · Answer 1 · edited May 23 '17 at 12:31

Well it turned out that I had to tweak the code that was being used to export the sql file before it's being imported. Apparently, thanks to the coder who documented his work, the export script was actually copied from here.

Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName,  $tables=array("table1","table2","table3"), $backup_name="somesql.sql" );

function Export_Database($host,$user,$pass,$name,  $tables=false, $backup_name=false )
{
    $mysqli = new mysqli($host,$user,$pass,$name); 
    $mysqli->select_db($name); 
    $mysqli->query("SET NAMES 'utf8'");

    $queryTables    = $mysqli->query('SHOW TABLES'); 
    while($row = $queryTables->fetch_row()) 
    { 
        $target_tables[] = $row[0]; 
    }   
    if($tables !== false) 
    { 
        $target_tables = array_intersect( $target_tables, $tables); 
    }
    foreach($target_tables as $table)
    {
        $result         =   $mysqli->query('SELECT * FROM '.$table);  
        $fields_amount  =   $result->field_count;
        $rows_num       =   $mysqli->affected_rows; 
        $res            =   $mysqli->query('SHOW CREATE TABLE '.$table); 
        $TableMLine     =   $res->fetch_row();
        $content        =   (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";

        for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) 
        {
            while($row = $result->fetch_row())  
            { //when started (and every after 100 command cycle):
                if ($st_counter%100 == 0 || $st_counter == 0 )  
                {
                        $content .= "\nINSERT INTO ".$table." VALUES";
                }
                $content .= "\n(";
                for($j=0; $j<$fields_amount; $j++)  
                { 
                    $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); 
                    if (isset($row[$j]))
                    {

                        if($row[$j] === ''){ $content .= 'NULL'; }
                        else{ 
                            if(is_numeric($row[$j])){ $content .= $row[$j]; }
                            else{ $content .= '"'.$row[$j].'"'; }
                        }
                    }
                    else 
                    {   
                        $content .= '""';
                    }     
                    if ($j<($fields_amount-1))
                    {
                            $content.= ',';
                    }      
                }
                $content .=")";
                //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) 
                {   
                    $content .= ";";
                } 
                else 
                {
                    $content .= ",";
                } 
                $st_counter=$st_counter+1;
            }
        } $content .="\n\n\n";
    }
    //$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
    //$backup_name = $backup_name ? $backup_name : $name."-".time().".sql";
    $backup_name = $backup_name ? $backup_name : "somesql.sql";
    header('Content-Type: application/octet-stream');   
    header("Content-Transfer-Encoding: Binary"); 
    header("Content-disposition: attachment; filename=\"".$backup_name."\"");
    echo $content; exit;
}

So that the generated sql file will churn out something like this:

CREATE TABLE `sometable` (
`id` smallint(9) unsigned NOT NULL AUTO_INCREMENT,
`var_1` varchar(200) DEFAULT NULL,
`var_2` varchar(200) DEFAULT NULL,
`var_3` varchar(200) DEFAULT NULL,
`int_1` int(100) DEFAULT NULL,
`int_2` int(100) DEFAULT NULL,
`int_3` int(100) DEFAULT NULL,
`text_1` text COMMENT 'depot address',
`text_2` text,
`text_3` text,
`decimal_1` decimal(10,2) DEFAULT NULL,
`decimal_2` decimal(10,2) DEFAULT NULL,
`decimal_3` decimal(10,2) DEFAULT NULL,
`datetime_1` datetime DEFAULT NULL,
`datetime_2` datetime DEFAULT NULL,
`datetime_3` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `var_1` (`var_1`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

INSERT INTO sometable VALUES
(2,"BN",NULL,NULL,NULL,NULL,NULL,"Some details",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(3,"MK",NULL,NULL,NULL,NULL,NULL,"Some other details",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(4,"CH",NULL,NULL,NULL,NULL,NULL,"Some other other details",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

The key part is this line in the function above:

if($row[$j] === ''){ $content .= 'NULL'; }
else{ 
    if(is_numeric($row[$j])){ $content .= $row[$j]; }
    else{ $content .= '"'.$row[$j].'"'; }
}

Now I am able to import the sql file in both systems without errors.

mysqli multi_query only works in XAMPP?

1 Answers1