NotionCommotion

It seems that your example 1 and example 2 provide the same UX where the user updates a single profile property, but example 3 updates all profile properties. Correct? If so, you should only compare your example 1 and example 2. So, example 1 does URL: "/update/upic1", POST: {"value":"foo"}, and example 2 does URL:" /update", POST: {"item": "upic1", "value":"foo"} or some derivative of, right? Are you using a 3rd party controller? If so, follow its structure. If not, not really sure if one is better than the other, but consistency is definitely a good thing.

ThAnks kicked, and while I agree it a hack, you think other solutions are better?

Ah, Benanamen with his aversion to XY questions I am trying to efficiently perform mass update queries where some of the NOT NULL columns are not updated. See https://forums.phpfreaks.com/topic/302727-multiple-updates/ for background history. Thanks PS. As indicated by others responding to this post, a composite key has nothing to do with it.

I receive JSON data via a socket. Certain data is expected based on the database. I wish to confirm that the JSON is not providing more or less data that is expected. I can easily enough do the array_diff_key along with confirm index counts are equal solution, but just felt there was a really slick way which would be a good thing to learn.

Dah... I see several workarounds. Get rid of the NOT NULL constraint (which I don't wish to do). Get fancy with triggers or stored procedures (which I also don't with to do). Maybe the following? INSERT INTO points (id, accounts_id, value, units, name, type) VALUES (150,51,158.6,'W','bogus','bogus') ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=UNIX_TIMESTAMP(); 4. EDIT. Or probably this. An extra advantage is INSERTS are never really desired as I am doing this just as a multiple update hack. INSERT IGNORE INTO points (id, accounts_id, value, units) VALUES (150,51,158.6,'W') ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=UNIX_TIMESTAMP();

Thanks Barand, Yes, that would work, and is what I meant by comparing the counts. Still seems there would be a better way.

Thanks Benamamen and Kicken. But as seen by the SELECT statement I showed, the record with PK 150-51 already has a value for name.

Thanks CyberRobot. A requirement is has only the three indexes, and a single array_diff_key() will not work. I could then use a second array_diff_key() or compare the count, but expect one of the other countless array functions might be more suitable. var_dump(array_diff_key(['a'=>null,'b'=>null,'c'=>null], ['a'=>1,'b'=>0,'c'=>2])); //array(0) { } var_dump(array_diff_key(['a'=>null,'b'=>null], ['a'=>1,'b'=>0,'c'=>2])); //array(0) { } var_dump(array_diff_key(['a'=>null,'b'=>null,'c'=>null], ['a'=>1,'b'=>0])); //array(1) { ["c"]=> NULL }

Why does the INSERT ON DUPLICATE KEY UPDATE fail? I read that I also need to add a UNIQUE INDEX (but I have no idea why) and did so, but it still fails. Thanks mysql> EXPLAIN points; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | accounts_id | int(11) | NO | PRI | NULL | | | name | varchar(45) | NO | MUL | NULL | | | value | varchar(45) | YES | | NULL | | | value_old | varchar(45) | YES | | NULL | | | units | varchar(45) | YES | | NULL | | | timestamp | int(11) | YES | | NULL | | | type | char( | NO | MUL | NULL | | +-------------+-------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> SHOW INDEX FROM points; +--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | points | 0 | PRIMARY | 1 | id | A | 27 | NULL | NULL | | BTREE | | | | points | 0 | PRIMARY | 2 | accounts_id | A | 27 | NULL | NULL | | BTREE | | | | points | 0 | name_UNIQUE | 1 | name | A | 27 | NULL | NULL | | BTREE | | | | points | 0 | name_UNIQUE | 2 | accounts_id | A | 27 | NULL | NULL | | BTREE | | | | points | 0 | unique_points_pk | 1 | accounts_id | A | 2 | NULL | NULL | | BTREE | | | | points | 0 | unique_points_pk | 2 | id | A | 27 | NULL | NULL | | BTREE | | | | points | 0 | unique_points2_pk | 1 | id | A | 27 | NULL | NULL | | BTREE | | | | points | 0 | unique_points2_pk | 2 | accounts_id | A | 27 | NULL | NULL | | BTREE | | | | points | 1 | fk_points_accounts_id_idx | 1 | accounts_id | A | 2 | NULL | NULL | | BTREE | | | | points | 1 | fk_points_point_types1_idx | 1 | type | A | 6 | NULL | NULL | | BTREE | | | +--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM points WHERE id=150; +-----+-------------+------------------+--------+-----------+-------+-----------+------+ | id | accounts_id | name | value | value_old | units | timestamp | type | +-----+-------------+------------------+--------+-----------+-------+-----------+------+ | 150 | 51 | sample test one | 102.67 | 102.74 | W | NULL | real | +-----+-------------+------------------+--------+-----------+-------+-----------+------+ 1 row in set (0.00 sec) mysql> INSERT INTO points (id, accounts_id, value, units) VALUES (150,51,158.6,'W') ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=NOW(); ERROR 1364 (HY000): Field 'name' doesn't have a default value mysql>

Given either $arr1a or $arr1b, what is the best way to confirm that $arr2 has only the three indexes a, b, and c? $arr1a=['a','b','c']; $arr1b=['a'=>null,'b'=>null,'c'=>null]; $arr2=['a'=>1,'b'=>0,'c'=>0];

By the way, below is what I eventually used. Thanks again for the help. private function savePoints($points) { /* max_allowed_packet=16,777,216 Assume 16 characters per parameter Other text is about 160 characters. 11 bytes per character? (16777216*.8-160*11)/16*11 = 95K. Just use 50K */ foreach(array_chunk($points, 50000) as $p) { $sql = 'INSERT INTO points (id, accounts_id, value, units) VALUES '.rtrim(str_repeat('(?,?,?,?),',count($p)/4),','). ' ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=NOW()'; $stmt=$this->db->prepare($sql); $stmt->execute(call_user_func_array('array_merge', $p)); } }

Thank you all for your help. Particularly MacGyver for your great explanation, Kicken for preventing me from accidentally cascade deleting all my data, and Barand for your awesome benchmarking test.

Can REPLACE result in a foreign reference constraint, or worse, CASCADE DELETE?

Thanks MacGyver. Nice post. I've never used REPLACE before. Why do you recommend if c1 and c2 are the only columns. REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Wow, not 3 times faster but 600 times. As a mater of principle, seems like the way to go. Kinda forgot what I was originally asking...

Pesky documentation! I think it will only be incremented if a row is actually inserted. See the following queries which were performed after the ones in my previous post. mysql> INSERT INTO t(pk,c1,c2) VALUES (1,1111,1111),(3,3333,3333),(4,4444,4444) -> ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2); Query OK, 6 rows affected (0.00 sec) Records: 3 Duplicates: 3 Warnings: 0 mysql> mysql> SELECT * FROM t -> ; +----+------+------+ | pk | c1 | c2 | +----+------+------+ | 1 | 1111 | 1111 | | 2 | 222 | 222 | | 3 | 3333 | 3333 | | 4 | 4444 | 4444 | +----+------+------+ 4 rows in set (0.00 sec) mysql> INSERT INTO t(pk,c1,c2) VALUES (5,5555,5555),(3,3333,3333),(4,4444,4444) -> ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2); Query OK, 1 row affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM t; +----+------+------+ | pk | c1 | c2 | +----+------+------+ | 1 | 1111 | 1111 | | 2 | 222 | 222 | | 3 | 3333 | 3333 | | 4 | 4444 | 4444 | | 5 | 5555 | 5555 | +----+------+------+ 5 rows in set (0.00 sec) mysql>

Sorry, my previous post didn't show all the SELECT statement results, and I didn't notice until the edit time expired... mysql> DROP TABLE t; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE IF NOT EXISTS t ( -> pk INT NOT NULL AUTO_INCREMENT, -> c1 INT NOT NULL, -> c2 INT NOT NULL, -> PRIMARY KEY (pk) -> ) -> ENGINE = InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,11,11); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,22,22); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,33,33); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM t; +----+----+----+ | pk | c1 | c2 | +----+----+----+ | 1 | 11 | 11 | | 2 | 22 | 22 | | 3 | 33 | 33 | +----+----+----+ 3 rows in set (0.00 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES (1,111,111),(2,222,222),(3,333,333) -> ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2); Query OK, 6 rows affected (0.00 sec) Records: 3 Duplicates: 3 Warnings: 0 mysql> mysql> SELECT * FROM t; +----+-----+-----+ | pk | c1 | c2 | +----+-----+-----+ | 1 | 111 | 111 | | 2 | 222 | 222 | | 3 | 333 | 333 | +----+-----+-----+ 3 rows in set (0.00 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,44,44); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM t; +----+-----+-----+ | pk | c1 | c2 | +----+-----+-----+ | 1 | 111 | 111 | | 2 | 222 | 222 | | 3 | 333 | 333 | | 4 | 44 | 44 | +----+-----+-----+ 4 rows in set (0.00 sec) mysql>

Thanks Kicken, but I am not witnessing your prediction. What am I missing? Thanks mysql> CREATE TABLE IF NOT EXISTS t ( -> pk INT NOT NULL AUTO_INCREMENT, -> c1 INT NOT NULL, -> c2 INT NOT NULL, -> PRIMARY KEY (pk)) -> ENGINE = InnoDB; INSERT INTO t(pk,c1,c2) VALUES(NULL,11,11); INSERT INTO t(pk,c1,c2) VALUES(NULL,22,22); INSERT INTO t(pk,c1,c2) VALUES(NULL,33,33); SELECT * FROM t; INSERT INTO t(pk,c1,c2) VALUES (1,111,111),(2,222,222),(3,333,333) ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2); SELECT * FROM t;Query OK, 0 rows affected (0.04 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,11,11); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,22,22); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,33,33); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t; +----+----+----+ | pk | c1 | c2 | +----+----+----+ | 1 | 11 | 11 | | 2 | 22 | 22 | | 3 | 33 | 33 | +----+----+----+ 3 rows in set (0.00 sec) mysql> mysql> INSERT INTO t(pk,c1,c2) VALUES (1,111,111),(2,222,222),(3,333,333) -> ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2); Query OK, 6 rows affected (0.00 sec) Records: 3 Duplicates: 3 Warnings: 0 mysql> mysql> SELECT * FROM t; +----+-----+-----+ | pk | c1 | c2 | +----+-----+-----+ | 1 | 111 | 111 | | 2 | 222 | 222 | | 3 | 333 | 333 | +----+-----+-----+ 3 rows in set (0.00 sec) mysql> INSERT INTO t(pk,c1,c2) VALUES(NULL,44,4); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM t; +----+-----+-----+ | pk | c1 | c2 | +----+-----+-----+ | 1 | 111 | 111 | | 2 | 222 | 222 | | 3 | 333 | 333 | | 4 | 44 | 4 | +----+-----+-----+ 4 rows in set (0.00 sec) mysql>

I haven't measured it, but have read multiple places that it is about three times slower. I haven't set a target and acknowledge it is therefore academic, but am still curious.

Thanks Psycho, Thank you for changing the title to "UPDATE". The ON DUPLICATE is just a hack to allow multiple updates, and there should be no INSERTS since all the records already exist.. See my my response to Kicken's post. I agree the CASE statements seem a little scary from a troubleshooting standpoint. Note sure I understand your proposed second solution.

Thanks kicken, Why is AUTO_INCREMENT relevant? The only problem I see with the ON DUPLICATE solution is if I receive ['pk'=>99,'c1'=>4,'c2'=>12] and record with primary key 99 doesn't exist, a new record will be inserted. Any thing else? I expect the query would look something like the following. Is there a slick way to create [2,4,12,4,6,4,8,2,15] out of [[2,4,12],[4,6,4],[8,2,15]] other than looping over them?. INSERT INTO t(pk,c1,c2) VALUES (2,4,12),(4,6,4),(8,2,15) ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2);

Okay. I had thought they were email addresses or names or something. Also, I was going to comment on my original reply but thought better to staying just on the subject of prepared statements, but... Why set $_SESSION with $_POST values? It does not magically provide any protection. Do you really need these as a session?

I will be performing multiple updates. All updates will include data for the same columns (i.e. pk, c1 and c2). The number of records to be updates is variable, and will span from 1 to thousands (example below shows three records). The data to be updated is suspect, and need be escaped. PDO is available. Multiple queries is simple, however, not as efficient, and am thinking of doing multiple updates in a single query. max_allowed_packet is set to 16,777,216 on my server. Does this just mean my SQL needs to be under 116,777,216 bytes? Does this take into account the prepared statement placeholder values? Seems like question mark placeholders would be easiest. Agree? How can multiple updates be performed? Maybe with an ON DUPLICATE? Any other way? Any other advice? Thanks GIVEN: [[2,4,12],[4,6,4],[8,2,15]] UPDATE t SET c1=4, c2=12 WHERE pk=2; UPDATE t SET c1=6, c2=6 WHERE pk=4; UPDATE t SET c1=8, c2=2 WHERE pk=15;

Good job attempting to use PDO and prepared statements, but you are not doing them correctly. Until you totally know what you are doing, NEVER use anything except a prepared statement. For instance, don't use $stmt=$pdo->query($sql);, but use $stmt=$pdo->prepare($sql);. Next, never directly add a variable into your SQL statement. If you 100% new it was safe, maybe, for for now, NEVER. Your SQL statement should look like one of the following: $tbl_name='your_table_name'; $sql = "SELECT * FROM $tbl_name WHERE date_order BETWEEN ? AND ?"; // or $sql = "SELECT * FROM $tbl_name WHERE date_order BETWEEN :to AND :from"; Okay, I earlier said never insert a variable into your SQL, but doing so with $tbl_name is okay (if you really need to) because you 100% know it is safe. Never, however, put then in your WHERE statement even if you know they are safe until you really know what you are doing. Also, the to and from in your between statement doesn't make any sense. Finally, you insert your variables into the prepared statement using: $stmt=$pdo->prepare($sql); $stmt->execute([$_POST['to'],$_POST['from']]); //If using ? as placeholders // or $stmt->execute(['to'=>$_POST['to'],'from'=>$_POST['from']]); //If using :to or :from as placeholders Update your script, and then give a better description what you mean by "process the values"

Hi chickennugt, Can you tell us why benanamen's solution works or have any questions why it does what it does?