插入API导致重复键上的数据库更新

Question

Iam doing like this and it is working.

//Repository

public function InsertListaccts($Table = '<listaccts>', $Data)
    {
      $this->getEntityManager()->getConnection()->insert($Table, $Data);
      }

//controller

public function ApiAction()
      {
          $Cpanel = new \Gufy\CpanelPhp\Cpanel;
          $Cpanel->setAuthType('hash');
          $Cpanel->setHost($this->container->getParameter('api_host'));
          $Cpanel->setAuthorization($this->container->getParameter('api_user'), $this->container->getParameter('api_hash'));
          $Cpanel->setTimeout(50);

          $Arguments = array();
          $QueryCpanel= $Cpanel->__call('listaccts', $Arguments);
          $em = $this->getDoctrine()->getEntityManager();
          $json = json_decode($QueryCpanel, true);

          foreach ($json ['acct'] as $List) {
            $Data['Domain'] =$List ['domain'];
            $Data['IP'] = $List ['ip'];
            $Data['UserName'] = $List ['user'];
            $Data['Email'] = $List ['email'];
            $Data['StartDate'] = $List ['startdate'];
            $Data['DiskPartition'] = $List ['partition'];
            $Data['Quota'] = $List ['disklimit'];
            $Data['DiskSpaceUsed'] = $List ['diskused'];
            $Data['Package'] = $List ['plan'];
            $Data['Theme'] = $List ['theme'];
            $Data['Owner'] = $List ['owner'];
            $Data['UnixStartDate'] = $List ['unix_startdate'];

              $em->getRepository('AppBundle:Listaccts')
                  ->InsertListaccts('listaccts', $Data);
        }

Iam trying to insert data from api to database, but the problem is evrytime if I run query it keep insert all api value I get then duplicate values. At the moment I have unique value for UserName, Now if the same UserName is already present in the database, I'd like to update it. Is it possible to do on this query? $this->getEntityManager()->getConnection()->insert($Table, $Data);

I was trying to do something like this, Insert part is working. How can I make this query work, Or is there a better way to do this

 public function InsertListaccts($Table = '<listaccts>', $Data)
    {
       // $this->getEntityManager()->getConnection()->insert($Table, $Data);
        $Sql="INSERT INTO $Table(`ListacctsID`,`Domain`, `IP`, `UserName`,
                                 `Email`, `StartDate`, `DiskPartition`, `Quota`, `DiskSpaceUsed`, `Package`,
                                 `Theme`, `Owner`, `UnixStartdate`) VALUES
                                 ('{$Data['Domain']}', '{$Data['IP']}', '{$Data['UserName']}', '{$Data['Email']}', '{$Data['StartDate']}', '{$Data['DiskPartition']}', '{$Data['Quota']}', '{$Data['DiskSpaceUsed']}',
                              '{$Data['Package']}', '{$Data['Theme']}', '{$Data['Owner']}', '{$Data['UnixStartDate']}')
                        ON DUPLICATE KEY UPDATE DOMAIN =VALUES(DOMAIN ),IP=VALUES(IP),UserName=VALUES(UserName),Email=VALUES(Email),StartDate=VALUES(StartDate),DiskPartition=VALUES(DiskPartition),Quota=VALUES(Quota)
                                 ,DiskSpaceUsed=VALUES(DiskSpaceUsed),Package=VALUES(Package),Theme=VALUES(Theme),Owner=VALUES(Owner),UnixStartdate=VALUES(UnixStartdate)
                                 ";
        $Stm = $this->getEntityManager()->getConnection()->prepare($Sql);
        $Stm->execute();
       // return $Data;

        }

I dont really want to change anything in controller.

Answer 1

   public function InsertListaccts($Table = '<listaccts>', $Data)
    {
       // $this->getEntityManager()->getConnection()->insert($Table, $Data);
        $Sql="INSERT INTO $Table(`Domain`, `IP`, `UserName`,
                                 `Email`, `StartDate`, `DiskPartition`, `Quota`, `DiskSpaceUsed`, `Package`,
                                 `Theme`, `Owner`, `UnixStartdate`) VALUES
                                 ('{$Data['Domain']}', '{$Data['IP']}', '{$Data['UserName']}', '{$Data['Email']}', '{$Data['StartDate']}', '{$Data['DiskPartition']}', '{$Data['Quota']}', '{$Data['DiskSpaceUsed']}',
                                  '{$Data['Package']}', '{$Data['Theme']}', '{$Data['Owner']}', '{$Data['UnixStartDate']}')
                                    ON DUPLICATE KEY UPDATE
                                    Domain= '{$Data['Domain']}', IP ='{$Data['IP']}',
                                    UserName='{$Data['UserName']}', Email='{$Data['Email']}',
                                    StartDate='{$Data['StartDate']}',
                                    DiskPartition='{$Data['DiskPartition']}', Quota='{$Data['Quota']}',
                                    DiskSpaceUsed='{$Data['DiskSpaceUsed']}',
                                    Package='{$Data['Package']}', Theme= '{$Data['Theme']}',
                                    Owner='{$Data['Owner']}', UnixStartdate='{$Data['UnixStartDate']}'
                                  ";
        $Stm = $this->getEntityManager()->getConnection()->prepare($Sql);
        $Stm->execute();

        }

This query is working fine, but as sakhunzai suggested "but using param binding is much safer – sakhunzai" If someone transfer this query in what he meant It will be very helpfull.