How to execute 2 sql queries Simeltaneously..?

PHP
1

I was trying insert values simultaneously into MySql database using mysqli_multi_query but record insertion failed…the sql querues are exceuting f9…but dont know about php

Below is my php code with db

      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')";
         //we// are using mysql_query function. it returns a resource on true else False on error
          
		  $sql= $sql_tableone.";".$sql_tabletwo;
		  
		  $result = mysqli_multi_query( $con,$sql);
		  
		 if(! $result )
			{
				echo "<script type=\"text/javascript\">
					alert(\"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); 
	}
	}
    $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); 
}
}
2

enable error reporting for mysqli!

mysqli_report(MYSQLI_REPORT_STRICT);
3

You don’t need to insert anything “simultaneously”.

Or, in other words, running two queries one by one with separate calls are the same “simultaneous”. So there is not a single reason to use multi|_query while there is a strong reason not to use it, as you ought to use prepared statements for your inserts.

Whatever idea about such a “simultaneous” insert you have is wrong. And your problem is either a completely imaginary one, or has another solution.

So rewrite your code to two separate prepared statements and express the reason why do you think you need “simultaneous” inserts

4

@colshrapnel…according to my requirement ive to insert simeltaneously and need to import few fields into another table…

5

You just misunderstood this requirement.

Either way, there is no such thing like “simultaneous insert” - even with multi query the inserts are consequent, not simultaneous.

So do what you told: make two separate prepared statements.

6

@colshrapnel…no sir…ya i know there is no concept as simultaneous execution in mysql…
ive prepared to seperate statment and trying to execute…below is my php code…

        $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')";
         //we// are using mysql_query function. it returns a resource on true else False on error
          
		  $sql= $sql_tableone.";".$sql_tabletwo;
		  
		  $result = mysqli_multi_query( $con,$sql);
		  
		 if(! $result )
			{
				echo "<script type=\"text/javascript\">
					alert(\"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); 
	}
	}
7

you must prepare your statements using prepare then bind variables using bind_param() and execute them using execute(), each query on its own.

8

@colshrapnel…there are 58 parameters for first query …shall i bind each one of those parameters…??

below is my query.

    $sql_tableone = $mysqli->prepare("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')");
9

So, this looks like the same question from your other post ( How to execute 2 sql queries Simultaneously..? ) so I’ll ask again - if you execute the two queries separately without using mysqli_multi_query(), do they work? If they do not work, which one fails? As they’re not running at the same time, I can’t see that there’s any difference in calling them separately or, for that matter, making one big query that does both operations in one go. Other than it’s a bit more difficult to get the results and figure out if either of them failed.

Yes, you would need to do that to use a prepared statement.

Is there any chance that the data coming in from your CSV file might have a problem? Can you show a few sample lines from that data?

This confuses me. You say the queries are executing, but the record insertion failed. Do you mean that if you build the query and run it from phpmyadmin it works correctly with the same values, but when you run the PHP code it does not? Or do you mean that you don’t get a failure message when you run the PHP code?

10

@droopsnoot …when i run the query individually they are executing f9… K ill bind each of those parameter and then execute…

11

So, when you run the two queries, separately, from this PHP code (but with two query calls rather than the single multi_query call), they both work correctly and insert the data into the two tables?

If that’s the case, and you are getting FALSE as a return on the multi_query, then it’s the first query that is failing, according to the documentation.

12

what i mean is…when i execute each query individually without using mysqli_multi_query from the web page i created its working perfectly without any error…but when i try tto execute using mysqli_multiquery… then it fails…and going to the following block…

            //queries

               $result = mysqli_multi_query( $con,$sql);
		  
		     if(! $result )
			{
				echo "<script type=\"text/javascript\">
					alert(\"Record Insertion Failed.\");
					</script>";
			
			}
13

Can you post the update code that does work?

14

Yes, of course you should bind all these parameters. And you better start doing it already.

15

@droopsnoot…ya mate…thanks mate i came to know which query was failing…2nd one it was failing…

16

As far as I know you cannot use prepared statements for multiple queries in a single mysqli_multi_query(). If you must use mysqli_multi_query() then use mysqli_real_escape_string() to escape each variable you put into the query, otherwise your code will not be safe.

This will work fine, however the question is why do you want to use mysqli_multi_query? Is it really necessary? It’s much easier to work with single queries, easier error handling, easier result extraction, ability to prepare statements, etc. Do you experience delays due to the database server being in a remote location so you want to minimize the connection round trips?

Normally, for inserts it is much better to do multiple single inserts in a transaction, which will speed things up and make your (complex) operation atomic. For non transactional database engines like myisam inserts are already pretty fast so there’s no problem either way.

17

It is not necessary.
It’s either a superstition or some sort of misunderstanding. It’s always the case with multi query.

So the question here is not the reason, which is certainly bogus, but the reluctance with which noobs always take the idea of separate execution.

18

Not wanting to hijack the topic or anything, but a related question based in the advice here (only in pdo).
Are we saying it’s bad to execute more than one query at a time?
It’s not something I’ve done before, but just recently did something like this:-

$sql = $db->prepare("SELECT somevalue FROM table WHERE id = :id; DELETE FROM table WHERE id = :id");
$sql->execute([':id' => $id]);

It just seemed more efficient or DRY that way.
Is there any advantage or disadvantage of that?

19

it doesn’t work.

example:

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE test (id INTEGER PRIMARY KEY, comment TEXT)');
$pdo->exec('INSERT INTO test (id, comment) VALUES (1, "foo")');
$sql = $pdo->prepare("SELECT comment FROM test WHERE id = :id; DELETE FROM test WHERE id = :id");
$sql->execute([':id' => 1]);

echo $pdo->query('SELECT comment FROM test WHERE id = 1')->fetchColumn(); // foo
20

There are the same disadvantages @Lemon_Juice already highlighted: more complex error handling, more complexresult extraction. Getting affected rows from your DELETE statement alone would take such amount of code that would negate all the seeming advantages.

Speaking of the DRY code it’s better to employ a function that accepts both a query and its parameters. So now you can compare two code snippets:

$value = $db->run("SELECT somevalue FROM table WHERE id = ?", [$id])->fetchColumn();
$db->run("DELETE FROM table WHERE id = ?",[$id]);
// vs.
$sql = $db->prepare("SELECT somevalue FROM table WHERE id = :id; DELETE FROM table WHERE id = :id");
$sql->execute([':id' => $id]);
$value = $sql->fetchColumn();
$stmt->nextRowset(); // need to get the error message from the delete query, if any

let’s look at these two code snippets and talk of readability. The first code block is distinct and meaningful. The second one you have to decipher first.
Yes, it probably would be more efficient and DRY to mix all your lunch courses in one bowl and gulp it at once. Though i doubt you’re after such an efficiency.

I’d say that multi-query is useful only in case you need to run a blob of predefined queries, sort of dump. While for the regular application code there are no advantages at all.

1 Like