Abhishek Singh Abhishek Singh - 1 year ago 63
PHP Question

insert into table or update for duplicate key error in php

I get Syntax error with my code in the query statement but I cannot figure out why. I have

id, token, gpslat, gpslong
as my table columns. I want to update record if token name is same otherwise insert. I am getting error. Following is my PHP code.


header('Content-Type: application/json');

$token = $_GET['token'];
$gps = $_GET['gps'];
$date = $_GET['gpslat'];


$sql = "INSERT INTO whosonline (token,gpslat,gpslong) VALUES ('$token', '$date', '$gps') ON DUPLICATE KEY gpslat='$date',gpslong='$gps'";

if ($conn->query($sql) === TRUE) {
// echo "New record created successfully";
$idx= mysqli_insert_id($conn);

$sql = "SELECT * FROM tokentable WHERE token='$token'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$uname = $row["username"];
} else {


} else {
echo "Error: " . $sql . "<br>" . $conn->error;

Error I am getting :
Error: INSERT INTO whosonline (token,gpslat,gpslong) VALUES ('{3CAD0B79-41DA-2CFC-9A9A-644B3E1A93C7}', '11.1241', '101.21020') ON DUPLICATE KEY gpslat='11.1241',gpslong='101.21020'<br>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 'gpslat='11.1241',gpslong='101.21020'' at line 1{"status":0,"msg":{"msg":"Error adding user!"}}

Answer Source

You forgot the UPDATE keyword in the ON DUPLICATE KEY portion of the query:

$sql = "INSERT INTO whosonline (token,gpslat,gpslong) VALUES ('$token', 
       '$date', '$gps') ON DUPLICATE KEY UPDATE gpslat='$date',gpslong='$gps'";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download