Jump to content
Larry Ullman's Book Forums
nick

Data Insert In To Multiple Tables

Recommended Posts

I want to use the database design from the first example in the book but I want to use the admin portion to add multiple pictures. I am going to add a separate table to the database in order to customize it for my use. My question is, When I add a new record in the admin portion and upload portion to pages information portions to the picture table is there a way to upload them with multiple locations or will I have to use to queries and will the same principle apply when I am deleting that page and the pictures. I just wanted to make sure this was an acceptable practice and if so if any one has an example that would be awesome

Share this post


Link to post
Share on other sites

nick, I'm confused by what you're asking for.

 

However, I'm thinking that if you have a properly normalized DB across several tables, then updating one table should automatically take into account all other tables.

 

With that said, you're going to more or less have to upload each photo separately. You could set up multiple upload fields on one page, similar to how Gmail handles attaching multiple files to one email, but there is really no getting around that.

 

Anyway, if you have the chance, please clarify what you want. Thank you.

  • Upvote 1

Share this post


Link to post
Share on other sites
My question is, When I add a new record in the admin portion and upload portion to pages information portions to the picture table is there a way to upload them with multiple locations or will I have to use to queries and will the same principle apply when I am deleting that page and the pictures. I just wanted to make sure this was an acceptable practice and if so if any one has an example that would be awesome

 

I think I understand what you want to do! Are you saying that you want to perform an insert into 2 tables at the same time?

 

There are a couple of solutions to uploading to two tables at the same time.

 

1) Perform 2 separate INSERT queries using standard sql.

2) Use a stored procedure.

 

The added benefit of using stored procedures is that if any part of the query fails, then the whole thing can be rolled back.

 

Is this what you want to do?

 

Matt

  • Upvote 1

Share this post


Link to post
Share on other sites

Of course, the key is that if it's possible to do everything in one query instead of two, it'll probably be better to go with one query, but that depends a lot on how your database is designed.

Share this post


Link to post
Share on other sites

I think I understand what you want to do! Are you saying that you want to perform an insert into 2 tables at the same time?

 

There are a couple of solutions to uploading to two tables at the same time.

 

1) Perform 2 separate INSERT queries using standard sql.

2) Use a stored procedure.

 

The added benefit of using stored procedures is that if any part of the query fails, then the whole thing can be rolled back.

 

Is this what you want to do?

 

Matt

 

 

something like this is what I had in mind

mysqli_query("INSERT INTO table1 (columnA,columnB) VALUES ('something',123)");

mysqli_query("INSERT INTO table2 (columnX,columnY) VALUES ('foo','bar')");

 

this would have information for both tables placed split up and put into separate tables maybe with one identifier for both

Share this post


Link to post
Share on other sites

And my question still remains: Why do you need to insert data into two separate tables? I apologize, as perhaps I'm not thinking outside of the box here, but for any given PHP page/operation, I can't imagine why you'd need to perform an insert operation into multiple tables, assuming that your tables are properly normalized.

 

Again, I apologize for my ignorance here. If you could please better explain the situation, it would be much appreciated.

Share this post


Link to post
Share on other sites

And my question still remains: Why do you need to insert data into two separate tables? I apologize, as perhaps I'm not thinking outside of the box here, but for any given PHP page/operation, I can't imagine why you'd need to perform an insert operation into multiple tables, assuming that your tables are properly normalized.

 

Again, I apologize for my ignorance here. If you could please better explain the situation, it would be much appreciated.

the reason being is I will have 5 to 8 pictures and an identifier . I will want another table for the info describing the pictures with a unique identifier because i can't or I don't think I should have have a description with 5 to 8 pic it would be redundant. I hope that helps explain a little better.

Share this post


Link to post
Share on other sites
And my question still remains: Why do you need to insert data into two separate tables?

 

HartleySan, I apologize for interjecting here, but why are you questioning his logic? Nick obviously knows what he's doing! This situation happens all the time and has nothing to do with proper normalization! If anything, normalization (which I am in no way saying shouldn't be done) can exacerbate the problem. I dealt with a similar situation recently with my gallery creation pages where I was going to insert data into the "galleries" table and "profiles" table after the user had gone through a process of filling out a form for both!

Share this post


Link to post
Share on other sites

Well, now that he has clarified the situation, I understand and agree (to a degree).

 

With that said, I have seen countless examples of people not properly normalizing their tables, and it causing issues with having to perform more queries than is necessary. I just wanted to make sure that wasn't the case.

 

So, nick, I am sorry for doubting you, but this is the first time I've seen you post on this boards, and I just wanted to make sure you weren't the proverbial "newb". Again, I apologize, but it was hard for me to understand your situation, which was essential for helping answer your question.

 

Anyway, I'm still going to fire back with the following question: If you are uploading multiple images, assuming they're not the same, why would the descriptions be the same? Also, I've gotten in this discussion with multiple people recently, but I think that even if a description can be similar/the same as another, as long as the descriptions are not dependent on each other, I see no reason why not to include that information in the same table as the other image info.

 

And if that's the case, you only need one table and one query. Well, just my two cents. I'm open for debate.

Share this post


Link to post
Share on other sites

The specifics of what Nick is doing still aren't clear, as far as I can tell, but HartleySan, say you have a product in an ecommerce database and you have multiple images associated with that product. All of the product particulars--description, id, price, etc.--would go in the one table and records reflecting each associated image (and each product could have from 1 to ??? images) would go in a second.

Share this post


Link to post
Share on other sites

I apologize guys for not being as clear as possible. I am working on a site for a car lot. I will need the initial image of the car and several others inside. I want to insert several images 1 which will show like in a list of products and the other would pictures would be for like a details page which have the initial image plus the remaining images and longer descriptions. I just wanted to make sure that using 2 queries would be okay and safe or an okay practice since I had never done this before with multiple queries. I appreciate you all taking time to help.

Share this post


Link to post
Share on other sites

Although, as HartleySan said, it's best to use a single query when you can, you can't always. In those situations, multiple queries are fine (in fact, the only option).

Share this post


Link to post
Share on other sites
this is the first time I've seen you post on this boards, and I just wanted to make sure you weren't the proverbial "newb".

Sorry to attack you earlier HartleySan, but I am just really careful about questioning people's background knowledge, especially since the MikeMikeMike incident (and Mike made it clear in his posts that he was having a hard time with some of the more fundamental concepts). I was new to this forum a couple months back and got a bit of the same "newb" treatment, and I have an IT degree and have read several of Larry's books!

 

Anyway, page 265 of the E-Commerce book has an excellent example of a multiple INSERT stored proceedure - HartleySan, I do have Larry's book in front of me right now ;)

Share this post


Link to post
Share on other sites

All right. Thank you all for showing me the error of my ways. After thinking about it more carefully, I suppose multiple queries are sometimes an inevitable thing.

 

I have a question for Larry though: Given nick's situation, how would you organize the database? Would you have a separate table for images, and each image would be its own record? And then, you'd link all the foreign keys of images associated with a particular car to that car in the cars table? To me, that seems sloppy, as you'd have to have a comma-separated list of foreign keys in a field, wouldn't you? Well, I really don't know the best way to handle this, so would like to hear your opinion.

 

Thank you.

 

Edit: As another note, using stored procedures or prepared statements would help speed things up greatly. Also, I learned something interesting the other day. There are prepared statements in Microsoft's ASP, but they don't call them that, they call them "parameterized queries". Ugghh!

Share this post


Link to post
Share on other sites
I have a question for Larry though: Given nick's situation, how would you organize the database? Would you have a separate table for images, and each image would be its own record? And then, you'd link all the foreign keys of images associated with a particular car to that car in the cars table? To me, that seems sloppy, as you'd have to have a comma-separated list of foreign keys in a field, wouldn't you? Well, I really don't know the best way to handle this, so would like to hear your opinion.

 

Exactly, except not. You create one table for the cars and one table of images. You don't put FK's of the images in the cars table but rather put a FK of the car associated with each image in the images table. This is a totally logical, common, and appropriate solution.

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...