PDO Insert query not inserting data into database?

Question

I have following code first insert is working properly and data in to database but second insert query which depends on last_insert_id() fails. I have tried to find out mistake but failed to do so?

<?php
namespace Solutions\File;
class AddUserData {
    function __construct($DB_con)
   {
     $this->db = $DB_con;
   }
    public function insert_detail($productDetails){
      var_dump($productDetails);
    try
       {
            $stmt = $this->db->prepare("INSERT INTO product_master(reg_id,category_id,sub_cat_id,product_name)  VALUES(:reg_id,:category_id,:sub_cat_id,:product_name)");
            $query=$this->db->prepare("INSERT INTO gy_product_detail(product_id,product_detail,"
                    . "product_image_back,product_image_left,product_image_name,product_image_right,"
                    . "product_rate,product_discount) VALUES (last_insert_id(),:product_details,"
                    . ":product_image1,:product_image2,:product_image3,:product_image4,"
                    . ":rate,:discount");
            $stmt->execute(array(':reg_id'=>$productDetails['registration_id'],
                ':category_id'=>$productDetails['catagory_id'],
                ':sub_cat_id'=>$productDetails['sub_cat_id'],
                ':product_name'=>$productDetails['product_name']));
            $query->execute(array(
                ':product_details'=>$productDetails['product_details'],
                ':product_image1'=>$productDetails['image1']['name'],
                ':product_image2'=>$productDetails['image2']['name'],
                ':product_image3'=>$productDetails['image3']['name'],
                ':product_image4'=>$productDetails['image4']['name'],
                ':rate'=>$productDetails['product_cost'],
                ':discount'=>$productDetails['product_discount']));
            return $stmt;
       }
       catch(PDOException $e)
       {
           echo $e->getMessage();
       }
    }
}

can anyone guide where I'm wrong and point me in right direction, I am getting no errors however I have enabled error reporting?

some links I referred to: MySQL- Why is LAST_INSERT_ID() not working for me? MySQL: LAST_INSERT_ID() returns 0

i am able to solve my problem with help of fredi error who linked me to http://php.net/manual/en/pdo.error-handling.php thnks man — , Dec 31 '15 at 15:27

score 1 · Answer 1 · answered Dec 31 '15 at 14:15

You have to query the last id: select last_insert_id()

change your second query to:

        $query=$this->db->prepare("INSERT INTO gy_product_detail(product_id,product_detail,"
                . "product_image_back,product_image_left,product_image_name,product_image_right,"
                . "product_rate,product_discount) VALUES ((select last_insert_id()),:product_details,"
                . ":product_image1,:product_image2,:product_image3,:product_image4,"
                . ":rate,:discount");

score 0 · Answer 2 · answered Dec 31 '15 at 14:35

You should use PDO::lastInsertId, I use this in a daily basis and have never found a problem.

$stmt = $this->db->prepare("
    INSERT INTO product_master
    (reg_id,category_id,sub_cat_id,product_name)  
    VALUES 
    (:reg_id,:category_id,:sub_cat_id,:product_name)
");

$stmt->execute([
    ':reg_id'       => $productDetails['registration_id'],
    ':category_id'  => $productDetails['catagory_id'],
    ':sub_cat_id'   => $productDetails['sub_cat_id'],
    ':product_name' => $productDetails['product_name']
]);

// get the ID from last executed statement ($stmt->execute())           
$lastID = $this->db->lastInsertId();


$query = $this->db->prepare("
    INSERT INTO gy_product_detail
    (product_id,product_detail, product_image_back, product_image_left,
    product_image_name, product_image_right, product_rate, product_discount)
    VALUES 
    (:lastID,:product_details, :product_image1,:product_image2,
    :product_image3,:product_image4, :rate, :discount)
");

$query->execute([
    ':lastID'          => $lastID,
    ':product_details' => $productDetails['product_details'],
    ':product_image1'  => $productDetails['image1']['name'],
    ':product_image2'  => $productDetails['image2']['name'],
    ':product_image3'  => $productDetails['image3']['name'],
    ':product_image4'  => $productDetails['image4']['name'],
    ':rate'            => $productDetails['product_cost'],
    ':discount'        => $productDetails['product_discount']
]);

return $stmt;

PDO Insert query not inserting data into database?

2 Answers2