PHP+MySQL连续插入数据报错：Commands out of sync; you can't run this command now & 读取Excel数据并批量插入到数据中

问题1：

需要从Excel中读取数据，插入到数据库中，而且是三个表，插入第一表成功，插入第二个表时报错：Commands out of sync; you can't run this command now。（一般在框架中不会出现该问题）

原因是：

当执行完query后，MySQL会保存有返回地查询结果集。我执行下次插入数据的时候未释放结果集导致

解决方式：

        do
        {
            if ($result = $db->store_result()){//从准备好的sql语句传递结果集
                $result->close(); //釋放結果我的人們
            }
        } while ($db->next_result());//取出下一个结果

2：从excel读取数据并批量插入到MySQL数据库中。上代码：

<?php 
header ( "Content-type:text/html;charset=utf-8" );  //统一输出编码为utf-8  
error_reporting(1);
require_once 'PHPExcel/PHPExcel.php';
 
/**对excel里的日期进行格式转化*/
/* function GetData($val){
    $jd = GregorianToJD(1, 1, 1970);
    $gregorian = JDToGregorian($jd+intval($val)-25569);
    return $gregorian;//显示格式为 “月/日/年”
} */
 
$filePath = 'user_info.xlsx';
 
$PHPExcel = new PHPExcel();
 
/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($filePath)){
    $PHPReader = new PHPExcel_Reader_Excel5();
    if(!$PHPReader->canRead($filePath)){
        echo 'no Excel';
        return ;
    }
}

$user_info = array();
$key_arr = array(
	'imei','meid','imei2','terminal_model','color','city','district','organize','organ_code','username','user_code','user_iphone'
);

$PHPExcel = $PHPReader->load($filePath);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet(0);
/**取得最大的列号*/
$allColumn = $currentSheet->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
/**从第二行开始输出，因为excel表中第一行为列名*/
for($currentRow = 2;$currentRow <= $allRow;$currentRow++){
	/**从第A列开始输出*/
	$single_info = array();
	$i = 0;
	for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){
		$val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数*/
		$single_info[$key_arr[$i]] = $val;
		$i++ ;
	}
	$user_info[] = $single_info;
}


/*-------------------------------------------获取excel数据--------------------------------------------------------------------*/
$host = '127.0.0.1';
$user_name = 'root';
$password = '';
$table_name = 'sys_post';


$db = new mysqli($host, $user_name, $password, $table_name);
$db->query("SET NAMES utf8");



$org_str = "";
$org_arr = array();

$postman_str = "";
$postman_arr = array();

$terminal_str = "";
$terminal_arr = array();



$old_code_arr = array();
$sql_select_all = "select * from post_org";
$exec_sql = $db->query($sql_select_all);

while ($select_all_result = mysqli_fetch_assoc($exec_sql)){
    $old_code_arr[] = $select_all_result['org_code'];
}

$old_terminal_arr = array();
$sql_select_all = "select * from post_terminal";
$exec_terminal_sql = $db->query($sql_select_all);

while ($select_all_result = mysqli_fetch_assoc($exec_terminal_sql)){
    $old_terminal_arr[] = $select_all_result['imei'];
}


$i = 0;
$org_code_arr = array();
foreach ($user_info as $key_info => $val_info){
	if(!in_array($val_info['organ_code'],$org_code_arr)){//防止插入重复数据
        if(!in_array($val_info['organ_code'],$old_code_arr)){
            $adcode = 0;
            if($val_info['district'] == "泗县"){
                $val_info['district'] = 3121;
                $adcode = 341324;
            }else if($val_info['district'] == "萧县"){
                $val_info['district'] = 3119;
                $adcode = 341322;
            }
            $org_arr[$i]['org_name'] = $val_info['organize'];
            $org_arr[$i]['org_type'] = 0;
            $org_arr[$i]['org_code'] = $val_info['organ_code'];
            $org_arr[$i]['org_type_original'] = 4;
            $org_arr[$i]['province_id'] = 2913;
            $org_arr[$i]['city_id'] = 3116;
            $org_arr[$i]['district_id'] = $val_info['district'];
            $org_arr[$i]['adcode'] = $adcode;
            $org_arr[$i]['gmt_create'] = date("Y-m-d H:i:s",time());
            $org_arr[$i]['gmt_modified'] = date("Y-m-d H:i:s",time());
            $org_arr[$i]['auto_timestamp'] = date("Y-m-d H:i:s",time());
        }

        if(!in_array($val_info['imei'],$old_terminal_arr)){
            $terminal_arr[$i]['imei'] = $val_info['imei'];
            $terminal_arr[$i]['imei2'] = $val_info['imei2'];
            $terminal_arr[$i]['meid'] = $val_info['meid'];
            $terminal_arr[$i]['terminal_model'] = $val_info['terminal_model'];
            $terminal_arr[$i]['color'] = $val_info['color'];
            $terminal_arr[$i]['remark'] = "安徽省".$val_info['city'].$val_info['district'].$val_info['organize'];
            //$terminal_arr[$i]['repair_time'] = null;
            $terminal_arr[$i]['terminal_status'] = 0;
            $terminal_arr[$i]['gmt_create'] = date("Y-m-d H:i:s",time());
            $terminal_arr[$i]['gmt_modified'] = date("Y-m-d H:i:s",time());
        }
        $org_code_arr[] = $val_info['organ_code'];
        $i ++;
	}
	
}

foreach ($org_arr as $key => $val) {
	$org_str .= insert_batch('post_org',array_keys($val),array_values($val));
}

foreach ($terminal_arr as $key => $val) {
	$terminal_str .= insert_batch('post_terminal',array_keys($val),array_values($val));
}


function insert_batch($table, $keys, $values)
{
	$data_str = "";
	foreach ($values as $key => $val) {
		if(is_numeric($val)){
			if($key+1 == count($values)){
				$data_str .= $val;
			}else{
				$data_str .= $val.', ';
			}
			
		}else{
			if($key+1 == count($values)){
				$data_str .= '"'.$val.'"';
			}else{
				$data_str .= '"'.$val.'", ';
			}
		}
	}
	$sql_insert = 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.$data_str.');';//implode(', ', $values)
	return $sql_insert;
}

//echo $org_str;;
if(!empty($org_str)){
    $result_org = $db->multi_query($org_str);
    if(!$result_org){
        echo("错误描述: " . mysqli_error($db));
    }else {
        var_dump($result_org);
        do
        {
            if ($result = $db->store_result()){//从准备好的sql语句传递结果集
                $result->close(); //釋放結果我的人們
            }
        } while ($db->next_result());//取出下一个结果
    }
}

//echo $terminal_str;
if(!empty($terminal_str)){
    $result_terminal = $db->multi_query($terminal_str);
    if(!$result_terminal){
        echo("错误描述: " . mysqli_error($db));
    }
}
var_dump($result_terminal);