1

I know how to do INSERT INTO if the record doesn't exist. But if it does exist, mysql doesn't treat it as an error. It just treats it as 0 rows inserted. I want to know if that was the case and return that message to the user that the data they submitted already exists.

I know I can do it if I first query mysql before I attempt to insert the new data. However, is there a way to do it with the INSERT and get a result?

gurji999
  • 13
  • 1
  • 3
  • Welcome to SO ... please show us some code you tried and more detail about it. Take look at [this](https://stackoverflow.com/help/how-to-ask) to ask a better question. – sajjad rezaei Jan 14 '22 at 16:42
  • 1
    Unless you use `INSERT IGNORE` or `ON DUPLICATE KEY UPDATE`, you should get an error if you try to create a duplicate record. – Barmar Jan 14 '22 at 16:43
  • Does this answer your question? [How to handle error for duplicate entries?](https://stackoverflow.com/questions/3146838/how-to-handle-error-for-duplicate-entries) – sajjad rezaei Jan 14 '22 at 16:44
  • You can call `AFFECTED_ROWS()` to get the number of rows that were inserted. PDO and mysqli have functions that return this. – Barmar Jan 14 '22 at 16:45
  • I am not using a primary key to match. I am actually matching 2 fields. If both exist, then it is considered a duplicate. – gurji999 Jan 14 '22 at 18:24
  • $sql="INSERT INTO common_repairs (isp_id, repair_name) SELECT * FROM (SELECT '$ispid', '$newrepair') AS temp WHERE NOT EXISTS ( SELECT repair_name FROM common_repairs WHERE repair_name = '$newrepair' AND isp_id='$ispid' ) LIMIT 1"; – gurji999 Jan 14 '22 at 18:25

1 Answers1

0

You can use ON DUPLICATE KEY UPDATE in which case mysqli::$affected_rows should return 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values (source). So your code should be something like:

if ($mysqli->affected_rows == 1) {
                    
    $inserted = true;
}

if ($mysqli->affected_rows == 2) {
        
    $updated = true;
}
6opko
  • 1,718
  • 2
  • 20
  • 28
  • I just tried this, and now matter what, it returns 0. 2 is irrelevant since, I am only INSERTING and not UPDATING. Here is my SQL. `code` $sql="INSERT INTO common_repairs (isp_id, repair_name) SELECT * FROM (SELECT '$ispid', '$newrepair') AS temp WHERE NOT EXISTS ( SELECT repair_name FROM common_repairs WHERE repair_name = '$newrepair' AND isp_id='$ispid' ) LIMIT 1"; `/code` – gurji999 Jan 14 '22 at 18:17
  • Which fields are your keys in the common_repairs table? isp_id? – 6opko Jan 14 '22 at 22:18
  • If I understood your query correctly (it seems it's more complicated than it could be) you could possibly add isp_id, repair_name as a unique index (ALTER TABLE common_repairs ADD UNIQUE my_unique_index (isp_id, repair_name);) so when you do simple $sql = "INSERT INTO common_repairs (isp_id, repair_name) VALUES ('$ispid', '$newrepair')"; mysqli::$affected_rows will simply return 1 if inserted (the pair doesn't exist) or 0 if it's not inserted (the pair already exists). Hope that makes sense. – 6opko Jan 14 '22 at 22:29