i have implemented code insert , update data in database when try new row's data being inserted in table , not being updated how can define unique key in case work? have primary key id.
<?php $json = '{"latitude":53.86898451,"longitude":10.66561387,"route":4}'; $data = json_decode ( $json ); $route = "route_" . $data->{'route'}; $latitude = $data->{'latitude'}; $longitude = $data->{'longitude'}; require 'connection.php'; // check whether route's table exist. $results = $con->query ( "show tables like'" . $route . "'" ) or die ( mysqli_error () ); if (($results->num_rows) == 1) { //"update myguests set lastname='doe' id=2" $sql = "insert ".$route."(latitude, longitude, created_at) values( ? , ? , now() ) on duplicate key update latitude = values(latitude) , longitude = values(longitude)"; $stmt = $con->prepare($sql) or die ( $con->error ); $stmt->bind_param("ss",$latitude,$longitude); $stmt->execute(); $stmt->close(); echo "table exist"; } else { $create = "create table " . $route . " (id int(11) not null auto_increment primary key unique, latitude float(10,6) not null, longitude float(10,6) not null, created_at timestamp default current_timestamp)" ; $stmt = $con->prepare($create) or die ( $con->error ); $stmt->execute(); $stmt->close(); echo "table craeted"; } update
this i'm trying now
$sql = "insert ".$route."(id, latitude, longitude, created_at) values( id, ? , ? , now() ) on duplicate key update latitude = values(latitude) , longitude = values(longitude)"; which variable should add in values braces id?
you not specifying value id in insert statement, there never duplicate key.
instead, auto-generating next available id.
to solve this, specify value of pre-existing id (if any).
