PDO lastInsertId() returns 0

Question

I've been looking, at other questions asking the same, and can't figure out why my query won't act like it should.

My query:

$stmt = db()->prepare("INSERT INTO conversations (user1, user2) VALUES (?, ?)");
$stmt->execute(array($_SESSION['user']['userId'], $user));
echo db()->lastInsertId();

When I do this the lastInsertId(); keeps returning 0.

My db() function:

function db()
{
    $dsn = 'mysql:host=localhost;dbname=message_board';
    $username = 'root';
    $password = 'root';

    try {
        $db = new PDO($dsn, $username, $password);
    } catch(PDOException $e) {
        // exceptions handles here
    }
    return $db;
}

You are making a new `PDO` object every time you call `db()`. Just check if its already exists. — S.Visser, Apr 17 '13 at 08:56

score 7 · Accepted Answer · answered Apr 17 '13 at 08:55

7

function db()
{
    static $db;

    $dsn = 'mysql:host=localhost;dbname=message_board';
    $username = 'root';
    $password = 'root';

    if (!$db) {
       $db = new PDO($dsn, $username, $password);
    }
    return $db;
}

answered Apr 17 '13 at 08:55

Your Common Sense

156,878
40
214
345

Ahh, of course. How simple things can be sometimes. Thanks for your time :-) – skolind Apr 17 '13 at 08:57

score 1 · Answer 2 · answered Apr 17 '13 at 08:56

1

You're creating a new db connection every line.

Try:

$db = db();
$stmt = $db->prepare("INSERT INTO conversations (user1, user2) VALUES (?, ?)");
$stmt->execute(array($_SESSION['user']['userId'], $user));
echo $db->lastInsertId();

answered Apr 17 '13 at 08:56

scottlimmer

2,230
1
22
29

PDO lastInsertId() returns 0

2 Answers2

Linked