Error while performing sql server + php + Pdo query

Question

Follows the code:

$conexao = new PDO("odbc:Driver={SQL Server};Server=127.0.0.1;Database=MASTERDB; Uid=admin;Pwd=admin123;");


$select = $conexao->query("IF Object_ID('tempDB..#TabBatidas', 'U') is not null
  DROP TABLE #TabBatidas;


SET DATEFIRST 7;  -- domingo

with
Consulta as (
SELECT 
    F.FILIAL  AS  FILIAL,
  F.COLIGADA  AS  COLIGADA,
  F.CHAPA   AS  CHAPA,
  F.SECAO   AS  SECAO,
  F.SITUACAO  AS  SITUACAO,
  F.NOME    AS  NOME,
    V.DATA,
    DATENAME(dw,V.DATA) AS DIA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
  FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN vwFUNC  AS F ON V.CHAPA = F.CHAPA
  WHERE
        DATEPART(dw,V.DATA) = 1  -- domingo
        AND BATIDA IS NOT NULL 
    AND  V.DATA BETWEEN '$v_datainicio' AND '$v_datafinal'
        AND V.CODCOLIGADA = 1 

  GROUP BY V.CHAPA,V.DATA,
      F.FILIAL,F.COLIGADA,F.CHAPA,F.SECAO,F.FUNCAO,F.NOME,F.SITUACAO 
)
SELECT * 
  into #TabBatidas
  from Consulta;

CREATE clustered INDEX I1_TB on #TabBatidas (CHAPA, DATA);

SELECT 
  T1.CHAPA AS CHAPA, 
  T1.NOME AS NOME, 
  T1.FILIAL AS FILIAL,
  T1.SECAO AS SECAO,
  T1.SITUACAO AS SITUACAO, 
       Cast(T1.DATA as date) as [DATA 1],
       Cast(T2.DATA as date) as [DATA 2],
       Cast(T3.DATA as date) as [DATA 3],
     '04 - TESTE DOMINGO' AS OCORRENCIA
  from #TabBatidas as T1
       inner join #TabBatidas as T2 on T2.CHAPA = T1.CHAPA
       inner join #TabBatidas as T3 on T3.CHAPA = T1.CHAPA
  where T2.DATA = DateAdd(day, +7, T1.DATA)
        and T3.DATA = DateAdd(day, +14, T1.DATA);    

--
IF Object_ID('tempDB..#TabBatidas', 'U') is not null
  DROP TABLE #TabBatidas;
go");
?>
<div class="panel panel-primary">
  <div class="panel-heading">
    <h3 class="panel-title">04 - TESTE DE DOMINGO</h3>
  </div>
  <div class="panel-body">
<table class='datatable table table-hover table-bordered table-responsiv'>
   <thead>
     <tr>
        <th>CHAPA</th>
        <th>NOME</th>
        <th>FILIAL</th>
        <th>SECAO</th>
        <th>SITUACAO</th>
        <th>1° DOMINGO</th>
        <th>2° DOMINGO</th>
        <th>3° DOMINGO</th>
     </tr>
    </thead>
  <?php
     echo"<tbody>";   
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
     echo" <tr>";
        echo"<td>".$row['CHAPA']."</td>";
        echo"<td>".$row['NOME']."</td>";
        echo"<td>".$row['FILIAL']."</td>";
        echo"<td>".$row['SECAO']."</td>";
        echo"<td>".$row['SITUACAO']."</td>";
        echo"<td>".$row['DATA 1']."</td>";
        echo"<td>".$row['DATA 2']."</td>";
        echo"<td>".$row['DATA 3']."</td>";
     echo" </tr>";

   }   
  echo"  </tbody>";
 echo" </table>";

?>
  </div>
</div>

You’re returning to me:

Fatal error: Call to a Member Function fetch() on a non-object in G: php main reporting systems.php on line 411

The line 411 is :

while ($row = $select->fetch(PDO::FETCH_ASSOC)) {

If I run the query in sql server it runs normally, and if I listen to some other query in php will too, it is only in this one that this giving error.

Answer 1

Run the following function in your database:

if object_id('dbo.batidas', 'TF') is null
begin
  exec('create function dbo.batidas() returns @retorno table(T int) as begin return end');
end;
go

alter function dbo.batidas(@data_inicio date,
                           @data_final date)
returns @retorno table(chapa      varchar(100),
                       nome       varchar(100),
                       filial     varchar(100),
                       secao      varchar(100),
                       situacao   varchar(100),
                       [data 1]   date,
                       [data 2]   date,
                       [data 3]   date,
                       ocorrencia varchar(100))
as
begin
  declare @batidas table(filial    varchar(100),
                         coligada  varchar(100),
                         chapa     varchar(100),
                         secao     varchar(100),
                         situacao  varchar(100),
                         nome      varchar(100),
                         data      datetime,
                         dia       varchar(100),
                         batida    int);

  insert into @batidas(filial,
                       coligada,
                       chapa,
                       secao,
                       situacao,
                       nome,
                       data,
                       dia,
                       batida)
  select f.filial,
         f.coligada,
         f.chapa,
         f.secao,
         f.situacao,
         f.nome,
         v.data,
         datename(dw,v.data),
         max(v.sequencialbatida)
    from arelbatidatransitoview as v
    left join vwfunc  as f on v.chapa = f.chapa
   where datepart(dw,v.data) = 1  -- domingo
     and batida is not null
     and  v.data between @data_inicio and @data_final
     and v.codcoligada = 1
   group by v.chapa,
            v.data,
            f.filial,
            f.coligada,
            f.chapa,
            f.secao,
            f.funcao,
            f.nome,
            f.situacao;

   insert into @retorno (chapa,
                         nome,
                         filial,
                         secao,
                         situacao,
                         [data 1],
                         [data 2],
                         [data 3],
                         ocorrencia)
  select t1.chapa,
         t1.nome,
         t1.filial,
         t1.secao,
         t1.situacao,
         cast(t1.data as date),
         cast(t2.data as date),
         cast(t3.data as date),
         '04 - TESTE DOMINGO'
  from @batidas as t1
 inner join @batidas as t2 on t2.chapa = t1.chapa
 inner join @batidas as t3 on t3.chapa = t1.chapa
 where t2.data = dateadd(day, + 7, t1.data)
   and t3.data = dateadd(day, + 14, t1.data);

  return;
end;
go

This function is based on your previous select. Note that it is not necessary to create temporary tables when we can only use table-type variables. The purpose of the function is just what you wanted to do in the query: search and organize data systematically. After running it in the database, replace the select below in the query:

select bat.CHAPA,
       bat.NOME,
       bat.FILIAL,
       bat.SECAO,
       bat.SITUACAO,
       bat.[DATA 1],
       bat.[DATA 2],
       bat.[DATA 3],
       bat.OCORRENCIA
  from dbo.batidas('$v_datainicio', '$v_datafinal') bat