CODEWITHSUNDEEP
phpmysql
user3871
user3871

Reputation: 12716

PHP and MYSQL: Inserting Foreign Key

I have two tables, IMAGES and CATEGORIES.

CATEGORIES to IMAGES is a 1:M relationship (many images can have 1 category).

I'd like to insert images into a database from the filesystem, dynamically assigning foreign key categories on the way in.

I'd like to achieve this with a single insert, but it appears I need an insert for each category of image, like so:

 $allimages = scandir('./images/all_images/');
 $category1= scandir('./images/category1/');
 $category2= scandir('./images/category2/');

//CATEGORY 1: 
for($x=0; $x<count($category1); $x++)
{   
if(!is_dir(IMAGES_PATH . $category1[$x]))   
{  
  INSERT INTO images (imgid, imgname, categoryfk) VALUES ('$x', '$category1', 1)

//CATEGORY 2: 
for($x=0; $x<count($category2); $x++)
{   
if(!is_dir(IMAGES_PATH . $category2[$x]))   
{  
  INSERT INTO images (imgid, imgname, categoryfk) VALUES ('$x', '$category2', 2)

//CATEGORY 3: 
for($x=0; $x<count($allimages); $x++)
{   
if(!is_dir(IMAGES_PATH . $allimages[$x]))   
{  
  INSERT INTO images (imgid, imgname, categoryfk) VALUES ('$x', '$allimages', 3)

Is this really the only way I can do this? Can I achieve this category assigning in a single insert and loop?

Thank you!

Upvotes: 1

Views: 1305

Answers (2)

StuartLC
StuartLC

Reputation: 107367

In addition, you can also look up foreign keys on the fly by inserting the result of a lookup, e.g. to lookup the Foreign Key for 'Category 1' and then insert a new image referencing this category:

INSERT INTO Images(imgId, imgname, categoryFk)
   SELECT 9876, 'photo1.jpg', cat.categoryId
   FROM Category cat
   WHERE cat.categoryName = 'Category 1';

SQLFiddle here

Upvotes: 3

user330315
user330315

Reputation:

Not sure I understand you correctly, but from what I understand, this should do it:

INSERT INTO images 
   (imgid, imgname, categoryfk) 
VALUES 
   ('$x', '$category1', 1),
   ('$x', '$category2', 2),
   ('$x', '$allimages', 3)

Upvotes: 2

Related Questions