Jump to content
budimir

prepared statement vs mysqli_query

Recommended Posts

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>";
            }
         }

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by budimir

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by requinix
  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.