Prepared Statement with ON DUPLICATE KEY
I think this one should be pretty easy but I am having trouble getting it right. I have searched a bit but being new to prepared statements I can’t quite figure out the syntax from looking at other examples I have found here and elsewhere. Anyhow here is my code.
if($stmt = $mysqli -> prepare("INSERT INTO user_info (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, . ) ON DUPLICATE KEY UPDATE (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, . )")) < $stmt ->bind_param("sssssssi",$city, $state, $website, $public_contact, $user, $zipcode, $pic, $emailme); $stmt -> execute(); $stmt -> bind_result($result); $stmt -> close(); > user is the unique. This IMO is just a syntax problem so could somebody help me out with the correct syntax? Much appreciated. ETA: just to help trouble shooting this does work as intended when I remove the ON DUPLICATE KEY UPDATE part but obviously, it only allows the one record per user and will not update UPDATE: was never able to find a working syntax to use the ON DUPLICATE KEY UPDATE so what I did instead (admittedly probably not the most efficient way) was check the table before hand for the user. If the user exist I run and UPDATE, if not I run a INSERT. Below is my working code. Hope this helps somebody who gets stuck in my situation.
$sql = "SELECT * FROM user_info WHERE user='$user'"; if ($result=mysqli_query($mysqli,$sql)) < /* Return the number of rows in result set */ $rows=mysqli_num_rows($result); /* Free result set */ mysqli_free_result($result); >if($rows == 0) < if($stmt = $mysqli ->prepare("INSERT INTO user_info (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, . ) ")) < $stmt ->bind_param("sssssssi",$city, $state, $website, $public_contact, $user, $zipcode, $pic, $emailme); $stmt -> execute(); $stmt -> bind_result($result); $stmt -> close(); > > else < if($stmt = $mysqli ->prepare("UPDATE user_info SET city=?, state=?, website=?, public_contact=?, zipcode=?, pic=?, emailme=? WHERE user='$user'")) < $stmt ->bind_param("ssssssi",$city, $state, $website, $public_contact, $zipcode, $pic, $emailme); $stmt -> execute(); $stmt -> bind_result($result); $stmt -> close(); > > getting mysql_insert_id() while using ON DUPLICATE KEY UPDATE with PHP
I’ve found a few answers for this using mySQL alone, but I was hoping someone could show me a way to get the ID of the last inserted or updated row of a mysql DB when using PHP to handle the inserts/updates. Currently I have something like this, where column3 is a unique key, and there’s also an id column that’s an autoincremented primary key:
$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3"; mysql_query($query); $my_id = mysql_insert_id(); $my_id is correct on INSERT, but incorrect when it’s updating a row (ON DUPLICATE KEY UPDATE). I have seen several posts with people advising that you use something like
INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE to get a valid ID value when the ON DUPLICATE KEY is invoked— but will this return that valid ID to the PHP mysql_insert_id() function?
I don’t have the answer. But the solution looks clever. Why don’t you try it? I would think it shouldn’t be to hard to create a test case that would give a definitive answer. PS.: Don’t get me wrong though; I can understand you would want reassurance. I would probably too. But I would try it first I guess. 🙂
I was curious how that could work but I found this in the MySQL manual: If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().
@Alexandre: yes, and presuming mysql_insert_id() is just a proxy to LAST\_INSERT\_ID() I would think this should work as expected.
thanks guys, this is the approach that worked for me. You’re right— when you use the it sets the value of mysql_insert_id = the updated ID.
4 Answers 4
Here’s the answer, as suggested by Alexandre:
when you use the it sets the value of mysql_insert_id = the updated ID— so your final code should look like:
This will return the right value for $my_id regardless of update or insert.
This helped me, but just to be clear, for anyone else having the problem. If the record already exists and gets updated, the call to LAST_INSERT_ID() ensures that when you later call mysql_insert_id(), it’ll return the id of the record updated, and not zero as it usually would. Also note that «id» should be replaced by the auto_increment column name in the table. More info: dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
Sadly as of MySQL 5.7 this no longer appears to work, it does not give back the correct value. It appears to give the insert id + the result of the update (0,1,2)
What exactly is «id» ? Is this the auto increment colum name ? What if the thread starter has «foo» as auto increment unique identifier?
You could check if the Query was an insert or an update ( mysql_affected_rows(); returns 1 on insert and 2 on update).
If it was an insert use mysql_insert_id, if it was an update you’d need another Query.
I know it’s not excatly what your looking for but it’s the best i could come up with
Although not using mysql_insert_id() and ON DUPLICATE KEY UPDATE, alternative great way to get the value of any field when updating another found here:
UPDATE table SET , . LIMIT 1; SELECT @tempid; I used it having table with (id,status) ‘id’ primary index auto-increment, and ‘status’ was the field upon which update was made, but i needed to get ‘id’ of the updated row. This solution also proof to race conditions as mysql_insert_id().
This is my solution where you put the data into a single array and it’s automatically duplicated/populated into the «INSERT INTO .. ON DUPLICATE UPDATE .. » query.
It’s great for maintainability, and if you want you can make it a function / method too.
// save to db: $qData = [ "id" => mysql_real_escape_string($email_id), "synd_id" => mysql_real_escape_string($synd_id), "campaign_id" => mysql_real_escape_string($campaign_id), "event_id" => mysql_real_escape_string($event_id), "user_id" => mysql_real_escape_string($user_id), "campaign_name" => mysql_real_escape_string($campaign_name), "subject" => mysql_real_escape_string($subject), "from_name"=> mysql_real_escape_string($from_name), "from_email"=> mysql_real_escape_string($from), "content"=> mysql_real_escape_string($html), "link_to_template" => mysql_real_escape_string($hash), "ext_campaign_id" => mysql_real_escape_string($ext_campaign_id), "ext_list_id"=> mysql_real_escape_string($ext_list_id), ]; $q = "INSERT INTO email_campaigns (". // i.e create a string like `id`, `synd_id`, `campaign_id`.. with linebreaks for readability implode(", \n", array_map(function($k)< return "`$k`"; >, array_keys($qData))) .") VALUES (". // i.e '20', '532', '600' .. implode(", \n", array_map(function($v)< return "'$v'"; >, array_values($qData))) ." ) ON DUPLICATE KEY UPDATE ". // i.e `synd_id`='532', `campaign_id`='600' . // id & link_to_template table keys are excluded based on the array below implode(", \n", array_filter(array_map(function($k, $v)< if(!in_array($k, ['id', 'link_to_template']) ) return "`$k`='$v'" ; >, array_keys($qData), array_values($qData)))) ; PHP MySQL Check for duplicate entry?
How can I check if there already is a duplicate entry in the database? I don’t want to prevent it, just a warning. So if it already exists it simply gives a warning, it’s up to the user to ignore it or not.
Thanks in advance!
A bit confused, are you trying to see if the record exists than don’t run the insert command, or do you want to prevent duplicate entries by using keys and other constraints in mysql?
Better use the UNIQUE constraint to forbid duplicate values: “A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL .”
No I just want a check, if it already exists it gives a warning, the user CAN ignore it, but can also cancel it.
5 Answers 5
$key_id = 123; $result = mysql_query("SELECT * FROM table WHERE key_id='$key_id'"); $num_rows = mysql_num_rows($result); if ($num_rows) Thanks! It’s that simple, huh. If I understand correctly, it checks if the $result is true/not-null, right?
Actually it checks the number of rows inside $result . If it’s other than zero then you have a duplicate.
I made a friendlier way to handle duplicates for something like a user ID. For example, if a user puts in an id john and that ID already exists, it will automatically be changed to john1. If john1 exists it will automatically be changed to john2, etc.
// Force the while statement to execute once $usercount = 1; // The number to append to the duplicate value $incrementcount = 0; // Store the original value $origuserid = $userid; while ($usercount != 0) < // Query the database for the current ID $query = "SELECT COUNT(*) FROM userlist WHERE userid = '$userid'"; if ($stmt = $con->prepare($query)) < $stmt->execute(); $stmt->close(); $stmt->bind_result($usercount); $stmt->fetch(); > else < die('Query error'); >if ($usercount != 0) < // if count is anything other than zero, it's a duplicate entry $incrementcount++; // value to append $userid = $origuserid . $incrementcount; // append value to original user id // the while loop will execute again and keep incrementing the value appended // to the ID until the value is unique >> Another option is to use a SELECT COUNT() query which will tell you the number of duplications instead of just checking for any rows returned.
SELECT COUNT(*) FROM table WHERE field = ‘$field’
If you use the num rows method, it will be more efficient if you don’t select all fields when you aren’t going to use them — don’t make the MySQL engine work harder than it needs to.
I realize this is an OLD post, but i had errors with the selected answer, as it would always return true when it was false, copied exactly from the answer above, so i wrote a slight variation of it.
function checkValid($ipCheck) < $con=mysqli_connect("localhost","******","******","$DB_NAME"); $result = mysqli_query($con,"SELECT * FROM $TBL_NAME"); $end = true; while($row = mysqli_fetch_array($result) && $end) < if(strcmp($row['IP'],$ipCheck) == 0) $end = false; >return $end; > What this does, is it scans all until it reaches a false statement. If it doesn’t, then it will remain true, meaning no duplicates, and move on, otherwise, it finds a duplicate and returns false, and triggers the counter statement.
So Assume i am trying to compare IP’s from my database, and the column is IP:
if (!empty($_SERVER['HTTP_CLIENT_IP']))< $ip=$_SERVER['HTTP_CLIENT_IP']; //Is it a proxy address >elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))< $ip=$_SERVER['HTTP_X_FORWARDED_FOR']; >else < $ip=$_SERVER['REMOTE_ADDR']; >if(checkValid($ip)) < $sql = "INSERT INTO $TBL_NAME (IP) VALUES ('$ip')"; if (!mysqli_query($con,$sql)) < die('Error: ' . mysqli_error($con)); >> else echo "IP Already Exists In Database"; I am extremely new to PHP, but this works just fine for my purposes, and it doesn’t seem to affect my speed when loading the page.