PDO lastInsertId(); returning 0

Vote count:

0

I am having some trouble using PDO::lastInsertId(); it always returns a 0 instead of the last id.

I have searched around for hours a not a found a solution for my particular issue.

Below is code from class DB

    <?php
class DB {

    //Creating a single DB connection(istance)

    private static $_instance = null;
    private $_pdo, 
            $_query, 
            $_error = false, 
            $_results, 
            $_count = 0;

    public function __construct() {
        try {
            $this->_pdo = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db'), Config::get('mysql/username'), Config::get('mysql/password'));
        } catch(PDOException $e) {
            die($e->getMessage());
        }
    }

    public static function getInstance() {
        if(!isset(self::$_instance)) {
            self::$_instance = new DB();    
        }
        return self::$_instance;
    }

    //Executing queries e.g. getting, updating, deleting, inserting data

    public function query($sql, $params = array()) {
        $this->_error = false;
        if($this->_query = $this->_pdo->prepare($sql)) {
            $x = 1;
            if(count($params)) {
                foreach($params as $param) {
                    $this->_query->bindValue($x, $param);
                    $x++;
                }
            }

            if($this->_query->execute()){
                $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                $this->_count = $this->_query->rowCount();
            } else {
                $this->_error = true;
            }
        }

            return $this;

        }

        public function action($action, $table, $where = array()) {
            if(count($where) === 3) {
                $operators = array('=', '>', '<', '>=', '<=');

                $field      = $where[0];
                $operator   = $where[1];
                $value      = $where[2];

                if(in_array($operator, $operators)) {
                    $sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";
                    if(!$this->query($sql, array($value))->error()) {
                        return $this;
                    }
                }
            }
            return false;
        }

        //Queries

        public function get($table, $where) {
            return $this->action('SELECT *', $table, $where);
        }

        public function delete($table, $where) {
            return $this->action('DELETE', $table, $where);
        }

        public function insert($table, $fields = array()) {
            $keys = array_keys($fields);
            $values = '';
            $x = 1;

            foreach($fields as $field) {
                $values .= '?';
                if($x < count ($fields)) {
                    $values .= ', ';
                }
                $x++;
            }

            $sql = "INSERT INTO {$table} (`" . implode('`, `', $keys) . "`) VALUES ({$values})";

            if($this->query($sql, $fields)->error()) {
                return true;
            }

        return false;
        }

        public function update($table, $id, $fields) {
            $set = '';
            $x = 1;

            foreach($fields as $name => $value) {
                $set .= "{$name} = ?";
                if($x < count($fields)) {
                    $set .= ', ';
                }
                $x++;
            }

            $sql = "UPDATE {$table} SET {$set} WHERE id = {$id}";

            if(!$this->query($sql, $fields)->error()) {
                return true;
            }

            return false;
        }

        //Return last inserted ID
        public function lastInsertId() {
            return $this->_pdo->lastInsertId();
        }

        public function results() {
            return $this->_results;
        }

        public function first() {
            return $this->results()[0];
        }

        public function error() {
            return $this->_error;
        }

        public function count() {
            return $this->_count;
        }

}

The code that calls upon these functions is:

    <?php
    require_once 'core/init.php';

    if(Input::exists()) {
        if(Token::check(Input::get('token'))) {
        $validate = new Validate();
        $validation = $validate->check($_POST, array(
                'name' => array(
                    'required' => true,
                    'min' => 2,
                    'max' => 50 
                ),
                'email' => array(
                    'required' => false,
                    'min' => 6, 
                )

            ));

        if($validation->passed()) {
            //Session::flash('success', 'You have successfully registered!');
            //header('Location: index.php');
            $user = new User();
            $card = new Card();
            $db = new DB();

            $salt = Hash::salt(32);

            try {

                $user->create(array(
                    'name' => Input::get('name'),
                    'email' => Input::get('email'),
                    'joined' => date('Y-m-d H:i:s'),
                    'group' => 1,
                ));

                $lastID = $db->LastInsertID();

                $card->createCard(array(
                    'cust_id' => $lastID
                ));

                Session::flash('home', 'You have successfully registered and can now log in!');
                Redirect::to('index.php');

            } catch(Exception $e) {
                //could redirect user to a nother page
                die($e->getMessage());
            }
        } else {
            foreach ($validation->errors() as $error) {
                echo $error, '<br>';
            }
        }
    } 
}
?>

<form action="" method="post">
    <div class="field">
        <label for="name">Name</label>
        <input type="text" name="name" id="name" value="<?php echo escape(Input::get('name')); ?>">
    </div>

    <div class="field">
        <label for="email">Email</label>
        <input type="email" name="email" id="email">
    </div>

    <input type="hidden" name="token" value="<?php echo Token::generate(); ?>">
    <input type="submit" value="Register">

</form

>

This is just returns a value of 0 for the last inserted ID.

The insert statements are working as data goes into the database and both tables fine.

Any help would be much appreciated

Thanks Chris

asked 2 mins ago

Chris Hinds

1

PDO lastInsertId(); returning 0