时间戳

首页 > 解决方案 > 使用第二个查询时 sql 查询丢失数据的问题

php - 使用第二个查询时 sql 查询丢失数据的问题

问题描述

我希望您能提供帮助，我有一个使用一个主查询填充的表，然后是 4 个其他查询，它们根据查询结果为计算提供值。

主查询过滤员工已完成的工作，这些工作有链接到它们的样本，这些样本都有不同的值（SampleType、AgentFee、AgentFeeExtra），这是其他查询的来源。

其他查询提供了两个计算值，主要费用和额外费用，这对每种样品类型都是唯一的，当我输入第二个样品的实验室过滤器时，我一切正常，我得到一个总行数，但有一个例外type ($st2) 它会从页面（但不是数据库）中删除该样本类型的所有条目，因此计算将不准确，如果我复制另一个实验室参考（$st1、$st3 或 $st4）它将起作用，可以有人看到我在下面的代码中哪里出错了，因为我找不到问题吗？

获取和排序数据；

$results = mysqli_query($connection, "SELECT `AIID`, `Lab`, `CollectionAgent`, `CollectionDate`, `CollectionPostcode`, `NumberofClients`, `SampleType1`, `LaborDept2`, `SampleType2`, `LaborDept3`, `SampleType3`, `LaborDept4`, `SampleType4`, `Mileage`, `Postage`, `OtherFees`, `SubTotal` FROM `agentinvoice` WHERE CollectionDate = DATE_ADD(CURDATE(), INTERVAL -1 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -2 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -3 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -4 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -5 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -6 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -7 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -8 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -9 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -10 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -11 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -12 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -13 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -14 DAY) OR CollectionDate = DATE_ADD(CURDATE(), INTERVAL -15 DAY) AND CollectionAgent LIKE '$username'");
while($row = mysqli_fetch_array($results))
{
$id = $row['AIID'];
$lab = $row['Lab'];
$coag = $row['CollectionAgent'];
$cdat = $row['CollectionDate'];
$cpc = $row['CollectionPostcode'];
$qty = $row['NumberofClients'];
$st1 = $row['SampleType1'];
$lab2 = $row['LaborDept2'];
$st2 = $row['SampleType2'];
$lab3 = $row['LaborDept3'];
$st3 = $row['SampleType3'];
$lab4 = $row['LaborDept4'];
$st4 = $row['SampleType4'];
$mil = $row['Mileage'];
$pos = $row['Postage'];
$oth = $row['OtherFees'];
$sub = $row['SubTotal'];
}

$results_samp_one = mysqli_query($connection, "SELECT `SampleID`, `Lab`, `SampleType`, `LabFee`, `LabFeeExtra`, `AgentFee`, `AgentFeeExtra` FROM `sampletypes` WHERE Lab LIKE '$lab' AND SampleType LIKE '$st1'");
while($row = mysqli_fetch_array($results_samp_one))
{
$sample = $row['SampleType'];
$sampval1 = $row['AgentFee'];
$sampvalextr1 = $row['AgentFeeExtra'];
}

$results_samp_two = mysqli_query($connection, "SELECT `SampleID`, `Lab`, `SampleType`, `LabFee`, `LabFeeExtra`, `AgentFee`, `AgentFeeExtra` FROM `sampletypes` WHERE Lab LIKE '$lab2' AND SampleType LIKE '$st2'");
while($row = mysqli_fetch_array($results_samp_two))
{
$sample2 = $row['SampleType'];
$sampval2 = $row['AgentFee'];
$sampvalextr2 = $row['AgentFeeExtra'];
}

$results_samp_three = mysqli_query($connection, "SELECT `SampleID`, `Lab`, `SampleType`, `LabFee`, `LabFeeExtra`, `AgentFee`, `AgentFeeExtra` FROM `sampletypes` WHERE Lab LIKE '$lab3' AND SampleType LIKE '$st3'");
while($row = mysqli_fetch_array($results_samp_three))
{
$sample3 = $row['SampleType'];
$sampval3 = $row['AgentFee'];
$sampvalextr3 = $row['AgentFeeExtra'];
}

$results_samp_four = mysqli_query($connection, "SELECT `SampleID`, `Lab`, `SampleType`, `LabFee`, `LabFeeExtra`, `AgentFee`, `AgentFeeExtra` FROM `sampletypes` WHERE Lab LIKE '$lab4' AND SampleType LIKE '$st4'");
while($row = mysqli_fetch_array($results_samp_four))
{
$sample4 = $row['SampleType'];
$sampval4 = $row['AgentFee'];
$sampvalextr4 = $row['AgentFeeExtra'];
}

在页面上显示结果；

<table id="tableID" border="0"  class="sortable table zebra-style">


<thead>
  <tr>
<th><span class="style20">ID</span></th>
<th><span class="style20">Agent</span></th>
<th><span class="style20">Collection Date</span></th>
<th><span class="style20">Collection Postcode</span></th>
<th><span class="style20">Number of Clients</span></th>
<th><span class="style20">Lab 1</span></th>
<th><span class="style20">Sample Type 1</span></th>
<th><span class="style20">Lab 2</span></th>
<th><span class="style20">Sample Type 2</span></th>
<th><span class="style20">Lab 3</span></th>
<th><span class="style20">Sample Type 3</span></th>
<th><span class="style20">Lab 4</span></th>
<th><span class="style20">Sample Type 4</span></th>
<th><span class="style20">Sample Fee</span></th>
<th><span class="style20">Mileage</span></th>
<th><span class="style20">Postage</span></th>
<th><span class="style20">Other Fees</span></th>
<th><span class="style20">Sub Total</span></th>
</tr>
</thead>
<tbody  class="list">


  <tr>
    <td contenteditable="false" id="Lab_ID:<?php echo $AIID; ?>"><?php echo $AIID; ?></td>
    <td contenteditable="false" id="Agent:<?php echo $coag; ?>"><?php echo $coag; ?></td>
    <td contenteditable="false" id="Collection_Date:<?php echo $cdat; ?>"><?php echo $cdat; ?></td>
    <td contenteditable="false" id="Postcode:<?php echo $cpc; ?>"><?php echo $cpc; ?></td>
    <td contenteditable="false" id="Number_of_Clients:<?php echo $qty; ?>"><?php echo $qty; ?></td>
    <td contenteditable="false" id="Lab_1:<<?php echo Lab; ?>"><?php echo $lab; ?></td>
    <td contenteditable="false" id="Sample_Type_1:<?php echo $st1; ?>"><?php echo $st1; ?></td>
    <td contenteditable="false" id="Lab_2:<?php echo $lab2; ?>"><?php echo $lab2; ?></td>
    <td contenteditable="false" id="Sample_Type_2:<?php echo $st2; ?>"><?php echo $st2; ?></td>
    <td contenteditable="false" id="Lab_3:<?php echo $lab3; ?>"><?php echo $lab3; ?></td>
    <td contenteditable="false" id="Sample_Type_3:<?php echo $st3; ?>"><?php echo $st3; ?></td>
    <td contenteditable="false" id="Lab_4:<?php echo $lab4; ?>"><?php echo $lab4; ?></td>
    <td contenteditable="false" id="Sample_Type_4:<?php echo $st4; ?>"><?php echo $st4; ?></td>
    <td contenteditable="true" id="Sample_Value:<?php echo $samptot;?>"><?php echo $samptot;?></td>
    <td contenteditable="true" id="Milage:<?php echo $mil; ?>"><?php echo $mil; ?></td>
    <td contenteditable="true" id="Postage:<?php echo $pos; ?>"><?php echo $pos; ?></td>
    <td contenteditable="true" id="Other_Fees:<?php echo $oth; ?>"><?php echo $oth; ?></td>
    <td contenteditable="true" id="Subtotal:<?php echo $collecttot; ?>"><?php echo $collecttot; ?></td>
    <td><button id="save">Save</button>
<div id="msg"></div></td>    </tr>;

    </tr>

 </tbody>
</table>

我现在已经设法通过仅为示例 2 添加另一个选择查询来阻止它删除描述，但它仍然不会计算总数，尽管它显示了关于 echo 的信息并在我手动将示例类型输入到选择查询时计算它而不是参考，所以我对这个问题的原因感到困惑。

任何帮助是极大的赞赏。

标签： phpsql

解决方案

解决了它，不知何故设法在数据库上的值前面有一个空格，这就是它不起作用的原因！

推荐阅读