Get row id from multiple query without using transactions

Question

I'm creating social network site and И have wall where user post will be fetched. When user create new post they have options to attach photos. Post and attachment have diferent database tables and diferent Controllers and Models.

Problem is when i execute query for insert new post И need one more query for attachment witch will hold post_id. So first I need insert post in database then И need to insert attachments for that post? But how to get id for that post when all query is executed in some time?

Lets see my tables:

Post

post_id  account_id  post_text       
-------  ----------  -----------------------
1          1         Hello world post

Attachments

attachment_id  post_id  attachment_type  attachment_name  
-------------  -------  ---------------  -----------------
1              1          3               photo_1.jpg
2              1          2               video_1.mp4
3              1          3               photo_2.jpg

I dont want use transactions for this.

On submit I need to insert :

INSERT INTO post bla bla bla
INSERT INTO attachments bla bla bla <post_id> //how to get it when is executed in some time?

here is simple example of get `last insert id` : http://www.w3schools.com/php/php_mysql_insert_lastid.asp — Hardik Solanki, Dec 14 '15 at 11:28
I try with that. Is not that a little bad way because it is a large amount of information and some 12,000 profiles? — Ivan, Dec 14 '15 at 11:29
Please show your code? as you mentioned in two lines is not clear. — rahul, Dec 14 '15 at 11:30
I only have crud i need logic for controler how to get that id from post and insert it. — Ivan, Dec 14 '15 at 11:31
Possible duplicate of [CodeIgniter activerecord, retrieve last insert id?](http://stackoverflow.com/questions/1985967/codeigniter-activerecord-retrieve-last-insert-id) — Shadow, Dec 14 '15 at 11:34

score 2 · Answer 1 · edited Dec 14 '15 at 12:40

2

If you are using codeigniter there must be some option to get the inserted id i used in this way before......

$this->db->insert('posts', $post_data);
$insert_id = $this->db->insert_id();

edited Dec 14 '15 at 12:40

Veerendra

2,562
2
22
39

answered Dec 14 '15 at 11:33

rahul

776
5
30

Yes this work good. But what if i dont have attachment? Am getting all time new attachment row whith last id – Ivan Dec 14 '15 at 16:35

score 0 · Answer 2 · answered Dec 14 '15 at 11:36

You have to get the last inserted id from post table and then insert into attachments table with reference id

INSERT INTO post (<FIELDS>) VALUES (<VALUES)

$pdo->insert('posts', $yourData);
$lastInsertId = $pdo->lastInsertId();

Then you have to insert into attachments

INSERT INTO attachments (<FIELDS>) VALUES (<VALUES)

Get row id from multiple query without using transactions

2 Answers2