CODEWITHSUNDEEP
phpmysqlsql
John Smith
John Smith

Reputation: 8851

How to insert a row's primary key in to another one of its columns?

I find myself doing this a lot:

mysql_query("INSERT INTO employees (name, age) VALUES ('$name', '$age')");
$id = mysql_insert_id();
mysql_query("UPDATE employees SET pID = $id WHERE id = '$id'");

This is just an example but I often need to include the newly inserted row's ID in to another one of its columns and I am forced to use this cumbersome solution. Is there any other way?

Upvotes: 1

Views: 378

Answers (5)

Ian Wood
Ian Wood

Reputation: 6573

@John Smith - there are differing models of heirarchial data - yourchosen one can be difficult to work with...

have a look at some other methods

Upvotes: 1

Jon Black
Jon Black

Reputation: 16559

You can do it in a single call from php to mysql if you use a stored procedure:

Example calls

call insert_employee('f00',32);
call insert_employee('bar',64);

$sql = sprintf("call insert_employee('%s',%d)", $name, $age);

Script

drop table if exists employees;
create table employees
(
id int unsigned not null auto_increment primary key,
name varchar(32) not null,
age tinyint unsigned not null default 0,
pid int unsigned not null default 0
)
engine=innodb;

drop procedure if exists insert_employee;

delimiter #

create procedure insert_employee
(
in p_name varchar(32),
in p_age tinyint unsigned
)
begin

declare v_id int unsigned default 0;

  insert into employees(name, age) values (p_name, p_age);
  set v_id = last_insert_id();
  update employees set pid = v_id where id = v_id;
end#

delimiter ;

Upvotes: 1

uadnal
uadnal

Reputation: 11445

http://php.net/manual/en/function.mysql-insert-id.php

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

Upvotes: 0

vbence
vbence

Reputation: 20333

Check MySQL's LAST_INSERT_ID() function here. May or may not be what you need depending on your situation.

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191779

Since you don't know what the insert id is beforehand, there is no other way to do this (short of getting the max ID and using it in the insert query, but that's still another query). Why do you need to do it "a lot," though? Create a function that will do just that for you.

The better question is: why do you need the ID to be in two columns in the same table?

Upvotes: 2

Related Questions