How do I add data to different tables but keep the same id?

Question

What I am trying to do is to add data for a product into a database with MySQL 5.1 and PHP 5.2.12.

I have a table for general fields: name, description, price, number of times purchased, and also I have a table for the images that are associated with that product.

The goal is to have the same id for every image that corresponds to its product. For example, if I add the product to the main table, and its ID gets set to 128, then I want all of its pictures in the picture table to have the id 128.

How do I do this?

Answer 1

Create a new primary key for your images (an auto incrementing field for example) but also use a foreign key to reference the product table. Insert as follows:

 INSERT INTO Images (product_id, ....) VALUES (128, ...)

Answer 2

The image should have an (probably auto-increment) id of its own, entirely independent from the product it is associated with.

The association to the product should bedefined in a separate column (say, named "product_id").

This works only if an image can be associated to one product only. If an image can have multiple associations, it's worthwhile to create an associations table like so:

id | image_id | product_id

this way, any image can be associated to any number of products, possibly reducing overhead.

Answer 3

You want to use mysql_insert_id(); to get back the autonumbered ID of what you just inserted. You'll then want to use that ID in a link table that links pictures to products.

Answer 4

Create a third table called ProductImage.

ID, ProductID, ImageID

Use this table to store the relationship. You don't really need the ID in this table, but you might find a use for the ID at a later stage. ProductImageID is a unique identifier for that product/image.

This allows each picture to represent more than one product.