1

I have 2 tables, users and syssla where syssla_id is a foreign key in users(syssla_id2).

create table syssla(                                    
    syssla_id int auto_increment,
    Uppgift varchar (255),
    Information text,
    Tid varchar(100),
    primary key(syssla_id)
)engine=innodb;

create table users(                                 
    user_id int ,
    username varchar (50),
    user_password varchar (50),
    Namn varchar(100),
    syssla_id2 int,
    primary key(user_id),
    foreign key(syssla_id2) references syssla(syssla_id)
)engine=innodb;

I want to do an insert with a form in php. But how can I do to get the foreign key to be inserted to a specific user in the other table(users) at the same time?

Tirth Patel
  • 5,443
  • 3
  • 27
  • 39
Monk007
  • 19
  • 4
  • 2
    Possible duplicate of [How do I get the last inserted ID of a MySQL table in PHP?](http://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php) – cmnardi Apr 04 '16 at 18:17

2 Answers2

0

You should write a stored procedure that inserts first into syssla and then get the id using LAST_INSERT_ID() function to then insert new record into users

Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
0

You do the following:

// you start a mysql transaction
$mysqli->begin_transaction();
// execute your first query
$mysqli->query(""); // Insert query here
$new_id = $mysqli->insert_id;

$mysqli->query(""); // second query here using the $new_id

$mysqli->commit();

$mysqli->close();

If for whatever reason the second query fails it will cancel the first query and it will be like nothing ever happened, also starting a transaction prevents other queries from overwriting you last inserted id. so you will never get the wrong id by some other query being executed by a different user.

ps. try and use prepared statements, they protect you better against sql injection than escaping variables manually

Jester
  • 1,408
  • 1
  • 9
  • 21