CODEWITHSUNDEEP
phpmysqlcodeigniter
rochellecanale
rochellecanale

Reputation: 149

Insert a foreign key values in mysql (codeigniter)

hey guys i have a little problem. All i want is to insert a foreign key values in my table. Here’s my create table statement in mysql.

CREATE TABLE `sales` (
 `sales_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `fkmember` INT(10) UNSIGNED NOT NULL,
 `date_of_sales` DATETIME NOT NULL,
 PRIMARY KEY (`sales_id`),
 INDEX `fkmember` (`fkmember`),
 CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`fkmember`) REFERENCES `member` (`member_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3;


CREATE TABLE `sales_line` (
 `line_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `fkproduct` INT(10) UNSIGNED NOT NULL,
 `fksales` INT(10) UNSIGNED NOT NULL,
 `quantity_purchased` INT(10) UNSIGNED NOT NULL,
 `subtotal` FLOAT(7,2) UNSIGNED NOT NULL,
 PRIMARY KEY (`line_id`),
 INDEX `fkproduct` (`fkproduct`),
 INDEX `fksales` (`fksales`),
 CONSTRAINT `sales_line_ibfk_1` FOREIGN KEY (`fkproduct`) REFERENCES `product` (`product_id`),
 CONSTRAINT `sales_line_ibfk_2` FOREIGN KEY (`fksales`) REFERENCES `sales` (`sales_id`)
)

my table structure:

sales table sales_id | fkmember | date_of_sales |

sales_line table line_id | fkproduct | fksales | quantity_purchased | subtotal |

my code in inserting the values in two tables:

foreach($products as $p){
        $data = array(
            'sales_id' => null,
            'fkmember' => $memberid
            'name' => $p['product_name']
        );
        $this->db->insert('sales',$data);
    }
   foreach($products as $p){
        $data = array(
            'line_id' => null,
            'fk_product' => $p['id'],
            'fk_sales' => null,
            'quantity_purchased' => $p['product_qty'],
            'subtotal' => number_format($subtotal,2)
        );
        $this->db->insert('sales_line',$data);
    }
    }

i know i have an error in inserting values in inserting values in fk_sales. How can i insert a value in this field that comes from the id of my sales table? Because i want to insert these two tables in one round. Please Help Me Guys. Thanks

Upvotes: 1

Views: 7753

Answers (3)

Shaolin
Shaolin

Reputation: 2551

in your controller function, insert sales entry first, and return the sales id, then use the returned id to insert sales lines as below.

Eg: 
//Controller function

  //insert the sales record and get the sales id
  $sales_id = $this->model_sale->insert_sale($mameber_id, $product_name); 

foreach($products as $p)
{
  //insert sales line
  $this->model_sales_line->insert_sales_line($sales_id, $p['id'],$p['product_qty'],$sub_total);
}

//Sales Model

public function insert_sale($mameber_id, $product_name)
{
 $data = array(            
        'fkmember' => $memberid
        'name' => $p['product_name']
    );
    $this->db->insert('sales',$data);

   return $this->db->insert_id();
}

Don't forget to accept any of the answers if correct.

Upvotes: 0

seangates
seangates

Reputation: 1522

Try this (notice the use of $this->db->insert_id() ):

    foreach($products as $p){
        $data = array(
            'sales_id' => null,
            'fkmember' => $memberid
            'name' => $p['product_name']
        );
        $this->db->insert('sales',$data);
    }

    $sales_insert_id = $this->db->insert_id();

    foreach($products as $p){
        $data = array(
            'line_id' => null,
            'fk_product' => $p['id'],
            'fk_sales' => $sales_insert_id,
            'quantity_purchased' => $p['product_qty'],
            'subtotal' => number_format($subtotal,2)
        );
        $this->db->insert('sales_line',$data);
    }

Upvotes: 2

Laurence
Laurence

Reputation: 60040

Have a look at the Codeigniter Database Helper guide here. The first function is $this->db->insert_id();

So you can use this like;

 $this->db->insert('sales',$data);
 $fk_sales_id = $this->db->insert_id();

Upvotes: 0

Related Questions