Odtnhj

I hope that you can help, I have a table that is populated using one main query and then 4 other queries which supply values for a calculation based on the query result.

The main query filters jobs that have been completed by employees, these jobs have samples linked to them which all have different values (SampleType, AgentFee, AgentFeeExtra) which is where the other queries come in.

The other queries provide two values for calculation, the main fee and extra fee which are both unique to each sample type, I have everything working fine and I am getting a line total with one exception, when I enter the lab filter for the second sample type ($st2) it erases all entries of that sample type from the page (but not the database) so the calculation wont be accurate, if I duplicate another lab reference ($st1, $st3 or $st4) it will work, can anyone see where I have gone wrong in the code below as I cant find the issue?

Fetching and sorting the data;

$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'];

Displaying the results on the page;

<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>

I have now managed to stop it from erasing the description by adding anouther select query just for sample 2 but it still wont calculate the total despite the fact it shows the information on echo and calculates it if I manually enter the sample type into the select query instead of the reference so I am stumped as to the cause of this problem.

Any help is greatly appreciated.

Solved it, there somehow managed to be a space in front of the value on the database which is why it didn't work!