PDO insert new record to database based on lastInsertId()

Question

Ive just started learning PDO and I'm struggling by simply inserting a new record based from
$lastid = $db->lastInsertId();

The ID gets created in the database table from another function.

But nothing happens when i try to insert a new record based on that ID.

function add_name($last_id, $name) {
    $db = some_db();
    $query = "INSERT INTO team (name) VALUES (:name) WHERE id = '".$last_id."'";
    $stmt = $db->prepare($query);
    $stmt ->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt->execute();
}

`INSERT ... WHERE ...` ? You're looking for `UPDATE` maybe ? — Clément Malet, Aug 22 '14 at 13:08

Mike Brant · Answer 1 · 2014-08-22T18:16:43.610

2

INSERT ... WHERE is not valid SQL. If you are inserting a new record, an autoincremnt ID will be generated at that time (if you have such defined for the table).

If you are trying to INSERT a new row into a related table with the last id from another table, then you would set that value as one of your column inputs. So the workflow would look like this:

INSERT [column data for table_a] INTO table_a
[GET autoincrement from last insert]
INSERT (table_a_foreign_key_column, [other table_b columns]) VALUES (table_a_id, [other table_b values) INTO table_b

UPDATE:

Since UPDATE is what you want, you can make update like this:

UPDATE team
SET name = :name
WHERE id = :id

You should use parameters for both name and id values. It is still not clear to me why you would need to make an insert and then an update within the same script execution. It's not like you received any more input from the user that you did not already have. I would guess you could just insert this name values when first creating the record and save yourself the extra trouble of multiple queries.

edited Aug 22 '14 at 18:16

answered Aug 22 '14 at 13:08

Mike Brant

70,514
10
99
103

2

shouldn't it be a comment ? – Let me see Aug 22 '14 at 13:09
im actually inserting this new record on the same table where im getting the last_id from. the workflow is like this: 1. insert record to table A. 2. get the id of that record. 3. insert a new record in the same row, based on the id inserted from item #1. im sorry if im too vague on this :/ – user1735120 Aug 22 '14 at 14:10
@user1735120 That is not an insert then, that is an update. Why would you not put all the data in at once with the first insert? – Mike Brant Aug 22 '14 at 14:38
i needed to do something with the lastid first right after i insert a a new record. but it does make sense using update. ill give it a try. i think thats what im trying to achieving. updating the record. – user1735120 Aug 22 '14 at 15:35
hi i think "update" is what im looking for and not "insert". kindly change your answer. so i could accept it as answer. thanks for your time in helping me. really appreciate it. – user1735120 Aug 22 '14 at 16:02

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

i think your sql query is wrong, try this:

function add_name($last_id, $name) {
    $db = some_db();
    $query = 'INSERT INTO team (id, name) VALUES (:id, :name)';
    $stmt = $db->prepare($query);
    $stmt ->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt ->bindParam(':id', $last_id, PDO::PARAM_INT);
    $stmt->execute();
}

MySQL Insert Where query

edited May 23 '17 at 11:57

Community

1
1

answered Aug 22 '14 at 13:09

nickel715

2,505
1
23
28

PDO insert new record to database based on lastInsertId()

2 Answers2