How to construct multiple row insert query in mysqli statement?

Question

PHP

Table structure: id (key), user_id, service_id, sdate, svalue
For each user_id, for each service_id there can be several values ​​(records) that differ by date, but theoretically can be added on the same day.
At the same time, it is necessary to distinguish whether there is already a similar entry for user_id and service_id or not, and when inserting it, update or add it accordingly.

$sql = "INSERT INTO customers VALUES ";
/*
Тут я теоретически могу расставить где надо "id", где не надо - "NULL" и сформировать строки в виде: 
$sql .= "(?,?,?,?,?),";
или
$sql .= "(NULL,?,?,?,?),";
*/
$sql .= "ON DUPLICATE KEY UPDATE sdate=VALUES(sdate), svalue=VALUES(svalue)";
$query = $mysqli -> prepare($sql);
/* а дальше начинается проблема с формированием списка переменных в bind_param*/
$query -> bind_param("",$variables);
$query -> execute();
$query -> close();

Would it be correct to do an INSERT multiple times (About 50 service_id and from 0 to infinity (average 1 but theoretically up to 10) rows each) with one prepared statement?

Answer 1

Yes, you can use the same query with different values, but with a large number of rows, performance can upset you.
But what if you create an array where you will write all the values? You will fill it simultaneously with the formation of the query string. For example, like this:

$sql   = 'INSERT INTO `customers` (`id`, `name`, `email`) VALUES ';
$binds = [];
$index = 0;
$count = count($customers);
foreach($customers as $customer) {
    if ($customer->id) {
        $sql .= '(?, ?, ?)';
        $binds[] = $customer->id;
    } else {
        $sql .= '(NULL, ?, ?)';
    }
    if (++$index != $count) {
        $sql .= ', ';
    }
    $binds[] = $customer->name;
    $binds[] = $customer->email;
}
$sql .= ' ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `email` = VALUES(`email`)';
$query = $mysqli->prepare($sql);
foreach($binds as $bind) {
    $query->bind_param("s", $bind);
}