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.
"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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
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!!
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!!
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
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.
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.
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.