lastInsertId() variable in PHP not returning records when used in a select statement

Hi all.

I have the following code that is supposed to return some records based on lastInsertId() that is stored as a variable. Unfortunately, it's not returning any records when used in the select statements.

ShortageID is an INT data type and the select statement works when I remove the WHERE clause in the select statement. It returns all records, when I just want the record whose ShortageID = $ID.

What's wrong with my syntax?

Thank you in advance.

  $query = " 
            INSERT INTO ShortageDataEntry ( 
				ShortageProductID,
				Quantity,
	                UserID,
                CreateDate
            ) VALUES ( 

                :ShortageProduct, 
                :Quantity,
                :userid,
                :currentdatetime
            ) 
        "; 
                
        // Here we prepare our tokens for insertion into the SQL query.  
        $query_params = array( 
          
            ':ShortageProduct' => $_POST['ShortageProduct'], 
            ':Quantity' => $_POST['Quantity'], 
            ':userid' => $_SESSION['user']['userid'] ,
            ':currentdatetime' => $fdate
        ); 

$stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
            $ID=$db->lastInsertId(); 

 $smt = $db->prepare("SELECT ShortageProduct FROM ShortageDataEntry INNER JOIN ShortageProducts ON ShortageDataEntry.ShortageProductID = ShortageProducts.ShortageProductID WHERE ShortageDataEntry.ShortageID = '$ID'");


 $smt->bindColumn('ShortageProduct', $shortageproduct);
 $smt->fetch(PDO::FETCH_BOUND);

Open in new window

Sim1980Asked:
Who is Participating?
 
loki0609Connect With a Mentor Commented:
what driver for pdo are you using?

"Returns the ID of the last inserted row, or the last value from a sequence object, depending on the underlying driver. For example, PDO_PGSQL requires you to specify the name of a sequence object for the name parameter."
0
 
Dave BaldwinFixer of ProblemsCommented:
It may not be working because you are not using an 'autoincrement key field'.  That is what it is supposed to return.
http://php.net/manual/en/pdo.lastinsertid.php
0
 
Sim1980Author Commented:
I am using an autoincrement field.

 CREATE  TABLE  `myDB`.`ShortageDataEntry ` (  `ShortageID` int( 11  )  NOT  NULL  auto_increment ,
 `ShortageProductID` int( 11  )  NOT  NULL ,
 `Quantity` decimal( 11, 2  )  NOT  NULL ,
  `UserID` int( 11  )  NOT  NULL ,
 `CreateDate` datetime NOT  NULL ,
 PRIMARY  KEY (  `ShortageID`  )  ) ENGINE  =  MyISAM  DEFAULT CHARSET  = utf8;
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Ray PaseurCommented:
What data base are you using?  What is the value that is returned in $ID?  How do you know if $stmt->execute() worked?
0
 
Sim1980Author Commented:
It's a MySQL database.

And I know it works because it inserts the new record in the table.
0
 
Ray PaseurCommented:
know it works because it inserts the new record in the table.
Wrong answer!  You know it worked, but there is a return value from the method and the script should be testing that return value.  You should also visualize the $ID value.

This article shows how I've mapped the MySQL functions to PDO.  I have found no trouble getting the id in these examples.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
Sim1980Author Commented:
Thank you loki.
0
 
Ray PaseurCommented:
What was the solution?  The accepted answer is a cut-and-paste from the online man page for pdo::lastInsertId() and DaveBaldwin posted that link in the first response.
0
 
loki0609Commented:
hey... i want you to know I have MAD skillzzz when coming to cut and paste:)

that being said sometimes a cut and paste saves you from typing the exact same thing as what was already said...
0
 
Ray PaseurCommented:
@loki0609: Me, too (sometimes I even read the fine manuals).  But I just want the author of the question to tell us what fixed his issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.