1

I have two table one is photos and another one is photo_detail, this is how i have created both tables:

/* creating photos table */
CREATE TABLE photos(
->  pid INT(10) NOT NULL AUTO_INCREMENT,
->  purl VARCHAR(200) NOT NULL,
->  uid INT(5) NOT NULL,
->  time TIMESTAMP NOT NULL,
->  title VARCHAR(80) NOT NULL,
->  PRIMARY KEY (pid)
->  )
->  ENGINE = InnoDB DEFAULT CHARSET = utf8;

/* creating photo_detail table */

CREATE TABLE photodetail(
->  pid INT(6) NOT NULL,
->  ptext TEXT NOT NULL,
->  location VARCHAR(40) NOT NULL,
->  FOREIGN KEY (pid) REFERENCES photos(pid)
->  )
->  ENGINE = Innodb DEFAULT CHARSET = utf8;

My question is how i can add the automatically incremented ID from photos table to photo_detail table while storing the photo? i am using PHP language! any suggestion on this table design?

1 Answer 1

2

You can use LAST_INSERT_ID() to get the inserted id for example if you insert a row in your photos table and you need that auto incremented id for the detail table you can do so

INSERT INTO photos (purl, uid,`time`,title)
VALUES ( 'test',1,1111,'test');
INSERT INTO photodetail (pid, ptext, location)
VALUES (LAST_INSERT_ID(), 'test', 'test');

Fiddle Demo

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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