With the addition of all the lines figured out:

<?php // массив записей для добавления в БД: // $array_insert = array("('1','Anna','anna@mail.ru')", // "('2','Oleg','oleg@yandex.ru')", ... ); $query = mysql_query("INSERT INTO `t1` (`id`, `name`, `email`) VALUES ".implode(",", $array_insert), $db); ?>

Question:
$array_update - an array of records for updating the database.
How to write a similar request for UPDATE multiple records on selective id ? Given that the records can be updated about 10 pieces at a time ...
What should the values ​​of $array_update look like?

Update

I need to make an update as follows: I have a list of unique id and, corresponding to each id , the lines for the update (the data in the lines can be repeated). It is necessary to make an update of all lines in one request, where the id field corresponds to the current id from the list. I read about INSERT ... ON DUPLICATE KEY UPDATE... I do not yet understand how to apply to my situation.

  • @lommusic, some strange update, where you need to change a dozen records. The feeling that the structure of the database is not quite properly composed. Is the update data for all records the same? - Deonis
  • @Deonis Yes. In fact, this is a directory of ads, updated regularly ... - lommusic
  • Query, as usual, but condition either through OR, or IN: UPDATE table_name SET field = 'new_value' WHERE id IN (1,2,50, 100, N); If you talked about some kind of arrays, then in this case it could be an array of id-names: $ query = "UPDATE table_name SET field = 'new_value' WHERE id IN (" .implode (',', $ array_id). ")"; In addition, there is the INSERT ... ON DUPLICATE KEY UPDATE construct, which can also be used for updates, subject to the availability of unique / primary keys. But in your case, as I understood, this method will not work. - Deonis
  • @Deonis I need to update as follows: I have a list of unique id and, corresponding to each id, the lines for the update (the data in the lines can be repeated). It is necessary to make an update of all lines in one request, where the id field corresponds to the current id from the list. I read about INSERT ... ON DUPLICATE KEY UPDATE ... I do not yet understand how to apply to my situation. - lommusic

3 answers 3

If you want to use ON DUPLICATE KEY UPDATE , then here is an example of how to do this. Tested on tutorialspoint.com :

 MariaDB [(none)]> create database test_1; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use test_1 Database changed MariaDB [test_1]> create table t1 (id INT NOT NULL AUTO_INCREMENT, name varchar(100), email varchar(100), PRIMARY KEY (id) ); Query OK, 0 rows affected (0.03 sec) MariaDB [test_1]> insert into t1 (name, email) values ('user1', 'user1@gmail.com'), ('user2', 'user2@gmail.com'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test_1]> select * from t1; +----+-------+-----------------+ | id | name | email | +----+-------+-----------------+ | 1 | user1 | user1@gmail.com | | 2 | user2 | user2@gmail.com | +----+-------+-----------------+ 2 rows in set (0.01 sec) MariaDB [test_1]> INSERT INTO t1 (id, name, email) VALUES (1, 'user1', 'new_email_user1@gmail.com'), (2, 'new_name', 'user2@gmail.com') ON DUPLICATE KEY UPDATE name = VALUES(`name`), email = VALUES(`email`); Query OK, 4 rows affected (0.01 sec) Records: 2 Duplicates: 2 Warnings: 0 MariaDB [test_1]> select * from t1; +----+----------+---------------------------+ | id | name | email | +----+----------+---------------------------+ | 1 | user1 | new_email_user1@gmail.com | | 2 | new_name | user2@gmail.com | +----+----------+---------------------------+ 2 rows in set (0.00 sec)

    If the updated values ​​for each of the identifiers are different, unfortunately, updating them with one request will fail. For each of them, you will have to execute a separate UPDATE request.

      Sometimes you need to update the n-th number of rows in the MySql table. And insert not one value, but several, depending on the condition.

      The request will be

       UPDATE tbl_name SET fld = CASE WHEN pid=16 THEN '1' WHEN pid=17 THEN '2' WHEN pid=19 THEN '54' ELSE fld END;

      And if you don’t put ELSE at the end of an ELSE fld, then all values ​​that are not suitable by condition will be reset. Taken here