Update or replace existing multiple data if not exist insert without know unique key

Question

I have table like below

My query is :

INSERT INTO geo_layer (map_id, user_id, type, lat, lng, more_info) VALUES ('".$geo_id."','".$user_id."','".$type."','".$latln_0."','".$latlng_1."','".$more_info."') ON DUPLICATE KEY UPDATE map_id = '".$geo_id."', user_id = '".$user_id."', type = '".$type."', lat = '".$latln_0."', lat = '".$latlng_1."', more_info = '".$more_info."' WHERE map_id = '".$geo_id."' AND user_id = '".$user_id."'";

Query Output is:

INSERT INTO geo_layer (map_id, user_id, type, lat, lng, more_info) VALUES ('82','1','Point','88.409042358398','22.544903493838','') ON DUPLICATE KEY UPDATE map_id = '82', user_id = '1', type = 'Point', lat = '88.409042358398', lat = '22.544903493838', more_info = '' WHERE map_id = '82' AND user_id = '1'INSERT INTO geo_layer (map_id, user_id, type, lat, lng, more_info) VALUES ('82','1','Point','88.467063903809','22.564244372033','') ON DUPLICATE KEY UPDATE map_id = '82', user_id = '1', type = 'Point', lat = '88.467063903809', lat = '22.564244372033', more_info = '' WHERE map_id = '82' AND user_id = '1'INSERT INTO geo_layer (map_id, user_id, type, lat, lng, more_info) VALUES ('82','1','Point','88.401719927788','22.506530781509','') ON DUPLICATE KEY UPDATE map_id = '82', user_id = '1', type = 'Point', lat = '88.401719927788', lat = '22.506530781509', more_info = '' WHERE map_id = '82' AND user_id = '1'

What I want is: I like to update / replace existing data of multiple columns and if new data it will insert. but I don’t have access to unique id like primary key. data is coming from a array without any previous information.

Problem is except “layer_id” (auto increment) nothing is unique and I do not know what is layer_id before it run my query.

Solution I am thinking is check every row for existing value then insert if it new or update /replace previous or do both.

Please let me know if more details require.

look for `INSERT OR UPDATE` query of mysql . check here:- http://stackoverflow.com/a/4205207/4248328 — Alive to die - Anant, Oct 04 '16 at 07:34
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19 Yes I use this but I do not have access “id” before query VALUES(1, "A", 19) — Kuntal Parbat, Oct 04 '16 at 07:42
not exactly on topic, but creating a query like you did, is bound to allow attackers that can control the input (probably what is happening), to cause an SQL injection. — Noam Rathaus, Oct 04 '16 at 07:44
@nrathaus - yes, it is really risky if you have any suggestion please provide, but that should reach above goal — Kuntal Parbat, Oct 04 '16 at 08:05
@KuntalParbat for PHP use this: http://php.net/manual/en/pdo.prepared-statements.php — Noam Rathaus, Oct 05 '16 at 06:49

Update or replace existing multiple data if not exist insert without know unique key

0 Answers0