Insert into if not exist MySQL

Question

I'm using PostgreSQL to get data and i want to insert it into MySQL. I have a php script which allows me to get the data, check for repeated and then insert it into MySQL DB. Problem is that instead of inserting only the non existing data it inserts one random row and doesn't insert nothing else.

$check_for_episode = mysqli_query($conn, "SELECT DISTINCT house_number,number FROM episode WHERE house_number LIKE '".$house_number."' AND number = ".$episode_id_pg." LIMIT 1");

  if (mysqli_fetch_array($check_for_episode)){

  }else{
     $insert_episode = mysqli_query($conn, "INSERT INTO episode(".$episode_col.")VALUES('".$episode_values."')");

  }

the variables $episode_col and $episode_values get data from an array called $episode through implode:

$episode_col = implode(", ", array_keys($episode));
$episode_values = implode("', '", array_values($episode));

Possible duplicate of [MySQL: Insert record if not exists in table](http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table) — maximkou, May 16 '17 at 12:58
i've seen the post before, but when i try to do the NOT EXIST query it says that it doesn't work in phpMyAdmin — Mika, May 16 '17 at 13:07

score 1 · Accepted Answer · answered May 16 '17 at 13:35

This is you code.

IF statements are used for comparisons, yours is just fetching the array and there's no condition inside your IF statement. You need to fix the statements to get the columns and their respective values.

$check_for_episode = mysqli_query($conn, "SELECT DISTINCT house_number,number FROM episode WHERE house_number LIKE '".$house_number."' AND number = ".$episode_id_pg." LIMIT 1");

  if (mysqli_fetch_array($check_for_episode)){

  }else{
     $insert_episode = mysqli_query($conn, "INSERT INTO episode(".$episode_col.")VALUES('".$episode_values."')");

  }

You can try to use WHERE EXISTS or WHERE NOT EXISTS too.

SELECT DISTINCT column1 FROM table1
  WHERE EXISTS (SELECT * FROM table2
                WHERE table2.column1 = table1.column1);

SELECT DISTINCT column1 FROM table1
  WHERE NOT EXISTS (SELECT * FROM table2
                    WHERE table2.column1 = table1.column1);

For more info: https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

Insert into if not exist MySQL

1 Answers1