PHP devolviendo múltiples filas

html joomla

Question

Cuando hago eco de mi consulta en la pantalla y corro directamente en SSMS, obtengo UNA fila devuelta, que es exactamente lo que estoy buscando. Cuando ejecuto esta consulta en mi php obtengo múltiples filas devueltas (parece ser una para cada Dealer).

¿Qué debo cambiar en esta sintaxis para que solo tenga 1 fila con los totales que se muestran en la pantalla en php, como lo que sucede en mi SSMS? -> Y si copio / pego los resultados de la tabla en Excel para tratar de ordenar, todo lo que tengo son 0 que se muestran en la tabla, no datos reales como lo que veo devuelto cuando consulto directamente en SSMS

    {
$startdate = $_POST[''startdate''];
$enddate = $_POST[''enddate''];
$Dealer = $_POST[''dealer''];

$option = array();
$option[''driver''] = ''mssql'';
$option[''host''] = ''192.5.100.23'';
$option[''user''] = ''username'';
$option[''password''] = ''passs'';
$option[''database''] = ''test'';
$option[''prefix''] = '''';
$db = JDatabase::getInstance($option);
$query11 = $db->getquery11(true);
$query11 = "SELECT
MAX(A.Value1) AS Value1,
MAX(A.Value10) AS WorkinIT,
MAX(A.Value11) AS OTG
FROM
(
SELECT
ZT1.Dealer,
SUM(ISNULL(ZT1.[Value1],0)) AS Value1,
0 AS Value10,
0 AS Value11
FROM ROCK AS ZT1
WHERE ZT1.[PUD] >= ''$startdate''
AND ZT1.[PUD] <= ''$enddate''";
if ($Dealer != ''All'') {
$query11 .= " AND ZT1.Dealer = ''$Dealer''";
}
$query11 .= " GROUP BY ZT1.Dealer
UNION ALL
SELECT
ZT2.Dealer,
0 As Value1,
0 AS Value10,
0 AS Value11
FROM GALL ZT2
WHERE ZT2.[sellD] >= ''$startdate''
AND ZT2.[sellD] <= ''$enddate''";
if ($Dealer != ''All'') {
$query11 .= " AND ZT2.Dealer = ''$Dealer''";
}
$query11 .= " GROUP BY ZT2.Dealer
UNION ALL
SELECT
Dealer = RTRIM(LTRIM(dealer)),
0 As Value1,
SUM(ISNULL(abc,0)) AS Value10,
0 AS Value11
FROM WorkinIT AS C1
GROUP BY dealer
UNION ALL
SELECT
Dealer = RTRIM(LTRIM(dealer)),
0 As Value1,
0 AS Value10,
SUM(ISNULL(prs,0)) AS Value11
FROM eOTG AS C2";
if ($Dealer != ''All'') {
$query11 .= " WHERE C2.Dealer = ''$Dealer''";
}

$query11 .= " GROUP BY dealer) AS A";
}

echo $query11;
$db->setquery11($query11);
$query11 = $db->loadObjectList();
if ($query11) 
{
?>
<table border="1">
<thead>
<tr>
<th>Value </th>
<th>OTG </th>
<th>WorkinIT </th>
</tr>
</thead>
<?php
foreach ($query11 as $res) 
{
print "<tr>";
print "<td>" . "$" . round($res->Value1) . "</td>";
print "<td>" . "$" . round($res->WorkinIT) . "</td>";
print "<td>" . "$" . round($res->OTG) . "</td>";
print "</tr>";
}
}

EDITAR
Esta es la declaración de echo que la sintaxis anterior produce que funciona bien en la GUI de SSMS

SELECT Max(A.value1)  AS Value1, 
       Max(A.value10) AS WorkinIT, 
       Max(A.value11) AS OTG 
FROM   (SELECT ZT1.dealer, 
               Sum(Isnull(ZT1.[value1], 0)) AS Value1, 
               0                            AS Value10, 
               0                            AS Value11 
        FROM   rock AS ZT1 
        WHERE  ZT1.[pud] >= ''2017-06-01'' 
               AND ZT1.[pud] <= ''2017-06-22'' 
        GROUP  BY ZT1.dealer 
        UNION ALL 
        SELECT ZT2.dealer, 
               0 AS Value1, 
               0 AS Value10, 
               0 AS Value11 
        FROM   gall ZT2 
        WHERE  ZT2.[selld] >= ''2017-06-01'' 
               AND ZT2.[selld] <= ''2017-06-22'' 
        GROUP  BY ZT2.dealer 
        UNION ALL 
        SELECT dealer = Rtrim(Ltrim(dealer)), 
               0                   AS Value1, 
               Sum(Isnull(abc, 0)) AS Value10, 
               0                   AS Value11 
        FROM   workinit AS C1 
        GROUP  BY dealer 
        UNION ALL 
        SELECT dealer = Rtrim(Ltrim(dealer)), 
               0                   AS Value1, 
               0                   AS Value10, 
               Sum(Isnull(prs, 0)) AS Value11 
        FROM   eotg AS C2 
        GROUP  BY dealer) AS A 

EDIT 2
La imagen de abajo muestra lo que SSMS produce en la parte superior, y la imagen de abajo es lo que mi php genera

Answer 1

$ db-> loadObjectList () es la función de Joomla que devolverá una lista de filas. Si solo desea una fila, debe usar $ db-> loadObject () en su lugar