1

I have an insert sql query in a php script. The query executes in phpmyadmin, but when i place it in the php script,it does not execute. The error I get is "Commands out of sync; you can't run this command now". Any inputs on what may be wrong?

The following is the php code:

<?php
$request_method = $_SERVER['REQUEST_METHOD'];
 if ($request_method == 'POST') {

//retrieve request body
$requestBody = file_get_contents('php://input');

//write to stats file
$fp = fopen($_SERVER['DOCUMENT_ROOT'] . '/stats/upload/resourceStats.json', 'a') or die("unable to open file\n");
fwrite($fp, $requestBody, strlen($requestBody));
fclose($fp);

//parsing config file
$monitoring_db_config = parse_ini_file("mySql/monitoringConfig.ini");
//Connect to DB    
$mysqli_con = mysqli_connect($monitoring_db_config['host'],$monitoring_db_config['username'],$monitoring_db_config['password']);
if (mysqli_connect_error()) {
    die('Connect Error\n');
}

//execute .sql file
$multi_query = file_get_contents("mySql/monitoring.sql");
if(mysqli_multi_query( $mysqli_con , $multi_query) == FALSE ){
    echo "multi query failed"."\n";
}

//Insert stats into table
$arr_stat = json_decode($requestBody, true);

$sql_query = "INSERT INTO server_resource_stats (server_id,time,resource,measure,unit) VALUES (1,100,'resource',100,'%');";
if (mysqli_query($mysqli_con,$sql_query) != TRUE) {
        echo "error inserting into table\n";
    }
//close DB connection    
$mysqli->close($mysqli_con);
}
?>

The .sql file is as follows:

/*Create database 'monitoring'*/
CREATE DATABASE IF NOT EXISTS monitoring;

/*Use database 'monitoring'*/
USE monitoring;

/*Create table 'server_resource_stats'*/
CREATE TABLE IF NOT EXISTS server_resource_stats(id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,server_id INT(3) NOT NULL,time INT(12) NOT NULL,resource VARCHAR(20) NOT NULL, measure FLOAT(12,4) NOT NULL, unit VARCHAR(10) NOT NULL);
Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
user4460175
  • 31
  • 5
  • Instead of echoing out a text string echo out the [actual error](http://php.net/manual/en/mysqli.error.php). – Jay Blanchard Jan 26 '15 at 16:09
  • The error it gives is : "Commands out of sync; you can't run this command now" – user4460175 Jan 26 '15 at 16:18
  • Have a look here : http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now –  Jan 26 '15 at 16:30
  • I did see that. He uses mysqli_next_result. I not sure if I am supposed to store the result of the queries in d .sql file or not. And i am supposed to , then how do i use that stored value? – user4460175 Jan 26 '15 at 16:38
  • are you connected to the DB? :) it may sound like a stupid question but that was my problem – Robert Sinclair May 17 '17 at 18:59

1 Answers1

0

Code looks fine without actually putting on my test server, try printing your query from within your script this will check your getting the data from your file correctly you can then copy and paste the printed query into phpmyadmin or to check that it works.

andrew hutchings
  • 343
  • 1
  • 5
  • 18
  • I did that. It is fetching from the file correctly. Also working fine in phpmyadmin. It gives error "Commands out of sync; you can't run this command now". What i don't understand is why are the commands out of sync? – user4460175 Jan 26 '15 at 16:24
  • check here you cant run multiple querys at that level ie all within the mysqli multi queries function instead separate your queries. you can put them in an array and loop them. http://php.net/manual/en/function.maxdb-multi-query.php – andrew hutchings Jan 26 '15 at 16:32
  • But if i remove the insert query from my php script and execute only the queries in the .sql file as it is , all the queries get executed. The DB and the table are created. – user4460175 Jan 26 '15 at 16:35