When giving the result of a query, this inserting only the first line

Asked

Viewed 79 times

-1

I am bringing a query from one bank and I will insert in another,this running the question and that is 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 database query mysqli

  • And why do you think this code should insert more than one line, if you are running a single Insert with a set of VALUES only?

  • Why declare connection attributes twice, if you had already declared them at the beginning of the code?

  • @diegofm are 2 different servers

  • @Bacco only two changing attributes.

  • @diegofm really in theory it could change only these two, the good thing of separating is if it is something subject to reuse, not to forget to change anything. In fact, in this case I think it’s best to go right inside the function, with no extra variable. Even this port and socket so much left, since they are with default value can be omitted.

  • @Otaciobarbosa the way you were doing you should put the Insert inside the while. It’s not the right one, the more it would work.

Show 1 more comment

2 answers

2


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

A solution precarious would give an Insert to each row 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 insertion also, which would absurdly expedite the whole operation, maintaining only 2 darlings and doing executions in a row (the same way you’re retrieving lines).

  • 1

    It worked,now I will read about Prepared statements, thanks for the help.

  • @Otaciobarbosa is the same logic of SELECT that you did. just give a bind to the variables, and in WHILE only gives the STEPS of the Insert

  • Okay, I’ll test it. Thanks again.

1

Simple: you are only inserting your last result.

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

 while ($stmt->fetch())

In this, your $v variables assume values as the loop unfolds.

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

Two things you can do:

1° put your insertion query inside the while you are fetch 2° store fetch information in an array and then insert this array information into the Insert (foreach)

Browser other questions tagged php mysql database query mysqli

You are not signed in. Login or sign up in order to post.