Welcome to WebmasterWorld Guest from 50.19.34.255

Forum Moderators: open

Message Too Old, No Replies

MySQL: INSERT with a loop, then use ON DUPLICATE KEY UPDATE.

     
3:04 am on Mar 13, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


I'm inserting chunks of data on a regular basis, like this:

$query = "INSERT INTO table (pubdate, title, link, description) VALUES ";

foreach ($array as $key) {
list($var_1, $var_2, $var_3, $var_4) = explode('|:|', $key);

$query .= sprintf("('%s', '%s', '%s', '%s', '%s'), ",
mysql_real_escape_string($var_1),
mysql_real_escape_string($var_2),
mysql_real_escape_string($var_3),
mysql_real_escape_string($var_4));
}

$query = rtrim(', ', $query);

mysql_query($query);


(that code is still in development phase, so please ignore any typos)

But now, I want to modify it so that it updates duplicate keys, which I assume uses something like:

$query .= "ON DUPLICATE KEY UPDATE title='%s', link='%s', description='%s'"


But since I'm using literal replacements, and I'm doing the VALUES in a loop, I'm not sure where to put this.

The only option I can think of is to create a long series of queries within the loop:

$query = '';

foreach ($array as $key) {
list($var_1, $var_2, $var_3, $var_4) = explode('|:|', $key);

$query .= "INSERT INTO table (pubdate, title, link, description) VALUES ";
$query .= sprintf("('%s', '%s', '%s', '%s', '%s')",
mysql_real_escape_string($var_1),
mysql_real_escape_string($var_2),
mysql_real_escape_string($var_3),
mysql_real_escape_string($var_4));

$query .= "ON DUPLICATE KEY UPDATE title='%s', link='%s', description='%s';\n"
}

mysql_query($query);


I could be inserting 100 lines at once, though. Surely that's not the best solution?
5:24 am on Mar 13, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


Would REPLACE be faster / better than INSERT... ON DUPLICATE anyway?

$query = "REPLACE INTO table (pubdate, title, link, description) VALUES ";

foreach ($array as $key) {
list($var_1, $var_2, $var_3, $var_4) = explode('|:|', $key);

$query .= sprintf("('%s', '%s', '%s', '%s', '%s'), ",
mysql_real_escape_string($var_1),
mysql_real_escape_string($var_2),
mysql_real_escape_string($var_3),
mysql_real_escape_string($var_4));
}

$query = rtrim(', ', $query);

mysql_query($query);
11:01 pm on Mar 13, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


For the sake of posterity, I've tried this and it works, but I haven't benchmarked it to see if it's faster or slower.

However, it's worth noting that REPLACE deletes the row and then re-adds it, where INSERT INTO just updates it. So if you're using an Autoincrement, the number will change with each REPLACE.
3:19 am on Mar 14, 2017 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11156
votes: 116


https://dev.mysql.com/doc/refman/5.7/en/replace.html [dev.mysql.com]:
REPLACE is a MySQL extension to the SQL standard.


i would avoid using REPLACE unless it can't be done properly with INSERT INTO ... ON DUPLICATE KEY UPDATE...
5:01 am on Mar 14, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


Phranque, can you see a way to modify the original code without using INSERT INTO... ON DUPLICATE KEY? I've tried several variations, but the only way I can find is to run one INSERT for every row, but sometimes I have 100+ rows so that's not practical.