insert API results in database update on duplicate key

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.

You need a unique or primary key for ON DUPLICATE KEY UPDATE to work. // If you just want to avoid the problem that a refresh of the result page sends (and therefor inserts) the data again, then implement the POST/Redirect/GET pattern. — CBroe, Apr 03 '17 at 11:16
I do have primary key in my table. but i dont have any unique values from API. I want to get that query working. — user2268276, Apr 03 '17 at 11:38
at the moment I have unique value for UserName. can I then use duplicate key? — user2268276, Apr 03 '17 at 11:44
tell us more about what you are doing instead of how you are doing — sakhunzai, Apr 03 '17 at 11:49
also check the query : ` VALUES ('$Data')` should not this be a comma separated values for each column you have specified in insert ? e.g values ('{$Data['listacctid']}','{$Data['Domain']}', ... ,,, but using param binding is much safer — sakhunzai, Apr 03 '17 at 11:54
That is what iam trying to explain, I dont know how to do that in my query — user2268276, Apr 03 '17 at 12:01
The UPDATE part syntax is incorrect. What you wrote looks like the syntax for an INSERT instead. Example: UPDATE \`tablename\` SET \`column1\` = 123, \`column2\` = 'somevalue' — Sloan Thrasher, Apr 03 '17 at 12:07
I found answere here: http://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert — user2268276, Apr 03 '17 at 12:11

user2268276 · Accepted Answer · 2017-04-03T14:29:11.780

   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.

mistake I had ) at the end that shouldn't be there, Thanx @sakhunzai I think I have it now. — user2268276, Apr 03 '17 at 14:04

insert API results in database update on duplicate key

1 Answers1