php - details on mysqli_multi_query()?

Question

I'm new to php. I'm trying to execute the queries below. I know I have to use mysqli_multi_query but all my tries so far have given me error. How do I execute these queries? I've read and tried to run other examples with failure.

<?php

include_once 'connect.php';

$user_values = json_encode($_POST['user_values']);//get a json object
$data = json_decode($user_values);



$sql = "";

if (isset($data->phone)) {
    $sql .= "CALL new_company_phone('$data->name', $data->phone)";
}
/*procedure that inserts value of "phone" to the corresponding "name" row */

if (isset($data->street)) {
    $sql .= "CALL new_company_street('$data->name', '$data->street')";
}

if (isset($data->street_num)) {
    $sql  .= "CALL new_company_street_num('$data->name' , $data->street_num)";
}

if(isset($data->city)){
    $sql .= "CALL new_company_city('$data->name', '$data->city')";
  }

  if(isset($data->country)){
    $sql .=  "CALL new_company_country('$data->name', '$data->country')";
  }

  /* execute multi query */
if (!mysqli_multi_query($con, $sql)) {
   echo "error"; }

question from:https://stackoverflow.com/questions/65837447/details-on-mysqli-multi-query

Answer 1

On the contrary! You should never use mysqli_multi_query()! If you think you found a good use for this function, then you should rethink your approach.

What you should be using are prepared statements with parameter binding. Each CALL() should be a separate statement. You can create a function to make it easier to call each one if you want.

For example:

function callSP(mysqli $mysqli, string $sql, array $params) {
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    $stmt->execute();
}

if (isset($data->phone)) {
    callSP($con, "CALL new_company_phone(?,?)", [$data->name, $data->phone]);
}

if (isset($data->street)) {
    callSP($con, "CALL new_company_street(?,?)", [$data->name, $data->street]);
}
// and so on...

It's difficult to say what your stored procedures actually do. You might have to tweak the way you call them from PHP depending on whether they return results, and whether they use cursors. In general, I would recommend avoiding stored procedures whenever possible; you can do the same in PHP directly.