0

I have an array of values that I am looping through, trying to check a MySQL table for a column match for the array value, then inserting a new record if no match is found.

foreach($mls_ids as $mls_id) {

$query = "INSERT INTO mdc_data (mls_id) VALUES ('$mls_id') 
WHERE NOT EXISTS ( SELECT mls_id FROM mdc_data WHERE mls_id = '$mls_id')  
LIMIT 1 ";  
$result = mysqli_query($mysqli, $query) or trigger_error("Query Failed! 
SQL: $query - Error: ". mysqli_error($mysqli), E_USER_ERROR);   
}

No records are inserted into an the table. (the table is empty)

Error thrown: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT mls_id FROM mdc_data WHERE mls_id = '2189665') LIMIT

Nigel Ren
  • 51,875
  • 11
  • 34
  • 49
WGS
  • 189
  • 4
  • 15
  • See the manual https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html your query syntax failed. – Funk Forty Niner Jan 14 '19 at 15:52
  • 1
    Creating a unique index on MLS_ID and using 'REPLACE INTO' is probably a better approach if you want to refresh. Or just create a unique index on MSL_ID and do 'INSERT IGNORE' if you don't want to refresh anything – Dimi Jan 14 '19 at 15:53
  • @J. Knabenschuh the limit was from a code example I tried. Removed it but same error. – WGS Jan 14 '19 at 15:58
  • @Funk Forty Niner look at the reference and syntax looks correct. – WGS Jan 14 '19 at 15:58
  • [See this answer](https://stackoverflow.com/a/3025332/1415724), might help. – Funk Forty Niner Jan 14 '19 at 16:01

2 Answers2

0
INSERT INTO mdc_data (mls_id)
SELECT * FROM (SELECT '$mls_id') AS tmp
WHERE NOT EXISTS (
    SELECT mls_id FROM mdc_data WHERE id = '$mls_id'
) LIMIT 1;
Bedram Tamang
  • 1,437
  • 12
  • 13
0

Working code.

Added a unique index on mls_id col (@Dimi).

Used example provided by @Funk Forty Niner for the rest.

$query = "INSERT INTO mdc_data (mls_id) 
SELECT '$mls_id' FROM DUAL
WHERE NOT EXISTS (SELECT mls_id FROM mdc_data 
      WHERE mls_id='$mls_id') 
LIMIT 1";

Better understanding of the use of Dual now.

Thanks for the assistance!

WGS
  • 189
  • 4
  • 15