PHP返回多行 [英] PHP Returning Multiple Rows
问题描述
当我在屏幕上回复我的查询并直接在SSMS中运行时，我返回了一行，这正是我所追求的。当我在我的php中运行这个查询时，我得到多行返回（似乎是每个经销商一个）。
我应该更改这个语法， 1行总计显示在屏幕上像在我的SSMS发生什么？ - >如果我将表格的结果复制/粘贴到Excel中进行排序，我所有的都是0显示在表格中，而不是像我在SSMS中直接查询时返回的实际数据
{
$ startdate = $ _POST ['startdate'];
$ enddate = $ _POST ['enddate'];
$经销商= $ _POST ['dealer'];
$ option = array（）;
$ option ['driver'] ='mssql';
$ option ['host'] ='192.5.100.23';
$ option ['user'] ='用户名';
$ 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作为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
经销商= RTRIM（LTRIM（经销商）），
0作为Value1，
SUM（ISNULL（abc，0））AS Value10，
0 AS Value11
来自WorkinIT AS C1
GROUP BY经销商
UNION ALL
SELECT
经销商= RTRIM（LTRIM（经销商）），
0作为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经销商）AS A;
}
echo $ query11;
$ db-> setquery11（$ query11）;
$ query11 = $ db-> loadObjectList（）;
if（$ query11）
{
？>
< table border =1>
< thead>
< tr>
< th>值< / th>
OTG< / th>
WorkinIT< / th>
< / tr>
< / thead>
<？php
foreach（$ query11 as $ res）
{
print< tr>;
打印< td> 。 $。 （$ res-> Value1）。 < / TD> 中;
打印< td> 。 $。 （$ res-> WorkinIT）。 < / TD> 中;
打印< td> 。 $。 （$ res-> OTG）。 < / TD> 中;
打印< / tr>;
}
}
编辑
这是上面语法生成的 echo 语句，可以在SSMS GUI中正常运行
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经纪商= Rtrim（Ltrim（经销商）），
0 AS价值1，
总和（ Isnull（abc，0））AS Value10，
0 AS Value11
FROM workinit AS C1
GROUP BY经销商
UNION ALL
SELECT经销商= Rtrim（经销商）），
0 AS价值1，
0 AS价值10，
总和（Isnull（prs，0））AS价值11
从eotg AS C2
GROUP BY经销商） AS A
编辑2
下图显示顶部SSMS输出，底部图像是我的PHP输出
< img src =https://i.stack.imgur.com/jwCF8.pngalt =在这里输入图片描述>
$ db-> loadObjectList（）是将返回行列表的Joomla函数。
如果你只需要一行，你应该使用$ db-> loadObject（）而不是
When I echo my query on screen and run directly in SSMS I get ONE row returned, which is exactly what I am after. When I run this query in my php I get multiple rows returned (seems to be one for each Dealer).
What should I change in this syntax so that I only have 1 row with totals displayed on screen in php like what happens in my SSMS? --> And if I copy/paste the results of the table into Excel to try and sort, all I have are 0's being displayed in the table, not actual data like what I see returned when I query directly in 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>";
}
}
EDIT
This is the echo statement that the above syntax produces that runs fine in SSMS GUI
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
The below image shows what SSMS outputs on the top, and the bottom image is what my php outputs
$db->loadObjectList() is the Joomla function that will return a list of rows. If you want just one row you should use $db->loadObject() instead
这篇关于PHP返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
![< img src =https://i.stack.imgur.com/jwCF8.pngalt =在这里输入图片描述>](\"https://i.stack.imgur.com/jwCF8.png\"){kind=link}