[eluser]rochellecanale[/eluser]
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.
Code:
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:
Code:
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
