asked on "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)
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
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
PHPMySQL ServerWordPressSQL
Last Comment
Zakaria Acharki
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
ASKER
Thanks guys...
Really helpful.
Tim
Really helpful.
Tim
EXPERT CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You're welcome, glad we could help.
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!!