PHP/Symfony 2 - Fetch data from db, edit it and resend to another server

Question

In my symfony 2 app by using a console command I want to fetch data from database table. Looping through I want to change them a bit (e.g multiply some values) and then send it to database on another server (by curl).

How can I set new names of the columns and assign those data to them? And also how to send it as an .sql file?

Just to give you brief notion here is a raw version of my command:

class MyCommand extends ContainerAwareCommand
{
      private $input;
      private $output;

      protected function configure()
      {
        // my configs
      }

     protected function execute(InputInterface $input, OutputInterface $output)
      {
       $entityManager = $this->getContainer()->get('doctrine')->getManager('default');

       $queryBuilder = $entityManager->createQueryBuilder();
       $query = $queryBuilder
                -> // my query params



       foreach ($query->iterate() as $e)
      {
       // loop through results of the query in order to create a new DB table 
      }

     $this->sendData($output);

      }


}

Thank you in advance for any help and advises!

Answer 1

You can create another entity manager which connects to a different database. You can see that here, it's a fairly simple yaml config.

Just bring both entity managers (your 'default' and your 'other') then take what data you need and persist / flush to your other db.

Like so:

class MyCommand extends ContainerAwareCommand
{
      private $input;
      private $output;

      protected function configure()
      {
        // my configs
      }

     protected function execute(InputInterface $input, OutputInterface $output)
      {
       $entityManager = $this->getContainer()->get('doctrine')->getManager('default');
       $otherEntityManager = $this->getContainer()->get('doctrine')->getManager('other');

       $queryBuilder = $entityManager->createQueryBuilder();
       $query = $queryBuilder
                -> // my query params


       foreach ($query->iterate() as $e)
       {
           // loop through results 
           $otherEntity = new OtherEntity()
                                ->setFirstProperty($first)
                                ->setSecondProperty($second)
                      ;
           $otherEntityManaer->persist($otherEntity);
       }

      $otherEntityManager->flush();

      }   

}

Answer 2

Generate a sql file and send by curl to another server executes it doesn't seems the best approach at all. If someone else sends a malicious sql file to be executed on the server?

Doctrine provides a feature to a connect on multiple databases. Maybe you could use this functionality instead of sending the sql file.

Answering your question, inside your loop statement you could generate the INSERTS/UPDATES sql statements that you need and saving each one inside a text file using a function like fwrite

Take a look for this function here: http://php.net/manual/en/function.fwrite.php