CODEWITHSUNDEEP
phpmysqlpdo
Gary Justin
Gary Justin

Reputation: 204

Need to update a second table upon insert to first

I apologize in advance for this code, this is literally my first attempt at PHP and SQL. I have two tables:

table contacts(
contact_id int(10)
contact_first_name varchar(30)
contact_last_name varchar(30)
contact_phone_number varchar(30)
contact_email_address varchar(255)
contact_comments text
contact_date_added datetime
);

table submissions(
submission_id int(10)
contact_id int(10)
submission_date datetime
);

Primary Key for contacts is contact_id Primary Key for submissions is submission_id Foreign Key for submissions is contact_id

User enters simple contact data into a form, form gets parsed and data is inserted into the contacts table. I have this working the way I want to, but for the second table there is nothing to insert from the form and I need it to update with the most recent submission when the insertion to the contacts table is made.

I'm using PDO, this is where I'm at:

class Contact 
{
    public $first_name;
    public $last_name;
    public $phone;
    public $email;
    public $comments;

    function __construct($data)
    {
        $this->first_name = $data['firstName'];
        $this->last_name = $data['lastName'];
        $this->phone = $data['phone'];
        $this->email = $data['email'];
        $this->comments = $data['comments'];
    }
}

$contact = new Contact($_POST);

try
{
    $db_connection = new PDO('mysql:host=localhost:3306;dbname=userdata','root','');
    $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $exception)
{
    $exception->getMessage();
}

$sql = "INSERT INTO contacts(contact_first_name, contact_last_name,     contact_phone_number, contact_email_address, contact_comments) VALUES (:first_name, :last_name, :phone, :email, :comments)";

$query = $db_connection->prepare($sql);

$query->execute(array(':first_name'=> $contact->first_name, ':last_name'=> $contact->last_name, ':phone'=> $contact->phone, ':email'=> $contact->email, ':comments'=>$contact->comments));

Upvotes: 0

Views: 45

Answers (2)

dognose
dognose

Reputation: 20909

You could simply use the lastInsertId method of PDO: http://www.php.net/manual/en/pdo.lastinsertid.php

$query = $db_connection->prepare($sql);
$query->execute(array(':first_name'=> $contact->first_name, ':last_name'=> $contact->last_name, ':phone'=> $contact->phone, ':email'=> $contact->email, ':comments'=>$contact->comments));

$lastId = $db_connection->lastInsertId();

then, you can use $lastId to perform any subsequent insert, while reusing the last created contact_id.

Upvotes: 1

Barmar
Barmar

Reputation: 781708

You can use the following query:

INSERT INTO submissions (contact_id, submission_date)
VALUES (LAST_INSERT_ID(), NOW());

I'm assuming contact_id in the contacts table is auto-incremented. LAST_INSERT_ID() returns the most recent auto-increment ID from an INSERT statement on this database connection.

Upvotes: 1

Related Questions