Insert multiple rows in mysqli prepared statement

[thara]

I do have 3 mysql tables "users", "banks", and "user_bank". Every users must have at least one bank and maximum is two.

When users signup to system I need to insert bank details to `user_bank` table also.

This is how I tried it:
 

    $success=FALSE;
    
    if($success == FALSE) {
        $query = "INSERT INTO user_bank (
                                      beneficiary_id
                                    , beneficiary_bank_id
                                    , branch_id
                                    ) VALUES (?, ?, ?)";
        $stmt = $mysqli->prepare($query);            
        $stmt->bind_param('iii', $lastInsertId
                               , $bank_one
                               , $branchId_one
                         );    
        $stmt->execute();
                
        if ($stmt->affected_rows == 1) {                        
            $success=TRUE;
            
            if(!empty($bank_two) && !empty($branchId_two)) {
                
                $query = "INSERT INTO user_bank (
                                                id
                                                , bank_id
                                                , branch_id
                                                ) VALUES (?, ?, ?)";
                $stmt = $mysqli->prepare($query);            
                $stmt->bind_param('iii', $lastInsertId
                                       , $bank_two
                                       , $branchId_two
                                 );    
                $stmt->execute();    
                $success=TRUE;
            }
        }
    }
    
    if ($success == TRUE) {
        $messages = array('success'=>true, 'message'=>'You successfully registered.');
    }  

My question is, can anybody tell me I there a way to use a single query for this? If so how its doing?

Thank you.

[Barand]

When using prepared statements in a situation like this you define query, prepare and bind parameters once only.

 

After doing that, loop through the data values to set the parameters and execute.

$beneficiary = 123;          // or whatever
$bank_data = array (
            $branchId1 ,
            $branchId2    
);

$sql = "INSERT INTO user_bank (beneficiary_id, branch_id VALUES (?,?))";
$stmt = $db->prepare($sql);
$stmt->bind_param('ii', $benficiary, $branch);

foreach ($bank_data as $branch) {
    $stmt->execute();
}

Edited November 28, 2015 by Barand
typo

[thara]

Then how to use bank_ids? It also may have different values.

[Barand]

If your bank_branch table stores the bank_id then you don't need it in user_bank table.

[thara]

Yes, I understood. We really don't need that bank_id column. But I still have the problem if I have account_number instead of bank_id,

[Barand]

then

$beneficiary = 123;          // or whatever
$bank_data = array (
            array($branchId1 , $acc1),
            array($branchId2 , $acc2)   
);

$sql = "INSERT INTO user_bank (beneficiary_id, branch_id, account VALUES (?,?,?))";
$stmt = $db->prepare($sql);
$stmt->bind_param('ii', $benficiary,$branch, $acc);

foreach ($bank_data as $bdata) {
    list($branch, $acc) = $bdata;
    $stmt->execute();
}