lastInsertId?

Im trying to capture the last id of the last record added using

<input type="text" name="id" value="{$dbh->lastInsertId()}">

Problem is that I have 2 queries running on this page

 try {
 
  $sql = "INSERT INTO `reviews` (
  `r_date`,
  ...
  ) VALUES (
  :r_date,
  ...
  )";
  
$stmt = $dbh->prepare($sql);

  
  $stmt->execute(array(
    ':r_date' => $r_date
    ...
  ));
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

try
{
$sql = "UPDATE `providers` SET
  `r_count` = `r_count` + 1
  WHERE `id` = :p_id";
  
$stmt = $dbh->prepare($sql);
 
$stmt->execute(array(
   ':p_id' => $p_id
  ));
}
catch(PDOException $e) {
    $dbh->rollBack();
}

And I get a 0 in the text box (when I should get a 3)

Which is the last table you insert to that has an autoincrement field?

Yes, the reviews table has an aurto_increment (id) field

Do you do a $dbh->commit() at some point between Execute and LastInsertId?
What database engine is running behind your code? PDO is an abstraction layer, but not all databases will return LastInsertId correctly.

The problem you’re having is that your UPDATE query is overwriting the lastInsertId() method’s value to 0. You can see this by just outputting the value between and after two queries:

$pdo = new PDO('...');

$pdo->exec('INSERT INTO ...');
echo $pdo->lastInsertId(); // outputs a non-zero ID
$pdo->exec('UPDATE ...');
echo $pdo->lastInsertId(); // outputs zero

You’ll therefore want to save the value of lastInsertId() in a variable before the second query (the UPDATE one) is called.

I tried to assign it to a variable,

$review_id = $pdo->lastInsertId();

but am getting

Fatal error: Call to a member function lastInsertId() on a non-object in /home/luke69/public_html/submit_a_review.php on line 253

and StarLion, I dont have that commit thing. Should I? (Im using a MySQL database with PDO)

    try {
 
  $sql = "INSERT INTO `reviews` (
  `r_date`,
  ...
  ) VALUES (
  :r_date,'
  ...
    )";
  
$stmt = $dbh->prepare($sql);

  
  $stmt->execute(array(
    ':r_date' => $r_date,
    ...
  ));
  
$review_id = lastInsertId();

} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

Once I get the bugs ironed out, ill put $dbh->rollBack(); in the catch block (that will be betteras I dont want to get hacked)

What does that commit thing even do?

If you are using InnoDB in MySQL and you begin a transaction (using PDO::beginTransaction) you get temporary state where every INSERT and UPDATE you do is not written to disk, but just visible to your connection. Once you are happy with what you’ve done, you call PDO::commit to tell MySQL to write your changes to disk so everyone can see them. Or if something went bad, call PDO::rollBack to tell MySQL to ignore all your changes.

If you are using MyISAM in MySQL all of these functions will do nothing whatsoever, because MyISAM doesn’t support transactions. Beware that all PDO functions will behave as though everything is working like it should, but don’t be fooled by that.

Transactions are useful if you have multiple queries that belong together and they must either all be executed or none of them. Suppose you have a banking system and person A is giving money to person B. You would need two update queries here; one to decrease the balance of A, and one to increase the balance of B. Suppose that the first query succeeds, but the second query fails, so person A lost their money, but B didn’t get it, so it’s effectively lost. Now if you did this in a transaction and called rollback when you saw the second query failed, the database would undo your first insert and it would be like nothing ever happened.

Probably because you called your database object $dbh instead of $pdo?