Jesse Alford Jesse Alford - 5 months ago 7
MySQL Question

Using PHP how do I retrieve a certain field value from a database table and then input that single field value into a new table

This is something I've tried... I'm using a select statement to get the id from the parent table where the email is equal to the email provided by the user. After getting the id from the parent table I want to set this id to the parent_id field in the child table to a field value in a child table.

For instance, my parent table has id, first_name, last_name, address, phone, email, picture_approval and date. Then the child table has id, child_first_name, child_last_name, and parent_id

// already connected to database successfully
{
try
{
$sql = 'INSERT INTO parents SET
first_name = :fname,
last_name = :lname,
address = :address,
phone = :phone,
email = :email,
picture_approval = :picture,
date = NOW()';

$s = $pdo->prepare($sql);
$s->bindValue(':fname', $_POST['g_first_name']);
$s->bindValue(':lname', $_POST['g_last_name']);
$s->bindValue(':address', $_POST['address']);
$s->bindValue(':phone', $_POST['phone']);
$s->bindValue(':email', $_POST['email']);
$s->bindValue(':picture', $_POST['pictures']);
$s->execute();
}
catch (PDOException $e)
{
$errors[] = 'Error adding parent to database.';
}
}
//the above code works perfectly


But then this next part never works.

try
{
$sql = "SELECT id FROM parents WHERE email = :email";
$s = $pdo->prepare($sql);
$s->bindValue(':email', $_POST['email']);
$s->execute();
$s->bind_result($returned_id);

$s->fetch();

$fieldValue = "$returned_id";


}
catch (PDOException $e)
{
$errors[] = 'Error retrieving parent.';
}


Then I want to set up my INSERT INTO child statement... but not really sure how since my SELECT statement does not appear to be working right.
I did find this PHP function to extract a field value from a database but it only outputs the value, I need it to put it into another table.

Answer

Instead of manually looking for the ID, use lastInsertId().

$s->execute();
$returned_id = $pdo->lastInsertId();

Then you can use it as you need it.

Comments