David John Posted October 31, 2014 Share Posted October 31, 2014 Hi, everyone. There's a project I'm working on, and I would love to get a bit of help from developers who visit this forum. Here's the background: This is an app that will allow users to post comments/reviews about hotels that they stay in. I decided to make two tables: hotels and information (for the comments). hotels table: CREATE TABLE IF NOT EXISTS `hotels` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `airportDesignator` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `hotelName` varchar(255) NOT NULL, `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `city` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `state` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `zip` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73 ; information table: CREATE TABLE IF NOT EXISTS `information` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `hotels_id` int(10) unsigned NOT NULL, `comments` blob, `ratings` int(10) DEFAULT NULL, `date_created` date NOT NULL, PRIMARY KEY (`id`), KEY `hotels_id` (`hotels_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ; To get the hotels, here's the query I used: $sql = "SELECT * FROM hotels WHERE hotels.id=:id"; To add the comments, I used this query: $sql = "INSERT INTO information (hotels_id, comments, ratings, date_created) (SELECT hotels_id, comments, ratings, date_created FROM information LEFT JOIN hotels ON hotels.id = information.hotels_id)"; The hotels_id is inserting a "0" instead of the id from the hotels table, and I would love to know how to get the correct id (from the hotels table) inserted into the information table. Does hotels_id need to be a foreign key in order for the INSERT statement to work properly, or do I need to change the SQL INSERT query? If anyone needs further clarity, please let me know. Any feedback would be greatly appreciated. Link to comment Share on other sites More sharing options...
HartleySan Posted October 31, 2014 Share Posted October 31, 2014 Where is the VALUES keyword between your sets of parentheses for the INSERT statement? Also, in your subquery, why are you selecting info from the table you are inserting into and then re-inserting it? I'm a bit confused about what you are looking to accomplish here. As far as I can tell, so long as you have the hotel ID, the comment text and the rating, you shouldn't require a subquery within your INSERT statement. Link to comment Share on other sites More sharing options...
David John Posted October 31, 2014 Author Share Posted October 31, 2014 Sorry for being unclear, HartleySan. Please correct me if I'm wrong, but doesn't INSERT INTO SELECT replace the use of VALUES? I could be mistaken, but I thought this was the case. To illustrate an example of my objective, say a user wants to comment on/rate a hotel in Albany whose id = 1 in the hotels table. The GET query grabs the hotel id, the user enters his/her comments, and submits. What's inserted into the information table is the value of Albany (1) into the "hotels_id" column as well as the user's comments in the "comments" column in the information table. In addition, I hope to eventually add "ratings" and "date created". I realize now that my SQL query was way off. Thanks for pointing that out! I keep getting a parse error with this: $sql = "INSERT INTO information (hotels_id, comments, ratings) VALUES (:id, :comments, :ratings) WHERE hotels.id=:id"; Link to comment Share on other sites More sharing options...
HartleySan Posted October 31, 2014 Share Posted October 31, 2014 Yeah, you might be right about the INSERT INTO SELECT syntax. I actually haven't used a subquery like that since my college course on DBs. In other words, I've never used a query like that in the real world. If you already have the hotel ID and comments, etc. though, I would just do a regular insert like follows: INSERT INTO information (hotels_id, comments, ratings, date_created) VALUES ($hotel_id, '$comments', '$ratings', NOW()); Link to comment Share on other sites More sharing options...
Larry Posted October 31, 2014 Share Posted October 31, 2014 I do use INSERT INTO SELECT syntax, but I wouldn't use it here. This is a simple INSERT into "information", and you shouldn't need to select the hotel ID, as the form for submitting the comment should have that (as HartleySan is saying). Link to comment Share on other sites More sharing options...
David John Posted November 1, 2014 Author Share Posted November 1, 2014 It works now (thank God!) As usual, many thanks to HartleySan and Larry for your input. Next question: I am using "airportDesignator" (from the hotels table) in the "hotels_id" column (from the information table) when the comments are submitted. Does this violate 1NF/2NF/3NF or compromise referential integrity at all? Link to comment Share on other sites More sharing options...
Larry Posted November 6, 2014 Share Posted November 6, 2014 Isn't a comment reflective of the hotel, not the airport? If so, you should use the hotel ID in the comments table. Link to comment Share on other sites More sharing options...
David John Posted November 17, 2014 Author Share Posted November 17, 2014 Got it. Thanks, Larry! Link to comment Share on other sites More sharing options...
Recommended Posts