I have a query:

$query_add_analogue
  ->insert($db->quoteName('#__parts_analogues'))
  ->columns($db->quoteName($columns))
  ->values($value);
$db->setQuery($query_add_analogue);
$db->execute();

The table "#_parts_analogues" have two columns - "original_id" and "analogue_id".

I need to ignore the insert function, when the pair of values already exist in table. There is no primary key in table.

up vote 3 down vote accepted

As you're using MySQL, you can create an unique index over your columns original_id and analogue_id.

Then use a raw query like:

$db = JFactory::getDbo();
$query = 'INSERT IGNORE INTO ' . $db->quoteName('#__parts_analogues') VALUES ("your", "values");
$db->setQuery($query)->execute();

It works like an insert, but does not return an error.

Keep in mind that working without an index can lead to serious performance issues.

  • Sorry, can you explain what is "unique index over columns"? I can't understand =( – user3774771 Jul 16 '17 at 7:07
  • @christian if you see, you have not used actual OO query here, is there any way to achieve this with complete OO query. – Anant Mar 27 at 11:42
  • @Anant without performing a str_replace() this is as "OO" as it can get because Joomla's insert() method does not accommodate IGNORE. – mickmackusa Jul 19 at 23:05

I give a solution on this just for the new readers who may are looking for solution on this kind of problems. You cannot really solve this problem with Joomla query methods. As it was given above, a simple MySQL query like "INSERT IGNORE INTO" should work but it is not always the right solution. Instead, the "INSERT INTO...ON DUPLICATE KEY UPDATE" MySql query works. Why? Because if a record already exists, then it will be just updated (probably with the same record or with an updated record with the same ids and unique fields, so it is not a problem), if the record does not exist, then it will be created, inserted as a new record in the table.

First, understanding this particular problem in the original question (in this problem you want to insert or update a pair of keys into the table), you should not make the two columns in the table UNIQUE - "original_id" and "analogue_id". Because if you make them unique then you cannot insert again a pair of new keys where one of the keys is the same as the earlier inserted key of the pair. For example you earlier inserted a pair of keys like original_id = 1 and analogue_id = 1, and now you try to insert a new pair like original_id = 1 and analogue_id = 2. If these keys are both UNIQUE in the table you will not be able to insert the second pair as new pair and at the same time you will not want to update them either.

So what I would do is I would generate (in my code) a real UNIQUE key from the original_id and analogue_id keys and I would insert them together with the generated Unique key into the table. And then when I am updating the table with new data or inserting new data in the table I would use for example:

$my_unique_key = $original_id . $analogue_id . "something_to_make_it_more_unique";

$myTableItems = new stdClass();

// You can put all of the data items into the $myTableItems object
// if you have many of these data
// I will not do that here due to time saving

// and in foreach loop

foreach ($myTableItems as $item) {

  $fields = array($db->quoteName('myunique_key') . ' = ' . $db->quote($item->my_unique_key),
            $db->quoteName('original_id') . ' = ' . $db->quote($item->original_id),
            $db->quoteName('analogue_id') . ' = ' . $db->quote($item->analogue_id));

  $values = array($item->my_unique_key, $item->original_id, $item->analogue_id);

  $query = "INSERT INTO #__mytable (myunique_key, original_id, analogue_id) VALUES ('" . implode("', '", $values) . "')
  ON DUPLICATE KEY UPDATE" . implode(", ", $fields);

  $db->setQuery($query)->execute();
  }

And if you want to just insert the new records and you do not want to update the old records then you can use the INSERT IGNORE INTO query like this:

$query = "INSERT IGNORE INTO #__mytable (myunique_key, original_id, analogue_id) VALUES ('" . implode("', '", $values) . "')";

Simple reference on these Sql queries is for example here.

  • I feel that this ON DUPLICATE is the right solution to a different question. Here the OP states clearly that if the row is a duplicate (based on the column criteria), then nothing should be done. If your query is executed with the same outcome, then the database row will be overwritten with the pre-existing value(s) and make success/aff_rows harder to determine than the alternative. This is why I would not recommend ON DUPLICATE. – mickmackusa Jul 19 at 23:15
  • Yes, you are right, many times you do not want to update the existing data in the table, then the INSERT IGNORE query works the best. I update my message with that query. However, In your long description the $db->setQuery(str_replace("INSERT", "INSERT IGNORE", $insert_query)); does not work, you cannot do it that way. The query will not be changed to INSERT IGNORE from INSERT that way. (I have checked that). Have you used that and it worked? – Zollie Jul 20 at 6:13
  • Yes, it does work. I test all of my queries before posting because I would hate to give false information. (And there are other posters that have used the same approach on this site) That said, it is vulnerable to "over-replacing" in the fringe case where you have a value that contains the word INSERT. I could use regex to ensure only the first INSERT is replaced, but I didn't go down that rabbit hole. – mickmackusa Jul 20 at 6:14
  • I'll double check now. – mickmackusa Jul 20 at 6:19
  • Ohh, then I will check that again, I do not know why it was not working for me... Thank you for your comments! – Zollie Jul 20 at 6:20

Option #1 - the clean way (my recommended approach)

Call this query from phpMyAdmin to permanently establish the two columns as UNIQUE KEYS. (You will need to replace the #_ prefix with your private prefix string.)

ALTER TABLE `#__parts_analogues` ADD UNIQUE KEY(original_id, analogue_id)

Then whenever you wish to throw new rows into the table, you can use a INSERT IGNORE INTO query to avoid receiving those syntax errors. This adjustment is applied inside the setQuery() call.

Here is how I would write the OO code:

$db = JFactory::getDBO();
try {
    $insert_query = $db->getQuery(true)
                       ->insert("#__parts_analogues")
                       ->columns("original_id, analogue_id")
                       ->values($value); // I'm not sure if you are delivering a string, 1-dim array, or multi-dim array

    $db->setQuery(str_replace("INSERT", "INSERT IGNORE", $insert_query));    // add IGNORE 
    $db->execute();
    echo $db->getAffectedRows() , " row(s) inserted into parts_analogues table";
} catch (Exception $e) {
    echo  "Syntax Error"; // . " & Error: " . $e->getMessage();
}

I'm not sure if $value is a comma-separated string, a one-dimensional array containing two elements, or a multi-dimensional array containing sets of 2-element subarrays. If these id values are integers and coming from an external source, you should cast each id as (int) before feeding to the query for security reasons. Here's some relevant literature:

I should also point out that there is technically a possibility of making unintended multiple replacements within the query while using str_replace(), so if you have your tin-foil hat on, you can use this with the same effect:

$db->setQuery(preg_replace('~INSERT \K~', 'IGNORE ', $insert_query, 1)));    // add IGNORE
// Joomla puts \r\n (or just \n depending on your operating system) at the start of the query; var_dump($db) after setQuery() to see.

Option #2 - which IMO is the less elegant way because it requires two round trips to the database.

Basically, you would run a SELECT COUNT(*) query with a WHERE clause that searches for matches using your $values data to determine if the pair of values exists in the table, then you call an INSERT query when it is unique. *note, you can use two where() method calls, but I don't prefer that style because it is too much of a departure from raw sql syntax. You could also hardcode the AND and write a single string, but I was trying to get the whole code block to fit the available width on this page.

$db = JFactory::getDBO();
try {
    $select_query = $db->getQuery(true)
                       ->select("COUNT(*)")
                       ->from("#__parts_analogues")
                       ->where(
                           array(
                             "original_id = " . (int)$original_id),
                             "analogue_id = " . (int)$analogue_id)
                           )
                         )
                       ->setLimit(1);

    $db->setQuery($select_query);
    if (!$db->loadResult()) {
        // if count is 0 (false-y) that means the values are unique
        $insert_query = $db->getQuery(true)
                           ->insert("#__parts_analogues")
                           ->columns("original_id, analogue_id")
                           ->values((int)$original_id . "," . (int)$analogue_id);

        $db->setQuery($insert_query);
        $db->execute();
        echo "Row Added";
    } else {
        echo "Non-unique Values";
    }
} catch (Exception $e) {
    echo  "Syntax Error"; // . " & Error: " . $e->getMessage();
}
  • I like option 2 as a solution on this particular question, even if it is two rounds on database. – Zollie Dec 6 at 20:37
  • What's not to like about the first? – mickmackusa Dec 6 at 20:39
  • In your first solution I still do not like that you made both fields unique. Your second solution however really answer the original problem, so that is actually the correct solution. – Zollie Dec 6 at 20:50
  • It is my understanding that the pair of columns logically needs to be unique. Setting up a "fit-for-purpose" database table structure enables refined querying processes for all future interactions (code size is halved in this case). I don't understand how this detracts, but you are free to like whatever you like. Thanks for the vote. – mickmackusa Dec 6 at 21:08
  • 1
    Of course, we had a difference in understanding of the original problem of this question, but it does not matter. I can learn a lot from your well worked out query examples and that's what really matters for me. – Zollie Dec 6 at 21:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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