How to insert into two separate tables and link them with the same ID?

Question

I have two tables (client and drug) and I've created a form which will insert the information the user enters into these tables. A client can have many previous drug uses (shortened to drug) therefore I made a separate table for multiple drug use and I now need to link them. The client_id is auto-increment and so is the drug_id, however my drug_client_id isn't and I need it to be the same as the client_id (otherwise they won't link). I'm not too sure on how to do this so if anybody can help me out then I'd be highly grateful
I couldn't get it to work on one php file so I had to put the client and drug insert statement things in two different ones; so if you can help me in maybe getting them into only one as well, then I'd be even more grateful haha.
Please see my code below and help me, many thanks.

<html>
<head>
<title> Access Community Trust </title>
</head>
<body>

<form action="addclientwo.html" method="post">

First name: <input type="text" name="first_name">
<br>

Drug use: <input type="text" name="drug1">
<input type="text" name="drug2">
<input type="text" name="drug3">

<input type="submit" value="Submit">
</form>
</body>
</html>


<?php
Require("dbconnect.php");
//establishes connected to database

$first_name = $_POST['first_name'];

$stmt = $dbh->prepare("INSERT INTO client (first_name) VALUES (:first_name)");

$stmt->bindParam(':first_name', $first_name);

$stmt->execute();
?>

<?php
Require("dbconnect.php");

$drug1 = $_POST['drug1'];
$drug2 = $_POST['drug2'];
$drug3 = $_POST['drug3'];

$stmt = $dbh->prepare("INSERT INTO drug (drug1, drug2, drug3) VALUES (:drug1, :drug2, :drug3)");

$stmt->bindParam(':drug1', $drug1);
$stmt->bindParam(':drug2', $drug2);
$stmt->bindParam(':drug3', $drug3);

$stmt->execute();
?>

Btw, I'm making this for a homeless hostel so some of these clients may have taken drugs in the past (which they need on record); I'm not making some kind of drug dealership haha

You need to do a [`->lastInsertId()`](http://php.net/manual/en/pdo.lastinsertid.php) after inserting into your `client` table to get the client id, and then insert that in your `drug` table. see also http://stackoverflow.com/questions/10680943/pdo-get-the-last-id-inserted — Sean, Jan 14 '15 at 15:51

Kiyan · Accepted Answer · 2015-01-14T16:35:59.587

3

when you insert some thing in database you can get it's id like:

$lastId = $dbh->lastInsertId();

so you can do this like this:

$first_name = $_POST['first_name'];
$stmt = $dbh->prepare("INSERT INTO client (first_name) VALUES (:first_name)");
$stmt->bindParam(':first_name', $first_name);

$stmt->execute();

$drug_client_id = $dbh->lastInsertId();

$stmt = $dbh->prepare("INSERT INTO drug (drug1, drug2, drug3, drug_client_id) VALUES (:drug1, :drug2, :drug3, :drug_client_id)");

$stmt->bindParam(':drug1', $drug1);
$stmt->bindParam(':drug2', $drug2);
$stmt->bindParam(':drug3', $drug3);
$stmt->bindParam(':drug_client_id', $drug_client_id);

$stmt->execute();

and you need to do Require("dbconnect.php"); just one time at the top

edited Jan 14 '15 at 16:35

answered Jan 14 '15 at 15:50

Kiyan

2,155
15
15

1

I believe it should be `$dbh->lastInsertId();` instead of `stmt->lastInsertId();` – Sean Jan 14 '15 at 15:52
Cheers for the help lads, I'll probably need ongoing help with this project for different stuff so yeah watch this space – jarnold Jan 14 '15 at 17:35

score 0 · Answer 2 · answered Jan 14 '15 at 15:46

It looks like you don't pass the drug_client_id together with drug 1,2,3 into the second table.

So after the first insert into your client table you have to retrieve the client_id of this client e.g. $drug_client_id. You then have to use this value $drug_client_id to setup your secoind statement ( after of course checking, that the first save was successful)

Something like this:

$stmt = $dbh->prepare("INSERT INTO drug (drug1, drug2, drug3, drug_client_id) VALUES (:drug1, :drug2, :drug3, :drug_client_id)");

$stmt->bindParam(':drug1', $drug1);
$stmt->bindParam(':drug2', $drug2);
$stmt->bindParam(':drug3', $drug3);
$stmt->bindParam(':drug_client_id', $drug_client_id);

$stmt->execute();

If hope this helps.

score 0 · Answer 3 · answered Jan 14 '15 at 15:59

First, you need to clarify your database model :

Tables architecture

--- USER ---
PK id_user <smallint> <autoincrement>
   name ... -- and others fields needed


--- DRUG ---
PK id_drug <tinyint> <autoincrement>
   name    <varchar>

--- USER_USE_DRUG ---
FK id_user <smallint>
FK id_drug <tinyint>
   usage   <datetime>

PHP side (PDO)

1 - When an user use drug you have to insert it in the table user_use_drug, some examples :

// You know id_user & id_drug
$req = $db->prepare("INSERT INTO user_use_drug(id_user, id_drug, usage) 
VALUES (:id_user, :id_drug, NOW())");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->bindParam('id_drug', $id_drug, PDO::PARAM_INT);
$req->execute();


// You know user name (unique) but you know id_drug
$req = $db->prepare("INSERT INTO user_use_drug(id_user, id_drug, usage) 
VALUES (
    (SELECT id_user FROM user WHERE name = :uname), 
    :id_drug, 
    NOW()
)");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->bindParam('id_drug', $id_drug, PDO::PARAM_INT);
$req->bindParam('uname', $uname, PDO::PARAM_STR);
$req->execute();


// You know user name (unique) and drug name (unique too)
$req = $db->prepare("INSERT INTO user_use_drug(id_user, id_drug, usage) 
VALUES (
    (SELECT id_user FROM user WHERE name = :uname), 
    (SELECT id_drug FROM drug WHERE name = :dname), 
    NOW()
)");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->bindParam('id_drug', $id_drug, PDO::PARAM_INT);
$req->bindParam('uname', $uname, PDO::PARAM_STR);
$req->bindParam('dname', $dname, PDO::PARAM_STR);
$req->execute();

2 - When you want to get infos from an user, some examples :

// Get list of drugs used if you know the id_user 
// + retrieve his informations too 
// + retrieve datas on drug
$req = $db->prepare("SELECT * FROM user_use_drug 
LEFT JOIN user ON user.id_user = user_use_drug.id_user
LEFT JOIN drug ON drug.id = user_use_drug.id_drug
WHERE user_use_drug.id_user = :id_user");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->execute();

// show every drug used
while($drug = $req->fetch(PDO::FETCH_ASSOC)){
    echo $drug['name'];
}

// Variant to above
$req = $db->prepare("SELECT * FROM user_use_drug 
NATURAL JOIN user
NATURAL JOIN drug
WHERE user_use_drug.id_user = :id_user");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->execute();


// Count the number of drugs used if you know the id_user
$req = $db->prepare("SELECT COUNT(*) as nbr FROM user_use_drug 
WHERE id_user = :id_user");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->execute();

$rep = $req->fetch();
echo $rep['nbr'];


// Request to get the list of drugs used if you don't 
// know the id_user but his name (unique)
$req = $db->prepare("SELECT * FROM user_use_drug 
WHERE id_user = (SELECT id_user FROM user WHERE name = :name");
$req->bindParam('id_user', $id_user, PDO::PARAM_INT);
$req->bindParam('name', $name, PDO::PARAM_STR);
$req->execute();

// show every drug used
while($drug = $req->fetch(PDO::FETCH_ASSOC)){
    echo $drug['name'];
}

3 - Removing client

You should define Foreign key/constraints in your database and add ON DELETE/ON UPDATE actions to permite auto-delete of rows in user_use_drug when a user is deleted. Else you have to handle it by hand : deleting all row where id_user is and THEN delete the user.

How to insert into two separate tables and link them with the same ID?

3 Answers3

Tables architecture

PHP side (PDO)