prepared statement vs mysqli_query

[budimir]

Can someone explain why prepared statement in example below is failing while mysqli_query is working without any problem with exactly same set of data? I tried everything I could figure out and can't see the reason why it is happening.

//Now we get costs in% and absolute
        $number = 0;
        $numberText = "";
        $numberName = "";
        $total_costs = 0;
        $total_costs_abs = 0;
        foreach($cost_array as $key => $value){
          //Here we select only costs with % so we could calculate percentage
          if(strcmp($value["cost_measure"],"Percent") === 0){
            //$number .= getPercentOfNumber($local,$value["cost_amount"])."<br>";
            $number = getPercentOfNumber($local,$value["cost_amount"]);
            //$numberText.= $value["cost_amount"]." %<br>";
            $numberText = $value["cost_amount"];
            //$numberName .= "cost_name ".$value["cost_name"]."<br>";
            $numberName = $value["cost_name"];
            //Total value of all percentage costs
            $total_costs += $number;
            
            //Insert calculated costs in DB
//Not working
            if ($stmt = $conn_mysqli -> prepare("INSERT INTO calculations_cost (calculation_id, costs_id, user_id, cost_time, original_number, cost_amount, cost_measure, calculated_cost, cost_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
                $stmt -> bind_param("iiissssss", $calculation_id, $costs_id, $user_id, $create_time, $local, $numberText, $measure, $number, $numberName);
                $stmt -> execute();
                $stmt -> close();
            }
//Working
            $sql = "INSERT INTO calculations_cost (calculation_id, costs_id, user_id, cost_time, original_number, cost_amount, cost_measure, calculated_cost, cost_name) VALUES ('$calculation_id', '$costs_id', '$user_id', '$create_time', '$local', '$numberText', '".$value['cost_measure']."', '$number', '$numberName')";
            mysqli_query($mysqli,$sql) or die (mysqli_error($mysqli));
            echo $sql."<br>";
            //echo "Total costs: $total_costs<br>";
            }
         }

[Jacques1]

What is failing? What is the exact error message? If there is none, fix your PHP error reporting and also enable mysqli errors:

// this comes before the mysqli connection code

// make mysqli throw an exception whenever it encounters a problem
$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

My guess: Your prepared statement uses $conn_mysqli, but your query uses $mysqli. Obviously one of them is wrong.

[budimir]

Hey Guru,

 

Thanks for you're suggestion. This is error I get:

 

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Commands out of sync; you can't run this command now' in C:\wamp\www\pma\calculation\custom_based_method.php on line 159

 

What does that mean and how to trace it?

Edited February 7, 2017 by budimir

[Jacques1]

Have you heard of this cool new search engine called Google search? You should try it.

 

Actually, you can just open the PHP manual:

Note:

When using mysqli_stmt_execute(), the mysqli_stmt_fetch() function must be used to fetch
the data prior to performing any additional queries.

So, is there a query before the above statement where you haven't fetched/freed the result set yet?

[requinix]

It means somewhere you did an unbuffered query and didn't read all of the rows and/or didn't ->close the statement. It was probably earlier in your code.

 

Switch to buffered queries, which won't have that problem and are generally better for everyone involved.

 

[edit] 2 minutes.

Edited February 7, 2017 by requinix

[budimir]

Thank you for the help. I found a problem.

 

Thank you very much.