Changing multiple records at once in mysql+php?

Question

PHP

I am making a tester, there is a table with questions:

CREATE TABLE `questions` (
  `id` int(11) NOT NULL,
  `question` varchar(255) NOT NULL,
  `ans1` varchar(255) NOT NULL,
  `ans2` varchar(255) NOT NULL,
  `ans3` varchar(255) NOT NULL,
  `ans4` varchar(255) NOT NULL,
  `ans5` varchar(255) NOT NULL,
  `ans` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I display them on a separate page for editing, in input and textarea
How can I save changes by clicking on submit?
I tried to do according to the article
I collected this code

<?php
header('Content-Type: text/html; charset=utf-8');
if(isset($_POST['item_id'], $_POST['item_question'])){
    require("../includes/connection.php"); // Подключение к БД
    $item_id = $_POST['item_id'];
    $item_question = $_POST['item_question'];
    $item_ans1 = $_POST['item_ans1'];
    $item_ans2 = $_POST['item_ans2'];
    $item_ans3 = $_POST['item_ans3'];
    $item_ans4 = $_POST['item_ans4'];
    $item_ans5 = $_POST['item_ans5'];
    $item_ans = $_POST['item_ans'];
    // Объединяем массивы
    $allData = array_map(null,$item_id, $item_question, $item_ans1, $item_ans2, $item_ans3, $item_ans4, $item_ans5, $item_ans);
    // Генерируем запрос
    $query = "INSERT INTO `questions` (`id`,`question`,`ans1`,`ans2`,`ans3`,`ans4`,`ans5`,`ans`) VALUES ";
    $params = array();
    foreach($allData as $val){
      $params[] = '('.(int)$val[0] .','.(string)$val[1].','.(string)$val[2].','.(string)$val[3].','.(string)$val[4].','.(string)$val[5].','.(string)$val[6].','.(string)$val[7].')';
    }
    $query .= implode(',', $params) . " ON DUPLICATE KEY UPDATE `question`,`ans1`,`ans2`,`ans3`,`ans4`,`ans5`,`ans` = VALUES(`question`,`ans1`,`ans2`,`ans3`,`ans4`,`ans5`,`ans`)";
    echo $query; // можно вывести его на экран для проверки
    // Выполняем запрос
    $res = mysql_query($query, $con);
    echo (mysql_affected_rows($con) > 0 ? 'Данные успешно обновлены!' : 'Не произведено ни одной замены'),
      '<br><a href="./">Вернутся</a> на страницу редактирования.';
  
}
?>

nothing came out

No substitutions made

Does anyone know a solution or another way

Answer 1

CREATE TABLE `questions` (
`id` int(11) NOT NULL,
`question` varchar(255) NOT NULL,
`ans1` varchar(255) NOT NULL,
`ans2` varchar(255) NOT NULL,
`ans3` varchar (255) NOT NULL,
`ans4` varchar(255) NOT NULL,
`ans5` varchar(255) NOT NULL,
`ans` int(11) NOT NULL,
`cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

do not make such tables in practice, only in the sandbox
this method is considered obsolete and not safe
use mysqli or PDO, there are enough articles about them
to insert data use prepared queries, for example

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* выполнение подготовленного запроса */
mysqli_stmt_execute($stmt);

printf("%d строк вставлено.\n", mysqli_stmt_affected_rows($stmt));

/* закрываем запрос */
mysqli_stmt_close($stmt);

php.net/manual/ru/mysqli-stmt.bind-param.php

Answer 2

unfortunately, the whole project is already written by this method