0

I have one form let's say bill form. i have 3 tables.

  1. bill (billId->primary key)
  2. billdetails (billdetailId->primary key, billId-> foregin key)
  3. fintran (finalId -> primary key, there are total 10 inputs in form.

On submit first 5 input should go in bill table and other 5 input should go in bill details. And all will go in final table. i used below query for this.

BEGIN;
$sql = mysqli_query($conn,"INSERT INTO `bill`(`societyId`, `unitId`, `memberId`, `area`, `arrear`, `invoiceNumber`, `invoiceDate`, `dueDate`, `billingPeriod`, `total`, `interestOnArrear`, `totalDue`, `email`, `penalty`, `principalArrears`, `interestArrears`, `advancedAdjusted`, `netPending`) VALUES ('".$societyId."','".$unitId."','".$memberId."','".$area."','".$arrear."','".$invoiceNumber."','".$invoiceDate."','".$dueDate."','".$billingPeriod."','".$total."','".$interestOnArrear."','".$totalDue."','".$email."','".$penalty."','".$principalArrears."','".$interestArrears."','".$advancedAdjusted."','".$netPending."')");     
$memo = $_REQUEST['memo'];
$charge = $_REQUEST['charge'];
$sql= mysqli_query($conn,"INSERT INTO `billdetail`(`biilId`, `memo`, `charge`) VALUES (LAST_INSERT_ID(),'".$memo."','".$charge."')");
$sql= mysqli_query($conn,"INSERT INTO `fintran`(`societyId`, `docId`, `docTypeName`, `docDate`, `unitId`, `unitName`, `memberId`, `memberName`, `comboId`, `ledgerId`, `ledgerName`, `debit`, `credit`, `netValue`) VALUES ('".$societyId."',LAST_INSERT_ID(),'bill','','".$unitId."','".$unitId."','".$memberId."','".$memberId."','','".$charge."','','','','')");
COMMIT;

after insert data i want billId i.e primary key of bill table in both billdetails and fintran table. but with this query i'm able to get this in only billdetail table. In fintran table i get primary key of billdetail table. please help me with the same.

shaggy
  • 1,708
  • 2
  • 15
  • 17
amit sutar
  • 25
  • 7
  • 1
    Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Alex Howansky Apr 20 '17 at 15:33
  • You can also use `mysqli_insert_id()` to bind it to an variable, to use it more than once. – JustOnUnderMillions Apr 20 '17 at 15:36
  • http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization use transactions in a procedure with parameters. http://stackoverflow.com/questions/3053593/mysql-insert-into-2-tables shows you how to get the ID of the record inserted. – xQbert Apr 20 '17 at 15:36

1 Answers1

0

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

Check this : MySQL Insert into multiple tables? (Database normalization?)

Community
  • 1
  • 1
Jenish
  • 535
  • 4
  • 16