How to execute 2 SQL queries one after the other using mysqli_multi_query in PHP

Question

I was trying insert values simultaneously into MySQL database using mysqli_multi_query but it's not executing and going to if part showing alert message stating Record Insertion Failed.

Below is my PHP code with query

while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) {
    $sql_tableone =  "INSERT into inverterlog (`id`,`timestamp`,`irradiance`,`ambienttemp`,`photovoltaictemp`,`pv1voltage`,`pv2voltage`,`pv3voltage`,`pv1current`,`pv2current`,`pv3current`,`pv1power`,`pv2power`,`pv3power`,`pv1energy`,`pv2energy`,`pv3energy`,`gridvoltagegv1`,`gridvoltagegv2`,`gridvoltagegv3`,`gridcurrentgc1`,`gridcurrentgc2`,`gridcurrentgc3`,`gridpowergp1`,`gridpowergp2`,`gridpowergp3`,`sumofapparentpower`,`gridpowertotal`,`gridenergyge1`,`gridenergyge2`,`gridenergyge3`,`socounter`,`gridcurrentdcgc1`,`gridcurrentdcgc2`,`gridcurrentdcgc3`,`gridresidualcurrent`,`gridfrequencymean`,`dcbusupper`,`dcbuslower`,`temppower`,`tempaux`,`tempctrl`,`temppower1`,`temppowerboost`,`apparentpowerap1`,`apparentpowerap2`,`apparentpowerap3`,`sovalue`,`reactivepowerrp1`,`reactivepowerrp2`,`reactivepowerrp3`,`opmode`,`latestevent`,`pla`,`reactivepowermode`,`overexcitedunderexcited`,`reactivepowerabs`,`inverter`)
                                                          values('','$newDate','$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]','$emapData[8]','$emapData[9]','$emapData[10]','$emapData[11]','$emapData[12]','$emapData[13]','$emapData[14]','$emapData[15]','$emapData[16]','$emapData[17]','$emapData[18]','$emapData[19]','$emapData[20]','$emapData[21]','$emapData[22]','$emapData[23]','$emapData[24]','$emapData[25]','$emapData[26]','$emapData[27]','$emapData[28]','$emapData[29]','$emapData[30]','$emapData[31]','$emapData[32]','$emapData[33]','$emapData[34]','$emapData[35]','$emapData[36]','$emapData[37]','$emapData[38]','$emapData[39]','$emapData[40]','$emapData[41]','$emapData[42]','$emapData[43]','$emapData[44]','$emapData[45]','$emapData[46]','$emapData[47]','$emapData[48]','$emapData[49]','$emapData[50]','$emapData[51]','$emapData[52]','$emapData[53]','$emapData[54]','$emapData[55]','$inverter')";
    $sql_tabletwo = "INSERT into data (`id`,`timestamp`,`gridpowertotal`,`inverter`) values ('','$newDate','$emapData[26]','$inverter')";
    $sql= $sql_tableone.";".$sql_tabletwo;
    $result = mysqli_multi_query( $con,$sql);
    if (! $result ) {
        echo "<script type=\"text/javascript\">
            alert(\"multi query Record Insertion Failed.\");
            </script>";
    }
    fclose($file);
}
//throws a message if data successfully imported to mysql database from excel file
echo "<script type=\"text/javascript\">
    alert(\"CSV File has been successfully Imported.\");
    window.location = \"four.php\"
/</script>";
//close of connection
mysqli_close($con); 
}
}

Answer 1

If the id column is auto-incremented in the tables, then omit the column (and the empty value) from your query. Alternatively, you can use NULL (not quoted) if you are going to mention the column in your query.

Many, many people struggle to find the errors with their mysqli_multi_query() code block because it is not set up to properly output affected rows and errors.

I recommend having a look at a general purpose code block that will help to isolate troublesome queries, and read this answer.

It also looks like you are while-looping mysqli_multi_query()'s two queries. For efficiency, I recommend building up the full array of queries, finishing the loop, then calling mysqli_multi_query() only once.

p.s. Do any of your insert values have quotes in them? Prepared statements would help with that issue. Use the code block from my link and check the error message.

UPDATE: Here is my spoon-fed answer (Of course, I didn't actually test it before posting):

// I assume $newdate is not user declared and considered safe.
// I am using NULL for your auto-incremented primary key `id`.
// If you want to be assured that each pair has an identical `id`, perhaps use LAST_INSERT_ID() on second query of pair.

// establish variables for future use
$inverterlog_sql="INSERT INTO `inverterlog` (`id`,`timestamp`,`irradiance`,`ambienttemp`,`photovoltaictemp`,`pv1voltage`,`pv2voltage`,`pv3voltage`,`pv1current`,`pv2current`,`pv3current`,`pv1power`,`pv2power`,`pv3power`,`pv1energy`,`pv2energy`,`pv3energy`,`gridvoltagegv1`,`gridvoltagegv2`,`gridvoltagegv3`,`gridcurrentgc1`,`gridcurrentgc2`,`gridcurrentgc3`,`gridpowergp1`,`gridpowergp2`,`gridpowergp3`,`sumofapparentpower`,`gridpowertotal`,`gridenergyge1`,`gridenergyge2`,`gridenergyge3`,`socounter`,`gridcurrentdcgc1`,`gridcurrentdcgc2`,`gridcurrentdcgc3`,`gridresidualcurrent`,`gridfrequencymean`,`dcbusupper`,`dcbuslower`,`temppower`,`tempaux`,`tempctrl`,`temppower1`,`temppowerboost`,`apparentpowerap1`,`apparentpowerap2`,`apparentpowerap3`,`sovalue`,`reactivepowerrp1`,`reactivepowerrp2`,`reactivepowerrp3`,`opmode`,`latestevent`,`pla`,`reactivepowermode`,`overexcitedunderexcited`,`reactivepowerabs`,`inverter`) VALUES (NULL,$newdate";
$data_sql="INSERT INTO `data` (`id`,`timestamp`,`gridpowertotal`,`inverter`) VALUES (NULL,'$newDate'";
$tally=0;
$x=0;

// build all queries
while(($emapData=fgetcsv($file,10000,","))!==false){
    ++$x;
    $sql[$x]=$inverterlog_sql;  // start first query of pair
    for($i=1; $i<56; ++$i){
        $sql[$x].=",'".mysqli_real_escape_string($con,$emapData[$i])."'";
    }
    $sql[$x].=",'".mysqli_real_escape_string($con,$inverter)."');";  // end first query of pair
    $sql[$x].="$data_sql,'".mysqli_real_escape_string($con,$emapData[26])."','".mysqli_real_escape_string($con,$inverter)."')";  // whole second query of pair
    fclose($file);
}

// run all queries
if(mysqli_multi_query($con,implode(';',$sql)){
    do{
        $tally+=mysqli_affected_rows($con);
    } while(mysqli_more_results($con) && mysqli_next_result($con));
}

// assess the outcome
if($error_mess=mysqli_error($con)){
    echo "<script type=\"text/javascript\">alert(\"Syntax Error: $error_mess\");</script>";
}elseif($tally!=$x*2){  // I don't expect this to be true for your case
    echo "<script type=\"text/javascript\">alert(\"Logic Error: Only $tally row",($tally!=1?"s":"")," inserted\");</script>";
}else{
    echo "<script type=\"text/javascript\">alert(\"CSV File has been successfully Imported.\"); window.location = \"four.php\"/</script>";
}
mysqli_close($con);