Jump to content
Larry Ullman's Book Forums

Recommended Posts

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

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

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

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

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

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

  • 2 weeks later...
 Share

×
×
  • Create New...