"INSERT ... ON DUPLICATE KEY UPDATE" query adds new records (or doesn't update existing records) - please check my syntax.

Trying to run INSERT INTO ... ON DUPLICATE KEY UPDATE - but its Inserting new records every time.
We're running with $wpdb because this is a Wordpress plugin and the data is in a custom table. One of the fields is a SPATIAL datatype for storing map location POINT data.

So the full code looks like this (apologies for the length)

$place_name = $_POST['place_name'];
$state = $_POST['state'];
$country = $_POST['country'];
$geocode = $_POST['geocode'];
$place_lat = $_POST['place_lat'];
$place_lng = $_POST['place_lng'];
$MX_id = $_POST['MX_id'];
$MX_title = $_POST['MX_title'];
$MX_artist = $_POST['MX_artist'];
$track_url = $_POST['track_url'];
$img_url = $_POST['img_url'];
$positive = $_POST['positive'];
$note = $_POST['note'];

//this is variable for the primary key
//this is variable for the primary key
	if ($_POST['ntpID']===NULL){
		$NTP_id = 0 ;
	}else{
		$NTP_id = $_POST['ntpID'];
	}
//$NTP_id = $_POST['ntpID'];
// if I uncomment this, the value returned is 0 and nothing is updated. 
// With it commented out, a new record is added even when the $NTP_id value exists in the ID field.

$t = time();
$usr = get_current_user_id();
$usrnm = get_user_by( 'id', $usr );
$usrlg = $usrnm->user_login;


$updateQuery = "INSERT INTO $table (`id`,`geocode`,
					`country`,`img_url`,`MX_artist`,`MX_id`,`MX_title`,`note`,
					`place_lat`,`place_lng`,`place_name`,`positive`,`state`,
					`track_url`,`user_id`,`user_login`,`geopoint`) 
				VALUES 
					($NTP_id,$geocode,'$country','$img_url','$MX_artist',$MX_id,
					'$MX_title','$note',$place_lat,$place_lng,'$place_name',$positive,
					'$state','$track_url',$usr,
					'$usrlg', GeomFromText('POINT($place_lat $place_lng)')) 
		 ON DUPLICATE KEY UPDATE  `note`= '$note',`positive`=$positive ; " ;

$wpdb->query($updateQuery);
echo $wpdb->insert_id;

Open in new window


Can anyone see why its adding a new record every time, instead of updating an existing record, when the NTP_id passed already exists in the PK 'id' field?
I'm sure I've made an error somewhere. I just can't see the wood for the trees...
Thanks in advance.
Tim
LVL 1
Tim BrocklehurstHead of DigitalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zakaria AcharkiAnalyst DeveloperCommented:
The query looks valid, I think the problem comes from the post of the primary key variable $_POST['ntpID'], try to give it a static value to make sure the query update when the valid value exist, something like :

$NTP_id = 10;

Open in new window


Note: you don't need to check if the post is null and change it to zero, if you pass the null it will be fine (try to comment the if/else too) to reduce possibilities.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
As this is primarily a database issue: post your table DDL.

Is the primary key a single column or a compound? Are the data types involved in the primary key columns of an exact data type? Did you test a query generated by your code in the Workbench directly?

WHY don't  you use parameterized queries? SQL Injection!!
Tim BrocklehurstHead of DigitalAuthor Commented:
Thanks guys... really helpful.
I did as you suggested, Zakaria, and it behaved correctly. So you were certainly right to advise against having a conditional on the primary_key criteria itself.  And ste5an, thanks - the primary is a single column (id) field. The query works in Workbench, yes. I might have used parameterized queries, but was put off the idea because I wanted the spatial field computation to be done by MySQL. I couldn't find any way for it to be done by PHP and passed as spatial point data to MySQL. Then again, perhaps I'm missing the point...

Anyway... since posting this yesterday, other issues came to light in the application which we have been fixing. All of them adding to the mystery... until we finally realised the significance of adding a Spatial field to the MySQL table. Doing so has made a difference to the way many of our PHP scripts engage with that table. It seems, for example, that no longer can we expect reliable results if we begin our queries with 'SELECT * ' - we now have to specify field names for only the fields we want returned. Perhaps this is because PHP can't yet handle the Spatial GEO field type...?

Anyway, having rectified this in all existing PHP files, and then returned to the code I posted last night, we now find that it works as expected. I can't be sure what fixed it exactly, but the outcome is good. The query I posted is the only one which generates the Spatial field data from lat, lng coordinates, and now its working. And so is the rest of it... Hurray!

I am really grateful to both of you for taking the time to help. Here's to it continuing to work.
I'll try not to trouble you with anything else too quickly, and I hopefully this discovery might help others interested in adding Spatial field types to their MySQL dbs.

Best wishes
Tim
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Tim BrocklehurstHead of DigitalAuthor Commented:
Thanks guys...
Really helpful.
Tim
Tim BrocklehurstHead of DigitalAuthor Commented:
After adding spatial datatype fields to a MySQL Db, we discovered that all our PHP queries beginning with 'SELECT * ' needed to be changed so that only the required fields were called in. Without this, they were returning null values to json.

This doesn't directly explain how we fixed the problem posted in here, because the query in our code there specifies each field precisely. However, after we removed the conditional attempting to set null primary_key criteria to 0, the query began working as it should.
Zakaria AcharkiAnalyst DeveloperCommented:
You're welcome, glad we could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.