Setting an INT foreign key column to NULL in a foreach array using PHP

Question

I am having a form with an appending table which goes on appending the values entered in the form to the table and as I click the Save button, the entire record set goes to the table...

The Controller page:-

function invoiceEntry(){
    $ref_invoice_no=$_POST['ref_invoice_no'];
    $entry_date=$_POST['entry_date'];
    $store_name=$_POST['store_name'];
    $store_name=empty($_POST['store_name']) ? NULL : $_POST['store_name'];
    $no_packages=$_POST['no_packages'];
    $net_weight_each=$_POST['net_weight_each'];
    $total_net_qty=$_POST['total_net_qty'];
    $obj=new Sales();
    foreach ($ref_invoice_no as $key => $value) {
        $obj->addInvoice($ref_invoice_no[$key],$entry_date[$key],$store_name[$key],$no_packages[$key],$net_weight_each[$key],$total_net_qty[$key]);
    }
}

The Model page:-

function addInvoice($ref_invoice_no,$fregno,$entry_date,$catalogue_type,$store_name,$category,$grade,$pack_type,$manufacture_date,$full_half,$sample_allowed,$no_packages,$net_weight_each,$total_net_qty){
        $conn=new Connection();
        $sql="INSERT INTO invoice (ref_invoice_no,fac_reg_no,date_of_entry,exestate_mainsale,stores_code,category_code,grade_code,packing_code,date_of_manufacture,full_half,sample_allowed,no_of_packages,net_weight_each,total_net_qty) VALUES('$ref_invoice_no','$fregno','$entry_date','$catalogue_type',$store_name,'$category','$grade','$pack_type','$manufacture_date','$full_half','$sample_allowed','$no_packages','$net_weight_each','$total_net_qty')";
        echo $sql;
        $result=$conn->query($sql);
        return $result;
}

I am getting the error as :- Cannot add or update a child row: a foreign key constraint fails (teabs.invoice, CONSTRAINT invoice_ibfk_2 FOREIGN KEY (stores_code) REFERENCES stores (stores_code))

But if I go and place the query in PHPMyAdmin, it works perfectly as I have set the stores field to accept NULL values.

The code is vulnerable to SQL injection; this is [a serious problem that you need to fix](http://stackoverflow.com/q/60174/50079) **immediately** if untrusted people have access to the application. — Jon, Jul 11 '14 at 11:29

score 0 · Accepted Answer · answered Jul 11 '14 at 11:34

This is wrong since store_name is an array, and u need to check the indexes. this code is never setting null.

$store_name=empty($_POST['store_name']) ? NULL : $_POST['store_name'];

change it to:

$store_name=$_POST['store_name'];

You need to move that logic into:

$obj->addInvoice($ref_invoice_no[$key],$entry_date[$key],empty($store_name[$key]) ? NULL : $store_name[$key],$no_packages[$key],$net_weight_each[$key],$total_net_qty[$key]);

You can check if this works.

Setting an INT foreign key column to NULL in a foreach array using PHP

1 Answers1