3

With latest joomla version I try to achieve one query to the database and updating multiple rows at once.

I want to make a query like this:

INSERT INTO `tablename`
(`id`,`date`) VALUES 
('79','2018-12-01'),('78','2018-10-01'),('76','2018-06-01'),('80','2019-01-01')
ON DUPLICATE KEY UPDATE
(`date` = '2018-12-01'),(`date` = '2018-10-01'),(`date` = '2018-06-01'),(`date` = '2019-01-01')

If I setup this and debug:

$query
        ->insert($db->quoteName('#__tablename'))
        ->columns($db->quoteName($columns))
        ->values($values);

this only produces the first 3 row of my wanted query. How can I extend to add the text "ON DUPLICATE KEY UPDATE" and another array values to the end? Or there is another way of doing this? Its possible to add multiple query to setQuery, or concatenate queries?

1

Building a REPLACE INTO query is just as simple as building an INSERT ... ON DUPLICATE KEY UPDATE. Note that there is a difference between REPLACE INTO AND ON DUPLICATE KEY UPDATE.

Code:

$columns = ['id', 'date'];
$data = [
    [79, '2018-12-01'],
    [78, '2018-10-01'],
    [76, '2018-06-01'],
    [80, '2019-01-01']
];

$db = JFactory::getDbo();
try {
    foreach ($data as &$row) {
        $row = (int)$row[0] . ", " . $db->q($row[1]); // flatten 2-dim array and apply security techniques
    }
    $query = $db->getQuery(true)
                ->insert($db->qn('#__tablename'))
                ->columns($db->qn($columns))
                ->values($data);
    $db->setQuery(substr_replace($query, 'REPLACE', 0, 8));    // swap INSERT for REPLACE
    // in other words: $db->setQuery(str_replace("\r\nINSERT", "REPLACE", $query));
    // or:             $db->setQuery(preg_replace("~^\s+INSERT~", "REPLACE", $query));
    // Joomla puts \r\n at the start of the query; see via var_dump($db) after setQuery()
    /*
    echo "<pre>";
        print_r($db);   // see the updated sql object
    echo "</pre>";
    */
    $db->execute();
} catch (Exception $e) {
    echo  "Syntax Error: " , $e->getMessage();  // never show php's error message to the public
}
echo "<div>" , $db->getAffectedRows() , " row(s) inserted</div>";

This is the query built:

REPLACE INTO `#__tablename`
(`id`,`date`) VALUES 
(79, '2018-12-01'),(78, '2018-10-01'),(76, '2018-06-01'),(80, '2019-01-01')

This will ALWAYS print the same number of affected rows for your batch of four entries:

4 row(s) inserted



As for ON DUPLICATE KEY UPDATE..., this is as simple as appending the clause to the end of the Joomla-generated query and including VALUES() syntax. (only replace the $db->setQuery() line from the above snippet)

$db->setQuery($query . " ON DUPLICATE KEY UPDATE `date` = VALUES(`date`)");

to create this query:

INSERT INTO `#__tablename`
(`id`,`date`) VALUES 
(79, '2018-12-01'),(78, '2018-10-01'),(76, '2018-06-01'),(80, '2019-01-01')
ON DUPLICATE KEY UPDATE `date` = VALUES(`date`)

This type of query behaves differently and gives different feedback in terms of affected rows.

When identical rows exist in the database table for all of the rows that you intend to add, you will see 0 affected rows. If the PRIMARY ID already exists -- let's say 1 row has a different date value than what is in the query -- you will see 2 affected rows (I have run this on my own localhost and despite other things I've read, this must be removing, then adding). So this kind of information can be either very helpful or very confusing depending on what you are trying to determine. Imagine if you have all four ids represented in your table already and two of the four rows need the date value to be updated. After the query executes, you will be notified that 4 rows have been affected.

Here is a relevant answer that I posted a few years ago on StackOverflow.

  • Until your answer I created manual the query, but now, I updated thanks to you. For me in InnoDB mysql, the INSERT is the working choice, if exist the primary key, then will update the record, and doesn't increment the autoincrement value (primary key). The REPLACE INTO will throw an 1064 SQL syntax error (MySql 5.7). What I noticed that, it doesn't work if I use the setQuery 2 times, the INSERT overwrite with REPLACE will work only in one setQuery like: $db->setQuery(preg_replace("~^\s+INSERT~", "REPLACE", $query) . " ON DUPLICATE KEY UPDATE date = VALUES(date)"); – almost okey Sep 18 '18 at 16:41
  • I never meant for you to call setQuery() two times; just one time per snippet. Perhaps my answer is confusing. Yes, actions pertaining to autoincremented keys is another vital concern when choosing one of the techniques. I am surprised to hear that either of the answers delivered a 1064 error code -- I'd need to see your table schema and data to investigate. – mickmackusa Sep 18 '18 at 20:42
  • Okay, I just re-read your comment and realized that you merged my two separate techniques into one. You mustn't attempt this. Choose either REPLACE or INSERT ... ON DUPLICATE KEY UPDATE..., not both in the same query. – mickmackusa Sep 19 '18 at 2:32
  • Yeah, that was the problem, I supposed that I have to use both, my bad, sorry, and thank you! – almost okey Sep 19 '18 at 9:22
  • What? Wait. No. Don't use both. This is what I am trying to explain. – mickmackusa Sep 19 '18 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.