how to insert same id's to multiple tables in mysql. using php?

Question

I've created this to insert to multiple tables, and I want to insert the same ID to both table. ID is auto-incremented in test table and I want test_category table to take the same ID.

I tried it but don't know where I am doing it wrong and getting an error

Fatal error: Call to undefined method mysqli_stmt::insert_id() in C:\wamp64\www\Android\include\DbOperations.php on line 27"

Check this pic for detailed error report

[https://www.dropbox.com/s/nuij2o2ac3xp8mz/Untitled4.png?dl=0]

My php

public function testReg($name, $pin, $a, $b, $ho, $ll, $c, $d){
    $stmt = $this->con->prepare("INSERT INTO `test` (`name`, `pin`) VALUES (?, ?)");
    $stmt->bind_Param("ss",$name,$pin);
    if(!$stmt->execute()){
        return 2;
    }
    $stmttst = $this->con->prepare("INSERT INTO `test_category` (`pid`, `name`, `a`, `b`, `ho`, `ll`, `c`, `d`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
    $stmttst->bind_Param("isssssss",$stmt->insert_id(),$name,$a,$b,$ho,$ll,$c,$d);
    if ($stmttst->execute()){
        return 1;
    }else{
        return 2;
    }   
}

php.net/manual/en/pdo.lastinsertid.php

e4c5
– e4c5

2017-03-22 06:12:59 +00:00
Commented Mar 22, 2017 at 6:12 — e4c5
– e4c5, Commented Mar 22, 2017 at 6:12

Ejaz47 · Accepted Answer · 2017-03-22 07:11:33Z

1

The "mysqli::$insert_id" is an attribute of mysqli object and your trying on $stmt Try Following code will resolve your issue.

public function testReg($name, $pin, $a, $b, $ho, $ll, $c, $d){
        $stmt = $this->con->prepare("INSERT INTO `test` (`name`, `pin`) VALUES (?, ?);");
        $stmt->bind_Param("ss",$name,$pin);
        if(!$stmt->execute()){
            return 2;
        }
        $stmttst = $this->con->prepare("INSERT INTO `test_category` (`pid`, `name`, `a`, `b`, `ho`, `ll`, `c`, `d`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
        $stmttst->bind_Param("isssssss",$this->con->insert_id(),$name,$a,$b,$ho,$ll,$c,$d);
        if ($stmttst->execute()){
            return 1;
        }else{
        return 2;
        }

   }

As of Documents mysqli::$insert_id -- mysqli_insert_id — Returns the auto generated id used in the latest query

So instead of using $this->con->insert_id() you can use

$var = $this->con;
mysqli_insert_id($var);

edited Mar 22, 2017 at 7:11

answered Mar 22, 2017 at 6:39

Ejaz47

1451 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SAMEER RAJPAL Over a year ago

it's[mysqli_insert_id($this->con)] working giving an eeror also "Strict standards: Only variables should be passed by reference in C:\wamp64\www\Android\include\DbOperations.php on line 27"

SAMEER RAJPAL Over a year ago

got it... just need to call it using variable. Thanks anyways.

Louis Loudog Trottier · Accepted Answer · 2017-03-22 06:31:49Z

1

Try putting your last inserted id in a variable and then pass it to your 2nd query.

public function testReg($name, $pin, $a, $b, $ho, $ll, $c, $d){
$stmt = $this->con->prepare("INSERT INTO `test` (`name`, `pin`) VALUES (?, ?);");
$stmt->bind_Param("ss",$name,$pin);
if(!$stmt->execute()){
   return 2;
  }else{
        //ADDED ESLE AND STORE ID IN VAR
     $lastid=$stmt->insert_id();
  }


$stmttst = $this->con->prepare("INSERT INTO `test_category` (`pid`, `name`, `a`, `b`, `ho`, `ll`, `c`, `d`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
$stmttst->bind_Param("isssssss",$lastid,$name,$a,$b,$ho,$ll,$c,$d);
// CHANGED BIND PARAM for last id

if ($stmttst->execute()){
    return 1;
}else{
   return 2;
}

}

Also, you shold specify if you are using mysqli or PDO as most people assume you are using PDO as it is most commonly used.

http://php.net/mysqli_multi_query might also help you in this case. Or try creating a second connectionto the BD, not just a 2nd statement.

answered Mar 22, 2017 at 6:31

Louis Loudog Trottier

1,39714 silver badges26 bronze badges

1 Comment

Louis Loudog Trottier Over a year ago

further more you could nest your 2nd call inside the else so if the 1st query fails, the 2nd won't run.

Arjan · Accepted Answer · 2017-03-22 06:16:57Z

0

You are probably looking for PDO::lastInsertId. It returns the ID of the last inserted row. Therefore you can use this in your second query as it will hold the ID of the first. More info here: http://php.net/manual/en/pdo.lastinsertid.php

answered Mar 22, 2017 at 6:16

Arjan

6096 silver badges23 bronze badges

1 Comment

SAMEER RAJPAL Over a year ago

I don't understand about how to implement it... can you show me editing my php file?

Collectives™ on Stack Overflow

how to insert same id's to multiple tables in mysql. using php?

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related