My question is at a high risk of being a duplicated one, but that is not for lack of investigation. I've been around with this issue for long, and found nothing to solve it, but it seems such an obvious and common problem, that somebody must have answered this already.
The thing is, i have two related tables, say product and product_images, where product_images refers to product with the field product_id.
In the CRUD (a web app written in php with codeigniter and MySQL as db) i have a form with the product data, and a button for adding images from a pop up.
Question is: is there an elegant/efficient way to add an image to a product when I am CREATING the product (and there is still no product_id)? Or do i have to save the product first and then add the images on the product edit?
Thanks a lot for any idea! I feel very dumb with this problem.

  • Is your question more about the mechanics of inserting related rows i.e. in a single transaction, or, more about the user interface and workflow of entering products, images, and relating them and so how to present that to the user? – Erik Eidt Jun 3 '17 at 23:03
  • Hi Erik I guess i can't separate those two aspects. If there is a clear and robust mechanism, maybe involving transactions, that would allow a cleaner an simpler workflow and interface, right? And viceversa, if there isn't, one should find the best workflow in the UI to overcome that lack of such mechanism. But i really don't know. What could that mechanism be? – tebastian Jun 3 '17 at 23:45

I'd suggest you start with the domain experience that your users will have.

From a technical, data modeling point of view, you might have Products, Images, and Associations between them. But do you want your users to be able to — or to have to — manage these 3 things separately or do you want a simplified user experience where the user adds Products with Images, and perhaps from time to time adds more images to products. Anyway, that's a domain decision, not a technical one.

And, sorry, I don't know codeigniter, though, broadly speaking, you have to insert a row into a table before you can get/use its id in an association of some sort.

However, various databases will allow you to see the id after insert yet still within the transaction, so you can insert other rows (in another table that requires the former id as foreign key) in the same commit (i.e. in one round trip to the db), which is about as efficient as you'll get.

For example: MySQL; SQL Server; Oracle.

There is apparently also some support in PHP for getting the last id $conn->insert_id, see more here.

Search phrases "id of last insert"; "identity of last inserted row".

|improve this answer|||||
  • Thanks Erik. About the first part of your answer, it is precisely the idea behind my original question. I want the user to add the product with some images and, as you said, they can later edit to add/delete some more images. I know how to get the last inserted ID from the db, codeigniter has db->insert_id() for that. But what i don't know has to do with this workflow-> you click a "new product" button -> the form appears -> you enter the product name, etc -> in that form, you click on "new image" -> the pop up shows -> you upload the img -> There! What do you do with the image at that point? – tebastian Jun 4 '17 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.