troubleshooting Question

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

Avatar of Tim Brocklehurst
Tim BrocklehurstFlag for United Kingdom of Great Britain and Northern Ireland asked on
PHPMySQL ServerWordPressSQL
6 Comments2 Solutions130 ViewsLast Modified:
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;

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros