PHP MySQL PDO lastInsertID causes fatal error

Question 1

I tried looking through some other posts, but didn't see anything exactly what I'm looking for.

I have a DB query

$sql = "INSERT INTO groups(Name) VALUES (:name)";
$dbs = $dbo->prepare($sql);

$dbs->bindParam(":name", $_POST['name'], PDO::PARAM_STR);

$dbs->execute();

$groupID = $dbs->lastInsertId();

That returns this fatal error:

[Tue Dec 20 13:59:23 2011] [error] [client 127.0.0.1] PHP Fatal error:  Call to undefined method PDOStatement::lastInsertId() in /media/Storage/www/2011/admin/public/ajax.users.php on line 87, referer: http://localhost/2011/admin/public/menu.php?page=users

According to the php manual for PDO::lastInsertId():

If the PDO driver does not support this capability, PDO::lastInsertId() triggers an IM001 SQLSTATE.

How would I determine if my server supports lastInsertId()? I do not see IM001 in my error log anywhere.

When I run this, the data is inserted fine, but I cannot get its ID to use in the next set of INSERT's that set the group permissions.

Question 2

lastInsertId() is a method of the PDO class, not the PDOStatement class.

This should work:

$groupID = $dbo->lastInsertId();

Question 3

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\r\n|\r|\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        $statement->execute();
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
                        }
                    }
                }

Question 4

(Upgrading to an answer)

Looks like this bug, which is still open after almost five years; try instead:

while (true) {
  try {
    $row = $qry_bat->fetch(PDO::FETCH_ASSOC);
    if (!$row) break;
    $ins_db->execute(array(...));
    $newOnes++;
  }
  catch (PDOException $e) {
    if ($e->getCode() != 23000) {
      echo '<span class="msg-alert">'.$e->getMessage().'</span>';
    } else {
      $doublons++;
    }
  }
}

Question 5

You need to return the $result only in the run() method:

public function run($sql)
    {
        $result=$this->_connection->prepare($sql);
        $result->execute();
        return $result;
    }

Returning the $result->execute(); is returning true because the execute() succeeded. You need to return the current state of $result.

See if that works.

PHP MySQL PDO lastInsertID causes fatal error

Answers

php,mysql,pdo