Under other circumstances I might be tempted to use
$result = mssql_query("INSERT INTO table (fields) VALUES (data);
SELECT CAST(scope_identity() AS int)");
but as I will be inserting user-submitted data, I want to continue to use PDO, which returns an empty array.
Unfortunately, I'm running PHP on a Linux server and using dblib to interface with Microsoft SQL Server, which doesn't support PDO::lastInsertID()
.
Please help!
Update to include code example
Here's the code I'm using: col1 is a field of type int identity
and col2 is a datetime
with a default of getdate()
.
// Connect to db with PDO
$pdo = new PDO( 'dblib:host=' . $host . ';dbname=' . $database . ';', $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION) );
// Connect to db with MSSQL
$sql = mssql_connect( $host, $username, $password );
mssql_select_db( $database, $sql );
// Create SQL statement
$query = "INSERT INTO [table] ( col3, col4, col5 )
VALUES ( 'str1', 'str2', 'str3' );
SELECT SCOPE_IDENTITY() AS theID;";
// Run with MSSQL
echo "Using MSSQL...\n";
$result = mssql_query( $query );
$the_id = mssql_result( $result, 0, 'theID' );
echo "Query OK. Returned ID is " . $the_id . "\n";
// Run with PDO
echo "\nUsing PDO...\n";
$stmt = $pdo->query( $query );
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
print_r( $result );
And this is what was displayed:
Using MSSQL...
Query OK. Returned ID is 149
Using PDO...
Array
(
)
I would love to find out that I'd done something stupid, rather than come up against a horrible dead end :)
SELECT IDENT_CURRENT('tablename') To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
PHP PDO lastInsertId() method with examples in MySQL. MySQL has an AUTO_INCREMENT column attribute which automatically generates a consistent integer value upon a successful (or not) INSERT . Oftentimes, this attribute is set on a column deemed the PRIMARY KEY .
Getting MySQL last insert ID is a useful function that lets you quickly and easily retrieve the ID of the last record you added to your database.
For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of matching rows.
You've got a few choices:
SELECT @@IDENTITY - return the last ID created by actions of the current connection, regardless of table/scope
SELECT SCOPE_IDENTITY() - last ID produced by the current connection, in scope, regardless of table
SELECT IDENT_CURRENT('name_of_table'); - last ID produced on that table, regardless of table/scope/connection
Of the three, SCOPE_IDENTITY() is the best candidate.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With