How to update or write data to mysql database through prepared queries and odku?

Question

PHP

I write data to the database like this:

<?php
require_once 'connection.php'; // подключаем скрипт

// Создание соединения
$conn = new mysqli($servername, $username, $password, $database);
// Проверка соединения
if ($conn->connect_error) {
   die("Ошибка подключения: " . $conn->connect_error);
}
        $up_date = date('Y-m-d\TH:i:s');

// Подготовить и связать
$stmt = $conn->prepare("INSERT INTO offer (affiliate, date, day_sales, description, image, item_id, price_total, rate, sales, sales_per_day, timer, title, totalPartners, totalReward, up_date, user_fullname, user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

on duplicate key update affiliate = '116', day_sales = '11000'");

// on duplicate key update VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");


$stmt->bind_param("isissiiiiissiissi", $affiliate, $date, $day_sales, $description, $image, $item_id, $price_total, $rate, $sales, $sales_per_day, $timer, $title, $totalPartners, $totalReward, $up_date, $user_fullname, $user_id);


// Установить параметры и выполнить
$affiliate = "1"; 
$date = "$up_date"; 
$day_sales = "1"; 
$description = "Феодал системс"; 
$image = "Федор"; 
$item_id = "1111"; 
$price_total = "1"; 
$rate = "1"; 
$sales = "1"; 
$sales_per_day = "1"; 
$timer = "$up_date"; 
$title = "Федор"; 
$totalPartners = "1"; 
$totalReward = "1"; 
$up_date = "$up_date"; 
$user_fullname = "Федор"; 
$user_id = "777";
$stmt->execute();
echo "Успешно созданы новые записи";

// Закрыть связь
$stmt->close();
// Закрыть подключение
$conn->close();

?>

A unique record id (AUTO_INCREMENT) and a unique item_id are written to the database .

I need to check in a loop for the presence of an entry in the database by its unique $item_id, and if it exists, then update it, if there is no entry, then add it.

Need to do it right away

insert ... on duplicate key update ...

without a preliminary lookup which inserts the record if it doesn't exist and modifies it if it does.

I tried to adapt my code to this answer , but it did not work out correctly to set up on duplicate key update VALUES.

on duplicate key update VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

does not work.

That would suit me too

on duplicate key update affiliate = '116', day_sales = '11000'");

but how to substitute the necessary data $affiliate, $day_sales, etc. into the values?

on duplicate key update affiliate = '$affiliate', day_sales = '$day_sales'");

does not work.

Help me please.

Answer 1

ON DUPLICATE KEY UPDATE `affilate` = VALUES(`affilate`), `day_sales` = VALUES(`day_sales`)

Answer 2

Rsa97 , if you need to update the entire line, and alternate with a comma, or is there a simplified version?

ON DUPLICATE KEY UPDATE `affilate` = VALUES(`affilate`), `day_sales` = VALUES(`day_sales`), `description` = VALUES(`description`) E.T.