inserting to multiple tabels and giving same id's in both table

Question

i am trying to insert to multiple table using single registration form in android. and want it to insert same id's to both tables but it's taking different id's, can someone tell me what should i do for this?

DbOperations.php

public function createUser($name, $surname, $username, $user_pass, $address, $pin, $mail, $phone, $a, $b, $c, $d){
   if($this->isUserExist($username,$mail,$phone)){
     return 0;
   }else{
    $password = md5($user_pass);
    $stmt = $this->con->prepare("INSERT INTO `user_data` (`name`, `surname`, `username`, `password`, `address`, `pin`, `mail`, `phone`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
    $stmt->bind_Param("ssssssss",$name,$surname,$username,$password,$address,$pin,$mail,$phone);
    if(!$stmt->execute()){
     return 2;
    }
    $stmtcate = $this->con->prepare("INSERT INTO `employee_category` (`name`, `pin`, `a`, `b`, `c`, `d`) VALUES (?, ?, ?, ?, ?, ?);");
    $stmtcate->bind_Param("ssssss",$name,$pin,$a,$b,$c,$d);
    if ($stmtcate->execute()){
     return 1;
    }else{
     return 2;
    }
    
   }
  }

is it possible to use multiple statements mysqli->prepare? No. See here: http://stackoverflow.com/questions/11632902/mysqli-can-it-prepare-multiple-queries-in-one-statement — Jens, Mar 13 '17 at 11:48
@Jens in login function i've used it adding UNION method. so i thought it would work. — , Mar 13 '17 at 11:51
@Jens i got it, but not completely... like what to do for bind_param and execute for another $stmt? — , Mar 13 '17 at 12:08
I have added my comment as an answer. Feel free to accept it — Jens, Mar 17 '17 at 07:26
@Jens why did you deleted your answer? it's working but only not taking the same id's — , Mar 17 '17 at 08:06
@Jens yeah! that's true but we are just near to it. anyways let me know if you find any answer... i am searching the web for it too... — , Mar 17 '17 at 08:10

Jens · Answer 1 · 2017-03-17T09:00:53.310

0

You can not execute two Statements in one call of execute, so you have to split it into two Statements:

            $stmt = $this->con->prepare("INSERT INTO `user_data` (`name`, `surname`, `username`, `password`, `address`, `pin`, `mail`, `phone`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
            $stmt->bind_Param("ssssssss",$name,$surname,$username,$password,$address,$pin,$mail,$phone);
            if(!$stmt->execute()){
                return 2;
            }
            $stmt1 = $this->con->prepare("INSERT INTO `employee_category` (`pid`,`name`, `pin`, `a`, `b`, `c`, `d`) VALUES (?, ?, ?, ?, ?, ?, ?); ");
            $stmt1->bind_Param("issssss",$stmt->insert_id(),$name,$pin,$a,$b,$c,$d);
            if($stmt1->execute()){
                return 1;
            }else{
                return 2;
            }

edited Mar 17 '17 at 09:00

answered Mar 17 '17 at 07:25

Jens

63,364
15
92
104

Thanks mate, but i just not did it myself... :) only problem is... it's taking different id's in both tables which is auto incremental how to keep the id's same in both my table for the same user.? – Mar 17 '17 at 07:28
@SumitPal see my updated answer. It is the solution for mysqli. Hope this is the API you are using – Jens Mar 17 '17 at 07:34
@SumitPal BTW, you should not use the technical ID (autoincrement) as foreign key for both tables. – Jens Mar 17 '17 at 07:47
it's not working bro... it's showing {"error":false,"message":"User register successfully"} but inserting only to `user_data` – Mar 17 '17 at 07:49
getting this error: Fatal error: Call to undefined method mysqli_stmt::mysqli_insert_id() in C:\wamp64\www\Android\include\DbOperations.php on line 32 my line 32 is "$stmtcate->bind_Param("issssss",$stmt->mysqli_insert_id(),$name,$pin,$a,$b,$c,$d);" – Mar 17 '17 at 07:58
@SumitPal Have changed my answer again. But id can not be an autoincremant in the second table – Jens Mar 17 '17 at 08:13
I've updated my CreateUser function but is still not working... only taking insert values to 'user_data' but not to 'employee_category' and showing this error: " Fatal error: Call to a member function bind_Param() on boolean in C:\wamp64\www\Android\include\DbOperations.php on line 32" check my updated DbOperations.php in my question. – Mar 17 '17 at 08:44
Then you have an SQL error in your second SQL statement – Jens Mar 17 '17 at 08:47
but before adding this $stmt->insert_id() it was working fine and was taking values to both tables. without any error. can you please check my updated DbOperations.php content one more time? – Mar 17 '17 at 08:50
Maybe your column Name is not `id`? – Jens Mar 17 '17 at 08:51
Hey Bro, I've already done it... please check my DbOperations.php which in in my question section. – Mar 17 '17 at 09:05
@SumitPal Please never write "not working". explain what is not working and which error message do you get – Jens Mar 17 '17 at 10:30
okay, after adding "$stmt->insert_id()" it's inserting data to only user_data table but another tables isn't taking any values and showing this error: "Fatal error: Call to undefined method mysqli_stmt::insert_id() in C:\wamp64\www\Android\include\DbOperations.php on line 32" check image to see detailed error [https://www.dropbox.com/s/3h8s2wv6byijiyi/Untitled3.png?dl=0] – Mar 17 '17 at 11:24
did you find any solution? – Mar 18 '17 at 11:24

inserting to multiple tabels and giving same id's in both table

1 Answers1