When inserting the result of a query, inserting only the first row

Question

When inserting the result of a query, inserting only the first row

Navigation

#1 by (2 votes)
#2 by (1 votes)

-1

I'm bringing a query from one bank and I'm going to insert it into another one, this is running the question and it's only inserting the first line and not all the results, see my script below:

    <?php 

    $host="192.168.0.249";
    $port=3306;
    $socket="";
    $user="root";
    $password="";
    $dbname="db1";

    $conEmp = new mysqli($host, $user, $password, $dbname, $port, $socket)
        or die ('Could not connect to the database server' . mysqli_connect_error());

    $query = "select store_key as Loja, d.name as Departamento, sum(quantity) as Qtde, sum(amount) as Venda, sum(cost) as Custo, sum(margin) as MargemBruta, pos_number as Caixa                 from accum_item  as i                 left join department as d on i.department_key = d.department_key     where store_key in (1,2,4,5)       and pos_number >0       and fiscal_date between cast('2016-09-20' as DATE)      and cast('2016-09-20' as DATE)       group by store_key, i.department_key,pos_number      order by i.department_key";

    if ($stmt = $conEmp->prepare($query)) {
        $stmt->execute();
        $stmt->bind_result($Loja, $Departamento, $Qtde, $Venda, $Custo, $MargemBruta, $Caixa);
        while ($stmt->fetch()) {
         $v_loja   =  $Loja;
         $v_dep    =  $Departamento; 
         $v_qtde   =  $Qtde;
         $v_venda  =  $Venda; 
         $v_custo  =  $Custo;
         $v_margem =  $MargemBruta;
         $v_caixa  =  $Caixa;
         }
        $stmt->close();
    }

     $conEmp->close();


    $host="192.168.0.210";
    $port=3306;
    $socket="";
    $user="root";
    $password="";
    $dbname="db2";

    $conEmp2 = new mysqli($host, $user, $password, $dbname, $port, $socket)
        or die ('Could not connect to the database server' . mysqli_connect_error());

    mysqli_query($conEmp2,"INSERT INTO importacao (imp_id,
                                                  imp_loja,
                                                  imp_dep,
                                                  imp_qtde,
                                                  imp_venda,
                                                  imp_custo,
                                                  imp_margem,
                                                  imp_cx) 
                                VALUES ('',
                                        '$v_loja',
                                        '$v_dep',
                                        '$v_qtde',
                                        '$v_venda',
                                        '$v_custo',
                                        '$v_margem',
                                        '$v_caixa')");
     $conEmp2 ->close();

 ?>

php mysql query mysqli

asked by anonymous 20.09.2016 / 17:35

2 answers

1

Simple: You are entering only your last result.

First you make a select and fetch the results on the line

 while ($stmt->fetch())

In this, its variables $ v are assuming values as the loop unfolds.

Then you enter the values of the variables in your select from the other bank.

Two things you can do:

1st place your insertion query within the while on which you are fetching 2nd save the fetch information into an array and then insert the information in this array into the insert (foreach)

20.09.2016 / 17:48

Location.href is not transferring from page in ASP.NET MVC Zip files by command line in Windows? [closed]

score 2 · Accepted Answer

The basic problem with your code is that you are only giving an INSERT with a set of values.

A precarious solution would be to give an insert to each line returned from the first server:

<?php 
   $host="192.168.0.249";
   $port=3306;
   $socket="";
   $user="root";
   $password="";
   $dbname="db1";

   $conEmp = new mysqli($host, $user, $password, $dbname, $port, $socket)
      or die ('Could not connect to the database server' . mysqli_connect_error());

   $host="192.168.0.210";
   $port=3306;
   $socket="";
   $user="root";
   $password="";
   $dbname="db2";

   $conEmp2 = new mysqli($host, $user, $password, $dbname, $port, $socket)
      or die ('Could not connect to the database server' . mysqli_connect_error());

   $query = "select store_key as Loja, d.name as Departamento, sum(quantity) as Qtde, sum(amount) as Venda, sum(cost) as Custo, sum(margin) as MargemBruta, pos_number as Caixa                 from accum_item  as i                 left join department as d on i.department_key = d.department_key     where store_key in (1,2,4,5)       and pos_number >0       and fiscal_date between cast('2016-09-20' as DATE)      and cast('2016-09-20' as DATE)       group by store_key, i.department_key,pos_number      order by i.department_key";

   if ($stmt = $conEmp->prepare($query)) {
      $stmt->execute();
      $stmt->bind_result($Loja, $Departamento, $Qtde, $Venda, $Custo, $MargemBruta, $Caixa);
      while ($stmt->fetch()) {
         mysqli_query($conEmp2,"INSERT INTO importacao (imp_id,
            imp_loja,
            imp_dep,
            imp_qtde,
            imp_venda,
            imp_custo,
            imp_margem,
            imp_cx) 
            VALUES ('',
            '$Loja',
            '$Departamento',
            '$Qtde',
            '$Venda',
            '$Custo',
            '$MargemBruta',
            '$Caixa')"
         );
      }
      $stmt->close();
   }
   $conEmp->close();
   $conEmp2 ->close();
?>

Now, the most appropriate solution in this case would be to use prepared statements in the insert as well, which would absurdly streamline the whole operation, keeping only queries and executing the same way you are retrieving the rows).