PDO query is not inserting into dtabase?

[shany0786]

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 am wrong and point me in rigt direction,I am getting no errors however i have emabled error reporting?

[pritaeas]

Dump the values, run the query against the database manually with your values. Check for errors.

[Reverend Jim]

I'm thinking thal last_insert_id() doesn't get set until you actually execute the query so how about trying

$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(array(':reg_id'=>$productDetails['registration_id'],
':category_id'=>$productDetails['catagory_id'],
':sub_cat_id'=>$productDetails['sub_cat_id'],
':product_name'=>$productDetails['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");

$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']));

[shany0786]

there were no erros on my netbeans IDE but when i check on online editors it gives

<br /> <b>Parse error</b>: syntax error, unexpected '&lt;' in <b>[...][...]</b> on line <b>1</b><br /> 

on http://sandbox.onlinephpfunctions.com/

and

` syntax error, unexpected '<'

on http://phpfiddle.org/

my var_dump

`

array (size=12)
  'sub_cat_id' => string '2' (length=1)
  'catagory_id' => string '1' (length=1)
  'registration_id' => string '3' (length=1)
  'product_name' => string 'aaaaa' (length=5)
  'product_details' => string 'aaaaaaaaaaaaaaaaaaa' (length=19)
  'product_cost' => string '' (length=0)
  'product_discount' => string '' (length=0)
  'image1' => 
    array (size=5)
      'name' => string '' (length=0)
      'type' => string '' (length=0)
      'tmp_name' => string '' (length=0)
      'error' => int 4
      'size' => int 0
  'image2' => 
    array (size=5)
      'name' => string '' (length=0)
      'type' => string '' (length=0)
      'tmp_name' => string '' (length=0)
      'error' => int 4
      'size' => int 0
  'image3' => 
    array (size=5)
      'name' => string '' (length=0)
      'type' => string '' (length=0)
      'tmp_name' => string '' (length=0)
      'error' => int 4
      'size' => int 0
  'image4' => 
    array (size=5)
      'name' => string '' (length=0)
      'type' => string '' (length=0)
      'tmp_name' => string '' (length=0)
      'error' => int 4
      'size' => int 0
  'image5' => 
    array (size=5)
      'name' => string '' (length=0)
      'type' => string '' (length=0)
      'tmp_name' => string '' (length=0)
      'error' => int 4
      'size' => int 0

`

[shany0786]

can anyone help i have been banging my head from last night

[cereal]

@shany

Hi, consider that MySQL will not return the last inserted id if:

• the table does not have a column with the auto_increment attribute
• or if you manually feed the id

Example:

-- table without auto_increment column
CREATE TABLE IF NOT EXISTS `test` (
    `tid` INT UNSIGNED NOT NULL PRIMARY KEY,
    `msg` VARCHAR(50) NOT NULL
);

-- insert, no auto_increment
INSERT INTO `test` (`tid`, `msg`) VALUES(1, 'a');
INSERT INTO `test` (`tid`, `msg`) VALUES(2, 'b');
INSERT INTO `test` (`tid`, `msg`) VALUES(3, 'c');

-- get last id, will return 0
SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

-- show table statement
SHOW CREATE TABLE `test`;
+-------+-----------------------------------
| Table | Create Table                  
+-------+-----------------------------------
| test  | CREATE TABLE `test` (
  `tid` int(10) unsigned NOT NULL,
  `msg` varchar(50) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------

-- show contents
SELECT * FROM `test`;
+-----+-----+
| tid | msg |
+-----+-----+
|   1 | a   |
|   2 | b   |
|   3 | c   |
+-----+-----+
3 rows in set (0.00 sec)

-- alter table to add auto_increment
ALTER TABLE `test` MODIFY `tid` INT UNSIGNED NOT NULL AUTO_INCREMENT;

-- new table statement, with auto_increment
+-------+--------------------------------------------------
| Table | Create Table                                     
+-------+--------------------------------------------------
| test  | CREATE TABLE `test` (
  `tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `msg` varchar(50) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------

-- insert id manually
INSERT INTO `test` (`tid`, `msg`) VALUES(4, 'd');

-- get last inserted id, it will return 0
SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+

-- select contents
SELECT * FROM `test`;
+-----+-----+
| tid | msg |
+-----+-----+
|   1 | a   |
|   2 | b   |
|   3 | c   |
|   4 | d   |
+-----+-----+

-- correct insert to get the id
INSERT INTO `test` (`msg`) VALUES('e');

-- get the id
SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+

Documentation:

• http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

How this reflects on the following query? Last insert id will return NULL, or the value of a previous insert query, so the second query can:

• auto increment his id on his own
• get the id from another insert query

Extended example:

<?php

$conn = require './pdo.php';

/**
 * prepare for test
 */
$conn->query("DROP TABLE IF EXISTS `test1`");
$conn->query("DROP TABLE IF EXISTS `test2`");
$conn->query("DROP TABLE IF EXISTS `test3`");

$conn->query("CREATE TABLE IF NOT EXISTS `test1` (`tid` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `msg` VARCHAR(20))");
$conn->query("CREATE TABLE IF NOT EXISTS `test2` LIKE `test1`");
$conn->query("CREATE TABLE IF NOT EXISTS `test3` LIKE `test1`");


/**
 * here test3 will get the last_insert_id() from test1, instead of test2
 */
$data1 = ['a'];
$data2 = [20, 'a'];
$data3 = ['a'];

$stmt1 = $conn->prepare("INSERT INTO `test1` (`msg`) VALUES(?)");
for($i = 0; $i < 5; $i++) $stmt1->execute($data1); # last id 5

$stmt2 = $conn->prepare("INSERT INTO `test2` (`tid`, `msg`) VALUES(?, ?)");
$stmt2->execute($data2); # last id 20

$stmt3 = $conn->prepare("INSERT INTO `test3` (`tid`, `msg`) VALUES(last_insert_id(), ?)");
$stmt3->execute($data3); # expected id 20 from stmt2, inserted id 5 from stmt1


/**
 * restart, will change the connection id
 * here test3 will get the last_insert_id() from test2 as expected
 */
$conn = null;
$conn = require './pdo.php';

$data1 = ['b'];
$data2 = ['b'];
$data3 = ['b'];

$stmt1 = $conn->prepare("INSERT INTO `test1` (`msg`) VALUES(?)");
for($i = 0; $i < 5; $i++) $stmt1->execute($data1); # last id 10

$stmt2 = $conn->prepare("INSERT INTO `test2` (`msg`) VALUES(?)");
$stmt2->execute($data2); # last id 21

$stmt3 = $conn->prepare("INSERT INTO `test3` (`tid`, `msg`) VALUES(last_insert_id(), ?)");
$stmt3->execute($data3); # expected id 21 from stmt2, inserted id 21 from stmt2


/**
 * restart
 * here test3 will get NULL from last_insert_id() and automatically add id 22
 */
$conn = null;
$conn = require './pdo.php';

$data1 = [11, 'c'];
$data2 = [5, 'c'];
$data3 = ['c'];

$stmt1 = $conn->prepare("INSERT INTO `test1` (`tid`, `msg`) VALUES(?, ?)");
$stmt1->execute($data1); # last id 11

$stmt2 = $conn->prepare("INSERT INTO `test2` (`tid`, `msg`) VALUES(?, ?)");
$stmt2->execute($data2); # last id 5

$stmt3 = $conn->prepare("INSERT INTO `test3` (`tid`, `msg`) VALUES(last_insert_id(), ?)");
$stmt3->execute($data3); # expected id 5 from stmt2, inserted id 22 automatically

Hope this helps you.

[Johnny1]

Try using PDO's lastInsertId() method as an array parameter to the second statement. So, instead of ... VALUES( last_insert_id(), :product_details, ..., try using ... VALUES( :product_id, :product_details, ... and then in the array options provide the value for :product_id -ex:
array(':product_id' => $this->db->lastInsertId(), ':product_details' => $productDetails['product_details'], ...)

[shany0786]

    gy_product_detail   CREATE TABLE `gy_product_detail` (
 `detail_id` int(255) NOT NULL AUTO_INCREMENT,
 `product_id` int(255) NOT NULL,
 `product_detail` varchar(200) NOT NULL,
 `product_image_back` varchar(200) NOT NULL,
 `product_image_left` varchar(200) NOT NULL,
 `product_image_name` varchar(200) NOT NULL,
 `product_image_right` varchar(200) NOT NULL,
 `product_rate` int(200) NOT NULL,
 `product_discount` int(200) NOT NULL,
 PRIMARY KEY (`detail_id`),
 KEY `product_id` (`product_id`),
 CONSTRAINT `gy_product_detail_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product_master` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1





product_master  CREATE TABLE `product_master` (
 `product_id` int(255) NOT NULL AUTO_INCREMENT,
 `reg_id` int(255) NOT NULL,
 `category_id` int(255) NOT NULL,
 `sub_cat_id` int(255) NOT NULL,
 `product_name` varchar(200) NOT NULL,
 PRIMARY KEY (`product_id`),
 KEY `reg_id` (`reg_id`,`category_id`,`sub_cat_id`),
 KEY `category_id` (`category_id`),
 KEY `sub_cat_id` (`sub_cat_id`),
 CONSTRAINT `product_master_ibfk_1` FOREIGN KEY (`reg_id`) REFERENCES `gy_registration` (`reg_id`),
 CONSTRAINT `product_master_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories_master` (`Cat_id`),
 CONSTRAINT `product_master_ibfk_3` FOREIGN KEY (`sub_cat_id`) REFERENCES `gy_sub_category` (`sub_cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

as you can i have auto increment in both tables first table is the one in which i aam not getting the data second is table in which product_id is auto increment and needed that value in first table.Is something wrong with this

[cereal]

Ok,

now I see there is an error on line 23 of your first post, you're missing the closing VALUES() parenthesis:

 ":rate,:discount");

It should be:

 ":rate,:discount)");

[shany0786]

cereal thnks a lot man but i don't know why mysql errors are not displayed and neither IDE spots it?

[cereal]

Maybe your IDE was tricked by the string concatenation, I don't know, what are you using? You should add the PDO error methods to catch the errors.

Anyway, you can write that query in one single string:

$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)");

Or to make it more readable:

$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)
        ");

Besides this, try to reply with a post and use upvote/downvote comments only when necessary, these will affect users reputation and while it is nice to receive positive rep, it's not nice to receive negative rep, as your previous downvote, if not deserve it... ^__^