0

In my query I need to insert the auto-increment ID of my row into another column.

I need the protonumber column to have 3 strings together, but I can't figure out how to retrieve the current lastinsertID and make it work.

Any ideas?

        $stmt = $db->prepare('INSERT INTO protonumbers (season,program,designer,brand,date,protonumber) VALUES (:season, :program, :designer, :brand, :date, :protonumber)');
        $last_id = $pdo->lastInsertId();
        $stmt->execute(array(
            ':season' => $season,
            ':program' => $program,
            ':designer' => $designer,
            ':brand' => $brand,
            ':date' => $date,
            ':protonumber' => "{$program}-{$season}-{$last_id}"
            ));
        $id = $db->lastInsertId('protoID');
Gabriel Balza
  • 23
  • 1
  • 7
  • You should make two query: after the 1st get ladtId and updatethe record in the 2nd – splash58 Feb 20 '18 at 21:00
  • 2
    Are you sure you need to store that? It's just three of your existing columns concatenated together. Seems like you could just produce it in that format when you query and output. – Don't Panic Feb 20 '18 at 21:00
  • Yeah, I need to do that for other reasons. What's the best way to do that? – Gabriel Balza Feb 20 '18 at 21:02
  • Like @splash58 says. Assuming the insert ID you're referring to is the autoincrement ID of the record you just inserted, it doesn't exist until after the insert. You'll have to use a second update query. – Don't Panic Feb 20 '18 at 21:05
  • You can also look into setting up an after insert trigger: https://stackoverflow.com/questions/469009/can-you-access-the-auto-increment-value-in-mysql-within-one-statement – Don't Panic Feb 20 '18 at 21:09
  • @Don'tPanic I prefer using triggers but some hosters don't allows them :( – splash58 Feb 20 '18 at 21:11
  • @GabrielBalza generate it on fly: `SELECT protonumbers.* CONCAT(program,'-', season,'-' id_column_name) AS protonumber FROM protonumbers` (or any similar method to concat strings. this is for MySQL) – bato3 Feb 20 '18 at 21:13
  • And `$stmt = $db->prepare('INSERT...` don't make query, so you don't have `lastInsertId` – bato3 Feb 20 '18 at 21:14
  • @Don't Panic I'm a little confused. Could you please show me how to update it with the 3 strings? – Gabriel Balza Feb 20 '18 at 21:17
  • @bato3 how would it look? – Gabriel Balza Feb 20 '18 at 21:18
  • @GabrielBalza what: `how would it look?` remove column `protonumber` from database and calculate it on the fly (example above) or make 2 queries: `INSERT` without correct `protonumber` value and `UPDATE` to fix them – bato3 Feb 20 '18 at 21:24

1 Answers1

-2

Use LAST_INSERT_ID() from your SQL query.

Or

You can also use mysql_insert_id() to get it using PHP.

Khaled Alam
  • 885
  • 7
  • 12