Essential Books

PDO lastInsertId

suggest change

You may often find the need to get the auto incremented ID value for a row that you have just inserted into your database table. You can achieve this with the lastInsertId() method. 

// 1. Basic connection opening (for MySQL)
$host = 'localhost';
$database = 'foo';
$user = 'root'
$password = '';
$dsn = "mysql:host=$host;dbname=$database;charset=utf8";
$pdo = new PDO($dsn, $user, $password);

// 2. Inserting an entry in the hypothetical table 'foo_user'
$query = "INSERT INTO foo_user(pseudo, email) VALUES ('anonymous', 'anonymous@example.com')";
$query_success = $pdo->query($query);

// 3. Retrieving the last inserted id
$id = $pdo->lastInsertId(); // return value is an integer

In postgresql and oracle, there is the RETURNING Keyword, which returns the specified columns of the currently inserted / modified rows. Here example for inserting one entry: 

// 1. Basic connection opening (for PGSQL)
$host = 'localhost';
$database = 'foo';
$user = 'root'
$password = '';
$dsn = "pgsql:host=$host;dbname=$database;charset=utf8";
$pdo = new PDO($dsn, $user, $password);

// 2. Inserting an entry in the hypothetical table 'foo_user'
$query = "INSERT INTO foo_user(pseudo, email) VALUES ('anonymous', 'anonymous@example.com') RETURNING id";
$statement = $pdo->query($query);

// 3. Retrieving the last inserted id
$id = $statement->fetchColumn();  // return the value of the id column of the new row in foo_user
Found a mistake? Have a question or improvement idea? Let me know.

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:

PDO:
* PDO
* Preventing SQL injection with Parameterized Queries
* Basic PDO Connection and Retrieval
* Database Transactions with PDO
* PDO connecting to MySQLMariaDB server
* PDO Get number of affected rows by a query
* PDO lastInsertId
Table Of Contents
0 Getting started
1 Variables
2 Arrays
3 Functional programming
4 Types
5 Autoloading primer
6 Exception handling and error reporting
7 Working with dates and time
8 Sending email
9 Sessions
10 Cookies
11 Classes and objects
12 Password hashing
13 Output buffering
14 JSON
15 SOAP
16 Reflection
17 cURL
18 Dependency injection
19 XML
20 Regular expressions
21 Traits
22 Namespaces
23 Parsing HTML
24 Composer dependency manager
25 Magic methods
26 Alternative syntax for control structures
27 File handling
28 Magic constants
29 Type hinting
30 Multi-threading extension
31 Filters, filter functions
32 Generators
33 Operators
34 Constants
35 UTF-8
36 URLs
37 Object serialization
38 PHPDoc
39 Contributing to PHP Manual
40 String parsing
41 Loops
42 Control structures
43 Serialization
44 Closur
45 Reading request data
46 Type juggling and non-strict comparison issues
47 Security
48 PHP MySQLi
49 Command Line Interface CLI
50 Localization
51 Debugging
52 Superglobal variables
53 Unit testing
54 Variable scope
55 References
56 Compilation errors and warning
57 Installing PHP on Windows
58 DateTime class
59 Headers manipulation
60 Performance
61 Common Errors
62 Installing on Linux / Unix
63 Contributing to PHP Core
64 Coding conventions
65 Using MongoDB
66 Asynchronous programming
67 Using SQLSRV
68 Unicode Support
69 Functions
70 Create PDF files
71 Hot to detect client IP address
72 YAML
73 Image processing with GD
74 Multiprocessing
75 SOAP Server
76 Machine learning
77 Cache
78 Streams
79 Array iteration
80 Cryptography
81 PDO
82 SQLite3
83 Sockets
84 Outputting the value of a variable
85 String formatting
86 Compile PHP extensions
87 MongoDB
88 Manipulating an array
89 Executing upon an array
90 Processing multiple arrays together
91 SPL data structures
92 Comments
93 IMAP
94 Redis
95 Imagick
96 SimplXML
97 HTTP Authentication
98 Recipies
99 BC Math Binary Calculator
100 Docker deployment
101 WebSockets
102 APCu
103 Design patterns
104 Secure remember me
105 PHP mysqli
106 PHP built-in server
107 How to break down an URL
108 PSR
109 Contributors