insert data to two table at same times using foreign key php

Question

Please advice how do I insert the data to two table in database using php. Below is the code, currently I only able to insert the data for $insert_cust but unable to insert the values from for loop.

<?php
    //including the database connection file
    include_once("config_db.php");

    //getting data from the fields
    if(isset($_POST['Submit']))
    {
        $cust_name=$_POST['cust_name'];
        $cust_add=$_POST['cust_add'];

        //insert data to database tb_cust   
        $insert_cust=mysql_query("INSERT INTO tb_cust(cust_id,cust_name,cust_add) VALUES(NULL,'$cust_name','$cust_add')");
        mysql_query($insert_cust);
        $lastid=mysql_insert_id();

        //insert data to database tb_ord_dtl
        for($i=0; $i < count($_POST['tm_id']); $i++) {
            $tm_id = addslashes($_POST['tm_id'][$i]);
            $order_qty = addslashes($_POST['order_qty'][$i]);
            $order_unit_prc = addslashes($_POST['order_unit_prc'][$i]);

            //insert data to database tb_odr_dtl
            echo "INSERT INTO tb_odr_dtl(order_id,cust_id,tm_id,order_qty,order_unit_prc) VALUES(NULL,$lastid,'$tm_id','$order_qty','$order_unit_prc')";
        }       
    }
?>

@phpPhil when I echo for loop values I getting the answer I want as below. But this values is not inserted into the database table. As well I noticed that the order_id "NULL" & cust_id "0". Please advice. INSERT INTO tb_odr_dtl(order_id,cust_id,tm_id,order_qty,order_unit_prc) VALUES(NULL,0,'42','5','42') INSERT INTO tb_odr_dtl(order_id,cust_id,tm_id,order_qty,order_unit_prc) VALUES(NULL,0,'38','6','38') INSERT INTO tb_odr_dtl(order_id,cust_id,tm_id,order_qty,order_unit_prc) VALUES(NULL,0,'','','') — Quinn, Mar 24 '15 at 04:17

score 3 · Answer 1 · answered Mar 24 '15 at 04:18

3

$insert_cust=mysql_query("INSERT INTO tb_cust(cust_id,cust_name,cust_add) VALUES(NULL,'$cust_name','$cust_add')");
        mysql_query($insert_cust);

Why did you run mysql_query twice? As you try to run mysql_query again which is nonsense, you can't get a valid insert id I think.

At the end you use echo to insert?

answered Mar 24 '15 at 04:18

harrrrrrry

13,643
2
23
28

Good pickup! In addition - see this for possible reason why [mysql_insert_id() returns 0](http://stackoverflow.com/questions/8243503/mysql-insert-id-returns-0) – phpPhil Mar 24 '15 at 04:22
@zairwolf The 2nd query is to get the foreign key values so that I can insert into the 2nd table (the for loop query). The echo is to check whether the value display is what I would like to get. – Quinn Mar 24 '15 at 04:23
@Quinn Yes I understand. But you really don't need to use 2nd query to get the key. Just use `mysql_insert_id` closely after your `mysql_query` then you can get the foreign key. – harrrrrrry Mar 25 '15 at 04:34

score 0 · Answer 2 · answered Mar 24 '15 at 04:21

0

In forloop you need not to use echo there

for($i=0; $i < count($_POST['tm_id']); $i++) {
            $tm_id = addslashes($_POST['tm_id'][$i]);
            $order_qty = addslashes($_POST['order_qty'][$i]);
            $order_unit_prc = addslashes($_POST['order_unit_prc'][$i]);

            //insert query here
            $Insert_query=mysql_query("INSERT INTO tb_cust(cust_id,cust_name,cust_add) VALUES(NULL,'$cust_name','$cust_add')");
               }

answered Mar 24 '15 at 04:21

I'm Geeker

4,601
5
22
41

Yes, sorry I forgot to alter back as echo only for checking purpose. I have tried your above suggestion but i am unable to insert value to database table_order (primary key order_id & foreign key cust_id). I am able to insert data to table_cust (primary key cust_id). – Quinn Mar 24 '15 at 04:29

insert data to two table at same times using foreign key php

2 Answers2