MySQL insert ignore in PHP with where statement?

Question

I have the following MySQL statement in a PHP file that imports a .csv file. The statement successfully ignores exact record matches but I am getting some duplicate records in the table where the records are not exact 100%. I did notice that in those cases where duplicates are occuring the LATITUDE and LONGITUDE is still always the same. Is there a way to expand this with some sort of WHERE statement to also exclude records on import if these two fields already have a match in the table? I've tried all sorts of WHERE attempts and get a MySQL error?

    $query = "insert ignore into $databasetable values('$linemysql');";

jeroen · Accepted Answer · 2012-12-20 02:31:19Z

1

You can add a unique index on multiple columns in mysql directly.

In mysql it would be something like (for 2 columns from memory...):

ALTER TABLE `your_table` ADD UNIQUE `some_name_like_position` ( `LATITUDE` , `LONGITUDE` );

edited Dec 20, 2012 at 2:31

answered Dec 20, 2012 at 2:26

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rocco The Taco Over a year ago

So if I set a primary key on MLS_LISTING_ID, LATITUDE and LONGITUDE for example must all 3 match for a record to be omitted? Is it possible to omit if MLS_LISTING_ID OR (LATITUDE and LONGITUDE) matches?

jeroen Over a year ago

@Rocco The Taco Yes, the combination of the 3 is defined as unique so you cannot add a new row with the exact same 3 values but 2 the same ones and 1 different one will enter.

Rocco The Taco Over a year ago

Excellent. That was my problem, I did not account that the MLS could be different. I set MLS as the primary and then created an index for Lat/Long and it worked. Thanks so much for the help!

Collectives™ on Stack Overflow

MySQL insert ignore in PHP with where statement?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related