php - MYSQL storing multiple images

Question

I have a web page where users can upload articles with images on them. There is not limit on the amount of images a user can upload with their article. Each row in a MySQL database represents one article of many. What is the best way to store all these images. I know I would use BLOBS/LONGBLOBS but if I have no control over the amount of images a user uploads I can't just insert 50 columns for different images and hope they upload less than 50. What is the best way to do this.

Answer 1

The best way to do this is to create a table with images that references the article so it will leave you with a database like this:

The table creation script for Images would be like this:

CREATE TABLE `Images` (
    `ImageID` bigint NOT NULL AUTO_INCREMENT,
    `ArticleID` bigint NOT NULL,
    `Image` longblob NOT NULL,
    PRIMARY KEY (`ImageID`)
);
ALTER TABLE `Images` ADD CONSTRAINT `FK_Images` FOREIGN KEY (`ArticleID`) REFERENCES `Articles`(`ArticleID`);

Now all you have to do is insert an image into Images together with the ArticleID it belongs to.