4

I'm using PHP for a web aplication, Oracle for the database and I test the queries with TOAD.

When I ran the first query, I got 90 rows of results in both (PHP and toad):

select cliente, nombre, cadena,sum(valor) valor, sum(docs) docs,
ROUND(sum(vprom),2) vprom FROM DBO.LAC_PEDIDO_TIENDA_DIA WHERE
(trunc(fecha)=trunc(TO_DATE('2016-11-14','YYYY-MM-DD'))  ) GROUP BY 
cliente, nombre, cadena

When I ran the second one, I got 91 from toad and 146 from PHP:

select cliente, nombre, cadena,sum(valor) valor, sum(docs) docs, 
ROUND(sum(vprom),2) vprom FROM DBO.LAC_PEDIDO_TIENDA_DIA WHERE 
(trunc(fecha)=trunc(TO_DATE('2016-11-14','YYYY-MM-DD')) OR DLUN>7 ) 
GROUP BY cliente, nombre, cadena

The diferent is "DLUN>7"

I created a class for the connection:

<?php 
include 'credenciales.php';

class PHPRequest
{
    public $c1;

    function __construct($ruta,$usuario,$password)
    {
        try {
            $this->c1 = new PDO($ruta,$usuario,$password);
            $this->c1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch (Exception $e) {
            echo json_encode(array('exito'=>'0', 'mensaje'=>'error de conexion'));
        }//catch
    }

    public function cantidadFilas($query)
{
    $cantidad = $this->c1->query($query);
    return $cantidad->fetchColumn();
}

function query($query)
    {   
        try {
            $encode = array();
            $cantidad = $this->cantidadFilas($query);
            $resultado = $this->c1->query($query,PDO::FETCH_ASSOC);
            $contador=0;
            if($cantidad!='0' && $cantidad!='-1'){
            while($valor=$resultado->fetch()) {

                $contador++;

                //var_dump($resultado->fetch());

                $encode[]=$this->utf8ize($valor);
            }//while
            echo $contador;
            }
            if (sizeof($encode)==0) {
                $encode = array("exito"=>0,'mensaje'=>'no encontraron registros');
            }
            $enconde = $this->utf8ize($encode);
            return json_encode($encode,JSON_PRETTY_PRINT);
        } catch (Exception $e) {
            $encode = array("exito"=>0,'mensaje'=>'error:'.$e->getMessage());
            return json_encode($encode);
        }

    }
function utf8ize($d) {
        if (is_array($d)) {
            foreach ($d as $k => $v) {
                $d[$k] = $this->utf8ize($v);
            }
        } else if (is_string ($d)) {
            return utf8_encode($d);
        }
        return $d;
    }
}


$proceso = new PHPRequest($ruta,$usuario,$password);

  $proceso->query("select cliente, nombre, cadena,sum(valor) valor, sum(docs) docs, ROUND(sum(vprom),2) vprom FROM DBO.LAC_PEDIDO_TIENDA_DIA WHERE (trunc(fecha)=trunc(TO_DATE('2016-11-14','YYYY-MM-DD')) OR DLUN>7 ) GROUP BY cliente, nombre, cadena");
 ?>

Here is the database table view's code:

CREATE OR REPLACE FORCE VIEW DBO.LAC_PEDIDO_TIENDA_DIA
(
   CLIENTE, NOMBRE, CADENA, FECHA, VALOR, DOCS, DDOM, DLUN, DMAR, DMIE, DJUE, DVIE, DSAB, VPROM
)
AS
   SELECT cliente, nombre, cadena, fecha, valor, docs, 0 dDOM, 0 dLUN, 0 dMAR, 0 dMIE, 0 dJUE, 0 dVIE, 0 dSAB, 0 VPROM
     FROM dbo.lac_pedidos_dia
   UNION ALL
   SELECT cliente, nombre, cadena, TO_DATE ('', 'DD/MM/YYYY') fecha, 0 VALOR, 0 DOCS, dDOM, dLUN, dMAR, dMIE, dJUE, dVIE, dSAB, VPROM FROM dbo.lac_dia_tienda;
Paradox
  • 4,602
  • 12
  • 44
  • 88
  • 1
    are you definitely connecting to the same database from both toad and php? If so, are you sure there is no uncommitted data in one (or both) of your sessions (i.e. is one of your sessions seeing data that isn't yet available to all sessions)? – Boneist Nov 15 '16 at 16:30
  • yes, is the same connection, and all was commit. i use the same user and password. what can be? because when i sent without param "DLUN > 7" i got the same rows number. – Gerson Alvarado Nov 15 '16 at 16:38
  • Could it be that PHP does not work correctly with brackets () and OR in where clause? Can you check if you select returns same rows in Toad and PHP using just condition "DLUN > 7"? All rows should come from dbo.lac_dia_tienda table - correct? – Rusty Nov 16 '16 at 10:32
  • i found the problem and i get the correct answer, but is a bad solution and **NOT RECOMENDED**, when i choose in php the date for example 14/11/2016 is monday an i use DLUN for this day and when i run in TOAD 14/11/2016 and DMAR , "for Tuesday". i got the same numbers of rows. when i try run with DLUN in TOAD, i got other numbers of row. I dont know what happend. – Gerson Alvarado Nov 16 '16 at 16:04
  • What happens if you read directly dbo.lac_dia_tienda using the same where condition, DLUN > 7, with toad and PHP ? Maybe issue hides inthere, very likely if it is not a table but a view where date functions are involved. According to the software client and its language date functions have different outcome especially for the day number since English/US setting count monday as first day of the week and month/day can switch their place. – A. Lion Jun 09 '22 at 08:03

0 Answers0