So I have 2 tables:
users
with columnsid
(primary, auto_increment),username
,password
,person_id
(foreign key)- people with columns
id
(primary, auto_increment),first_name
,last_name
What I'm trying to do is when registering a new account have a new row inserted into people
and then have a new row inserted into users
with the people.id
as foreign key users.person_id
.
Right now I have 2 php functions that get executed right after eachother, firstly one with this query:
insert into people (first_name, last_name) values (:firstname, :lastname)
Secondly one with this query:
insert into users (username, password, person_id) values (:user, :pass, LAST_INSERT_ID())
All of this works fine except for the fact that last_insert_id()
keeps giving value 0
instead of the id from the previous query. Is it maybe not possible to use last_insert_id()
when using 2 separate queries? If so what would be the best way to go about it then?
This is my relevant php code:
//make new person
$newPerson = new PeopleManagement();
$pm = $newPerson->createNewPerson($_POST["firstName"], $_POST["lastName"]);
//make new user
$newUsr = new Authentication();
$ac = $newUsr->registerNewUser($_POST["user"], $_POST["pass"]);
public function registerNewUser ($user, $pass) {
try {
$dbm = new PDO(DBCONFIG::$db_conn, DBCONFIG::$db_user, DBCONFIG::$db_pass);
$dbm->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbm->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
hash = password_hash($pass, PASSWORD_DEFAULT);
$sql = "insert into users (username, password, person_id) values (:user, :pass, LAST_INSERT_ID())";
$stmt = $dbm->prepare($sql);
$stmt->execute(array(
':user' => $user,
':pass' => $hash
));
$dbm = null;
} catch(PDOException $ex) {
return "Could not connect to database";
}
}
public function createNewPerson($firstName, $lastName) {
$dbm = new PDO($this->dbConn, $this->dbUser, $this->dbPass);
$sql = "insert into people (first_name, last_name) values (:firstname, :lastname)";
$stmt = $dbm->prepare($sql);
$stmt->execute(array(
':firstname' => $firstName,
':lastname' => $lastName
));
$dbm = null;
}