asked on

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

Dave Baldwin

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

Sim1980

ASKER

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;

Ray Paseur

What data base are you using? What is the value that is returned in $ID? How do you know if $stmt->execute() worked?

Sim1980

ASKER

It's a MySQL database.

And I know it works because it inserts the new record in the table.

ASKER CERTIFIED SOLUTION

loki0609

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Sim1980

ASKER

Thank you loki.

Ray Paseur

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.

loki0609

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...

Ray Paseur

@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.