thara Posted December 20, 2015 Share Posted December 20, 2015 I am having a mysql table named `company_profile`. It may have only one record. So I tried to insert and update data of the table using `INSERT.... ON DUPLICATE KEY UPDATE` query.My query is something like this: $sql = "INSERT INTO company_profile ( company_name , tel , mobile , fax , email ) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE company_name= VALUES(company_name) , tel = VALUES(tel) , mobile = VALUES(mobile) , fax = VALUES(fax) , email = VALUES(email)"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('sssss', $company_name , $telephone , $mobile , $fax , $email ); $stmt->execute(); My problem is when I updating the data, it always inserting a new record into my table.Can anybody tell me what would be the problem of this?My table structure looks like this: CREATE TABLE IF NOT EXISTS company_profile ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_name VARCHAR(120) NOT NULL, tel VARCHAR(20) NOT NULL, mobile VARCHAR(20) NOT NULL, fax VARCHAR(20) DEFAULT NULL, email VARCHAR(60) NOT NULL, PRIMARY KEY (id), UNIQUE KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Quote Link to comment Share on other sites More sharing options...
Barand Posted December 20, 2015 Share Posted December 20, 2015 (edited) You aren't inserting the id into the new record so you are not attempting to create a duplicate Edited December 20, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
thara Posted December 20, 2015 Author Share Posted December 20, 2015 @Barand, I tried it something like this: $sql = "INSERT INTO company_profile ( id , company_name , tel , mobile , fax , email ) VALUES (1, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE company_name= VALUES(company_name) , tel = VALUES(tel) , mobile = VALUES(mobile) , fax = VALUES(fax) , email = VALUES(email)"; But still inserting new record... Quote Link to comment Share on other sites More sharing options...
Barand Posted December 20, 2015 Share Posted December 20, 2015 Did you already have a record with an id of "1"? The id would be the id of the record to be updated, not 1 every time. Quote Link to comment Share on other sites More sharing options...
thara Posted December 20, 2015 Author Share Posted December 20, 2015 Sir, there should be only one record set in the table. Thats why I set 1 for id. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 20, 2015 Share Posted December 20, 2015 So what are the ids of the records now in your table? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 20, 2015 Share Posted December 20, 2015 If there should only ever be a single record in that table why have an auto_incrementing id why even attempt to insert a new record? Just update the existing one Quote Link to comment Share on other sites More sharing options...
thara Posted December 21, 2015 Author Share Posted December 21, 2015 Yes sir, it is not needed auto_increment id, so I changed my table structure got it to work. At first, when using these data, it should be insert and after inserting it should be use update. Thats why I tried to used INSERT.... ON DUPLICATE KEY UPDATE query. Sir, can we get it to work adding an unique key to "company_name" column, like this UNIQUE KEY (company_name) Quote Link to comment Share on other sites More sharing options...
Barand Posted December 21, 2015 Share Posted December 21, 2015 As long as your existing id is "1" then the version you had in #3 should work. (It worked for me) Quote Link to comment Share on other sites More sharing options...
thara Posted December 21, 2015 Author Share Posted December 21, 2015 Yes sir, I got it to work. In #8 I asked other method can we use. isn't it? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 21, 2015 Share Posted December 21, 2015 Seems a pointless question but, yes, so long as you don't want to change the company name. Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 21, 2015 Share Posted December 21, 2015 thara, you are missing the point. There was nothing, specifically, wrong with the query you were using in post #3. What Barand was asking was if the existing record actually had an id of '1'. Your response was that there should only ever be one record. But, that doesn't mean that record will have an id of 1. Since it is an auto-increment value, the first record would normally have an id of 1 if not specified in the INSERT statement. But, that id could have been specifically set to another value in the INSERT statement, changed later, or if you deleted the first record and created a new one (which could happen while testing/debugging) the id could be anything. So, to answer your question in #10 - which is the same question you asked in #1 and #3, you can use ON DUPLICATE KEY UPDATE using any field as a unique key as long as you include the value of that field in the query and the value is a duplicate of an existing record. Changing from id to company_name makes no difference. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.