How to access mysql assigned primary key in other column

Question

I have this mysql query:

insert into table_products values (NULL, '$product_name', '$product_description', '$image_path', '$brand_id', 1)

Since product_id is an auto_increment primary key, passing NULL will assign it the next available integer.

It is the same integer I want to assign to an image path with some pre and post string string.

For example, if the product_id is assigned as 900, I want to assign image_path to 'somestring/' + 900 + '.png' i.e something/900.png

How to get this 900 for the image path column while inserting in to table.

What sort of code are you using to execute this statement? Is it just a sql script or is it in Java or some other programming language? — Jeremy Goodell, Aug 04 '15 at 17:18
Looks a duplicate of http://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query — gogasca, Aug 04 '15 at 17:23

score 1 · Accepted Answer · answered Aug 04 '15 at 17:20

1

You would probably need to do a separate query and update that column with the id that was just inserted, eg:

do your insert:

insert into table_products values (NULL, '$product_name', '$product_description', NULL, '$brand_id', 1)

Then grab the id using mysqli_insert_id or PDO::lastInsertId and set the path string eg:

$id = mysqli_insert_id($connection);
$path = 'somestring/' . $id .'.png';

Then update the previous row with the path:

UPDATE table_products SET path =  '$path' WHERE product_id = $id;

answered Aug 04 '15 at 17:20

Andrew

Thanks for very quick response. Even i thought of this solution. But here for every insert two queries to be executed. Can't we avoid that? – Devesh Agrawal Aug 04 '15 at 17:25
As far as I know you can't access the insert id until after the insert has been completed, forcing the second query, but it is a good question. – Andrew Aug 05 '15 at 16:30

1 Answers1