Wagtail Posted March 10, 2014 Share Posted March 10, 2014 Hello everyone, please be so kind and help me with the following query. I have an intermediary Hotels_features table into which I'm inserting some values. How can I prevent the insertion of duplicate values? I can't have the same features_id be associated with the same hotel_id multiple times. I think one can use "on duplicate key update" but I'm not sure how to integrate this with the INSERT INTO query. An example query: INSERT INTO `Hotels_features` (`hotel_id`, `features_id`) VALUES ( 2,7), (2, 2), (2, 4) Thank you very much! Link to comment Share on other sites More sharing options...
Larry Posted March 10, 2014 Share Posted March 10, 2014 You can prevent duplicate keys by making the combination of hotel_id and features_id the primary key or just a unique index. Not sure what you'd want to do on duplicate key, though. That's used to change one of the values attempting to be inserted, which I wouldn't think you'd want to do here. Link to comment Share on other sites More sharing options...
HartleySan Posted March 10, 2014 Share Posted March 10, 2014 Wagtail, given your DB design, duplicate values should never be assigned to the table to begin with. Link to comment Share on other sites More sharing options...
Wagtail Posted March 10, 2014 Author Share Posted March 10, 2014 Hi Larry and HartleySan, thank you for your help. I've made the combination of hotel_id and features_id the primary key. Wagtail, given your DB design, duplicate values should never be assigned to the table to begin with. No, I am trying to prevent duplicate values being inserted into the table. What I have done is create checkboxes for all of the features. If 'le grand hotel' has 5 features, then those 5 checkboxes will already be checked. If I now check 2 more checkboxes, I'll have 7 checkboxes checked, but only 2 checkbox values must be inserted into the table (the other 5 checkbox values are already present in the intermediary table). What would be nice is if the query could also delete those rows when the associated checkboxes are "unchecked". Does this make sense? Thanks! Link to comment Share on other sites More sharing options...
HartleySan Posted March 10, 2014 Share Posted March 10, 2014 This is a common problem with a SQL answer already built in: http://stackoverflow.com/questions/16338578/avoid-duplicates-of-unique-key-within-insert-query Link to comment Share on other sites More sharing options...
Wagtail Posted March 11, 2014 Author Share Posted March 11, 2014 HartleySan, thank you for the link. The query is working well now - no duplicate values are being inserted into the table. Now I'm trying to delete certain rows but no luck so far. $Q = "DELETE FROM Hotels_features WHERE features_id = 3 AND hotel_id = 1"; Nothing seems to happen, although the query does work in PHPmyAdmin. Must I first run a SELECT query? Hope you are well. Thank you! Link to comment Share on other sites More sharing options...
HartleySan Posted March 11, 2014 Share Posted March 11, 2014 If it works in phpMyAdmin, but not your PHP code, then your PHP code is obviously the problem. Make sure that you're executing all the proper functions for executing queries. Link to comment Share on other sites More sharing options...
Wagtail Posted March 11, 2014 Author Share Posted March 11, 2014 The DELETE query doesn't work because I have a second query that INSERTS values into the same table. If I remove the latter query, then the former query will work. In my code I first check if ($_SERVER['REQUEST_METHOD'] == 'POST'), then I have the code for the DELETE query, followed by the code for the INSERT query. Do you think that this should work? I'm trying to figure this out. Thank you. Link to comment Share on other sites More sharing options...
Wagtail Posted March 11, 2014 Author Share Posted March 11, 2014 Never mind! It does work after all. Thanks. Link to comment Share on other sites More sharing options...
Recommended Posts