Page 1 of 1

pdo lastInsertId() method not working

Posted: Wed May 25, 2011 9:23 pm
by kc11
Hi,

I am working with php5.3's pdo database abstraction layer and mysql.

I have inserted some data into a mysql database table with 2 columns (k and u ).Please note that there is no id column, although the database has autoincrement turned on. This seems to be working fine. However when I try to use the lastInsertId() method it returns 0 every time. Below is the code. can anyone explain what the problem is?

Code: Select all


  try {
                    $db = new PDO("mysql:host=$hostname;dbname=$dbase", $username, $password);
                    echo "Connected to database"; // check for connection
                    }
                catch(PDOException $e)
                    {
                    echo $e->getMessage();
                    }





$sql = "INSERT INTO table  (K,U)  VALUES (:K, :U) ";  // k and u are defined elsewhere
$istmt = $db->prepare($sql);
if(!$istmt) {
 return "st";
}




                 
                foreach ($tab as $key) { 
                    
                
                $k = $key[x]; // I AM ITERATING THROUGH AN ARRAY AND LOADING THE VALUES INTO $k
                
                // Does record already exist? Only insert into database if new item...
               
                $sth = $db->prepare('SELECT * FROM table where K = ?');
               $sth->bindParam(1,$k);
                $sth->execute();
                $number_of_rows = $sth->fetchall();
                
if(count($number_of_rows)<1)
        {
            
            echo "<font color=green>Inserting new item..</font><br/>";
            
             $istmt->bindParam(':K', $k);
            $istmt->bindParam(':U', $u);
            $result = $istmt->execute();  
                       
              $id = $db->lastInsertId();          
               print ' ID '.$id;        
        }
        else
        {
            echo "<font color=blue>Not inserting existing item..</font><br/>";
        }


            }
              

$id = $db->lastInsertId();

print ' ID '.$id;


Thanks in advance,

KC

Re: pdo lastInsertId() method not working

Posted: Thu May 26, 2011 7:27 am
by Weirdan
post your table definition (output of SHOW CREATE TABLE).

Re: pdo lastInsertId() method not working

Posted: Thu May 26, 2011 10:23 pm
by kc11
CREATE TABLE `table` (
`K` varchar(512) NOT NULL,
`U` varchar(512) NOT NULL,
`Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Thanks for looking at it :)

KC

Re: pdo lastInsertId() method not working

Posted: Fri May 27, 2011 1:03 pm
by Weirdan
You don't have any auto increment columns in that table - thus no ids are generated.

Re: pdo lastInsertId() method not working

Posted: Sat May 28, 2011 9:44 pm
by kc11
Thanks Weirdan,

I added
`id` mediumint(9) NOT NULL AUTO_INCREMENT,


to my table and it now works.

KC