Complex mysql query error with INSERT ON DUPLICATE KEY UPDATE statement

Question

For the web application that I am developing, I have a very complex SQL statement. That was earlier. But I can’t understand what has changed.

Sql error:

"You have an error in the SQL syntax, check the manual that matches the version of your MySQL server for the correct syntax to use next to" ON DUPLICATE KEY UPDATE "bankaccountid = VALUES (bankaccountid), ownerid = VALUES ('on line 1"

My request:

<?php
if($bank_name1 !== '') {
        $bank1 = "('$bank_id1', '$owner_id', '$ownertype1', '$accounttype1', '$currency1', '$bank_name1', '$bank_loc1', '$bank_country1', '$bank_accountno1', '$bank_sortcode1', '$bank_iban1', '$bank_bicswift1', '$secondary1'),";
    } else {
        $bank1 = '';
    }

    if($bank_name2 !== '') {
        $bank2 = "('$bank_id2', '$owner_id', '$ownertype2', '$accounttype2', '$currency2', '$bank_name2', '$bank_loc2', '$bank_country2', '$bank_accountno2', '$bank_sortcode2', '$bank_iban2', '$bank_bicswift2', ''),";
    } else {
        $bank2 = '';
    }

    if($bank_name3 !== '') {
        $bank3 = "('$bank_id3', '$owner_id', '$ownertype3', '$accounttype3', '$currency3', '$bank_name3', '$bank_loc3', '$bank_country3', '$bank_accountno3', '$bank_sortcode3', '$bank_iban3', '$bank_bicswift3', '$secondary3'),";
    } else {
        $bank3 = '';
    }

    if($bank_name4 !== '') {
        $bank4 = "('$bank_id4', '$owner_id', '$ownertype4', '$accounttype4', '$currency4', '$bank_name4', '$bank_loc4', '$bank_country4', '$bank_accountno4', '$bank_sortcode4', '$bank_iban4', '$bank_bicswift4', '')";
    } else {
        $bank4 = '';
    }

    $sql = "INSERT INTO bankaccounts (bankaccountid, ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) VALUES ".$bank1." ".$bank2." ".$bank3." ".$bank4." ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid), ownerid = VALUES(ownerid), ownertype = VALUES(ownertype), accounttype = VALUES(accounttype), currency = VALUES(currency), bankname = VALUES(bankname), location = VALUES(location), bankcountry = VALUES(bankcountry), accountno = VALUES(accountno), sortcode = VALUES(sortcode), iban = VALUES(iban), bicswift = VALUES(bicswift), secondary = VALUES(secondary)";

I reread this request for an hour. I have to miss something pretty stupid ...

Here is the original SQL query:

INSERT INTO bankaccounts (bankaccountid, ownerid, ownertype, accounttype, currency,
    bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary)
    VALUES ".$bank1." ".$bank2." ".$bank3." ".$bank4."
ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid),
    ownerid = VALUES(ownerid), ownertype = VALUES(ownertype),
    accounttype = VALUES(accounttype), currency = VALUES(currency),
    bankname = VALUES(bankname), location = VALUES(location),
    bankcountry = VALUES(bankcountry), accountno = VALUES(accountno),
    sortcode = VALUES(sortcode), iban = VALUES(iban),
    bicswift = VALUES(bicswift), secondary = VALUES(secondary)

As long as part of the request is UPDATEcorrect, everything is in order. But when I try INSERT, I get an error caused by mysql.

Answer 1

. .

    if($bank_name1 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) VALUES ('$owner_id', '$ownertype1', '$accounttype1', '$currency1', '$bank_name1', '$bank_loc1', '$bank_country1', '$bank_accountno1', '$bank_sortcode1', '$bank_iban1', '$bank_bicswift1', '$secondary1') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id1', ownerid='$owner_id', ownertype='$ownertype1', accounttype='$accounttype1', currency='$currency1', bankname='$bank_name1', location='$bank_loc1', bankcountry='$bank_country1', accountno='$bank_accountno1', sortcode='$bank_sortcode1', iban='$bank_iban1', bicswift='$bank_bicswift1'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

    if($bank_name2 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift) VALUES ('$owner_id', '$ownertype2', '$accounttype2', '$currency2', '$bank_name2', '$bank_loc2', '$bank_country2', '$bank_accountno2', '$bank_sortcode2', '$bank_iban2', '$bank_bicswift2') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id2', ownerid='$owner_id', ownertype='$ownertype2', accounttype='$accounttype2', currency='$currency2', bankname='$bank_name2', location='$bank_loc2', bankcountry='$bank_country2', accountno='$bank_accountno2', sortcode='$bank_sortcode2', iban='$bank_iban2', bicswift='$bank_bicswift2'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

    if($bank_name3 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) VALUES ('$owner_id', '$ownertype3', '$accounttype3', '$currency3', '$bank_name3', '$bank_loc3', '$bank_country3', '$bank_accountno3', '$bank_sortcode3', '$bank_iban3', '$bank_bicswift3', '$secondary3') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id3', ownerid='$owner_id', ownertype='$ownertype3', accounttype='$accounttype3', currency='$currency3', bankname='$bank_name3', location='$bank_loc3', bankcountry='$bank_country3', accountno='$bank_accountno3', sortcode='$bank_sortcode3', iban='$bank_iban3', bicswift='$bank_bicswift3'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

    if($bank_name4 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift) VALUES ('$owner_id', '$ownertype4', '$accounttype4', '$currency4', '$bank_name4', '$bank_loc4', '$bank_country4', '$bank_accountno4', '$bank_sortcode4', '$bank_iban4', '$bank_bicswift4') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id4', ownerid='$owner_id', ownertype='$ownertype4', accounttype='$accounttype4', currency='$currency4', bankname='$bank_name4', location='$bank_loc4', bankcountry='$bank_country4', accountno='$bank_accountno4', sortcode='$bank_sortcode4', iban='$bank_iban4', bicswift='$bank_bicswift4'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

, .

Answer 2

$bank_name1="b1";
$bank_name2="b2";
$bank_name3="b3";
$bank_name4="b4";

echo $sql;

:

INSERT INTO bankaccounts (bankaccountid, ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) 
VALUES ('', '', '', '', '', 'b1', '', '', '', '', '', '', ''), 
('', '', '', '', '', 'b2', '', '', '', '', '', '', ''), 
('', '', '', '', '', 'b3', '', '', '', '', '', '', ''), 
('', '', '', '', '', 'b4', '', '', '', '', '', '', '') 

ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid), ownerid = VALUES(ownerid), ownertype = VALUES(ownertype), 
accounttype = VALUES(accounttype), currency = VALUES(currency), bankname = VALUES(bankname), location = VALUES(location), 
bankcountry = VALUES(bankcountry), accountno = VALUES(accountno), sortcode = VALUES(sortcode), iban = VALUES(iban), 
bicswift = VALUES(bicswift), secondary = VALUES(secondary)

, . VALUES().

, UPDATE :

update col1=someval1,col2=someval2, ...