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;
KC