[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-11-15
10
Medium Priority
?
445 Views
Last Modified: 2013-11-15
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

0
Comment
Question by:Sim1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39651816
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
 

Author Comment

by:Sim1980
ID: 39651837
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39651906
What data base are you using?  What is the value that is returned in $ID?  How do you know if $stmt->execute() worked?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Sim1980
ID: 39651915
It's a MySQL database.

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

Accepted Solution

by:
loki0609 earned 2000 total points
ID: 39651931
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39652000
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
 

Author Closing Comment

by:Sim1980
ID: 39652023
Thank you loki.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39652048
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
 
LVL 2

Expert Comment

by:loki0609
ID: 39652055
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39652262
@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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question