CODEWITHSUNDEEP
phpmysqldatabasereplace
fa_devlpr
fa_devlpr

Reputation: 57

Overwriting entries in a MYSQL database with new entries

I am trying to replace previous entries in MYSQL database each time new data is available, I have the following PHP code but it seems to add new entries each time. Please help, thanks.

I have tried using REPLACE but it still does not work, could anyone tell me what it is I am doing wrong?

<?php

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

$data = json_decode(file_get_contents('php://input'), true);

$mysqli = new mysqli("localhost","dbuser","Pa55uu0Rd","iewdb");

if (mysqli_connect_errno())
{
    echo json_encode(array('error' => 'Failed to connect to MySQL: ' . mysqli_connect_error() ));
    return;
}

if(!$data)
{
    echo json_encode(array('error' => 'Error input data'));
    return;
}
    $usernme = $data['usernme'];
    $longitude = $data['longitude'];
    $latitude = $data['latitude'];

$user = $mysqli->query("SELECT id FROM Users WHERE usernme = '$usernme' LIMIT 1");  

$user_id = $user->fetch_object();

if(!$user_id)
{     
     $mysqli->query("INSERT INTO Users (usernme) VALUES ('$usernme');");

     $user_id->id = $mysqli->insert_id;
}

if($longitude && $latitude)
{
    $mysqli->query("REPLACE INTO Locations (User_id,Longitude, Latitude) VALUES ($user_id->id,$longitude,$latitude);");
} 

$mysqli->close();

echo json_encode(array('user_id' => $user_id->id));

Upvotes: 0

Views: 1518

Answers (3)

fa_devlpr
fa_devlpr

Reputation: 57

Here is my solution to the problem and it works just fine. I decided to go with UPDATE as you can see below as I thought it was tidiest, thanks for the help.

<?php

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

//get parameters
$data = json_decode(file_get_contents('php://input'), true);

// Create connection
$mysqli = new mysqli("localhost","dbuser","Pa55w0rd","ewdb");

// Check connection
if (mysqli_connect_errno())
{
   echo json_encode(array('error' => 'Failed to connect to MySQL: ' . mysqli_connect_error() ));
   return;
}

if(!$data)
{
    echo json_encode(array('error' => 'Error input data'));
    return;
}

    $usernme = $data['usernme'];
    $longitude = $data['longitude'];
    $latitude = $data['latitude'];

$user = $mysqli->query("SELECT id FROM Users WHERE usernme = '$usernme' LIMIT 1");  

$user_id = $user->fetch_object(); 
if(!$user_id) 
{     
     $mysqli->query("INSERT INTO Users (usernme) VALUES ('$usernme');");

     $user_id->id = $mysqli->insert_id; 

     $mysqli->query("INSERT INTO Locations (User_id) VALUES ($user_id->id);");

}

if($longitude && $latitude)
{
    $mysqli->query("UPDATE Locations SET Longitude = $longitude, Latitude = $latitude WHERE User_id = $user_id->id;");

} 

/* close connection */
$mysqli->close();

echo json_encode(array('user_id' => $user_id->id));

Upvotes: 1

tzafar
tzafar

Reputation: 664

use update query something like this

UPDATE MyTable
SET User_id = 'USER_ID_VALUE', Longitude='LONGITUDE_VALUE', Latitude='LATITUDE_VALUE' 
WHERE SomeOtherColumn LIKE '%PATTERN%'

Upvotes: 1

Prashant M Bhavsar
Prashant M Bhavsar

Reputation: 1164

Logic : Instead of replacing old entry you can delete that old entries and later add fresh entries into database will always good in case of performance..

So you will have to write one delete and insert query only...instead of 3 queries

Upvotes: 1

Related Questions