0

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"; }
M. Eriksson
  • 13,450
  • 4
  • 29
  • 40
VectorXY
  • 349
  • 1
  • 3
  • 12
  • 1
    I think you need to end each of your queries with a semi-colon. I don't mean the semi-colon you already have at the end of your lines of PHP code, but *inside* the string. Also, looks like you're wide open to SQL injection attacks. – Greg Schmidt Jan 21 '21 at 23:54
  • @GregSchmidt I'm still trying to grasp the basics here :D Security will come. Anyway.. adding semicolons did the trick. Thanks for your answer. – VectorXY Jan 22 '21 at 00:05
  • 2
    Why do you need to use `mysqli_multi_query()`? You could just make several calls to `mysqli_query()`. `mysqli_multi_query()` makes it difficult to keep track of result sets and errors. I note that your last call appears to be missing a procedure name. FWIW, you would do better to write one update procedure that handles all these different changes rather than having several and making multiple calls for what is essentially one update. – Tangentially Perpendicular Jan 22 '21 at 00:09
  • You need to end each query with `;` so the server knows where one query ends and another begins. But I agree with the above comment, there's nothing useful that would be gained from using a multi query over multiple requests. – M. Eriksson Jan 22 '21 at 00:18
  • 3
    I strongly recommend against using `mysqli_multi_query()`. I've never seen any legitimat use of it, and it just makes things harder. You also can't use prepared statements, so you open up to SQL injection. – Barmar Jan 22 '21 at 00:28
  • 1
    It does seem likely that you can do everything you are doing with all those procedures with one query... – Nick Jan 22 '21 at 00:28
  • 1
    Agreed...it appears like this is probably a job for just one single INSERT query rather than a whole set of procedures. What exactly do they all do? – ADyson Jan 22 '21 at 01:00

1 Answers1

0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • One place I worked had a large and complex database, and a whole team to manage it. They provided a set of stored procedures for the rest of us programmers to use when accessing the database. We were specifically prohibited from using SQL primitives at any time. The idea was to protect database integrity against rogue PHP code. It worked pretty well for that, but we sometimes had to be creative with the SPs we used for some jobs since getting a new SP written could take months. – Tangentially Perpendicular Jan 22 '21 at 02:02