Issue
I have a dataset that I retrieve using PDO and fetchall. I need to find the first date a part was sold and its cost then also I need to find the last date that part was sold and its cost. I believe I have the php code how I want it because it is returning all the data I need. The question I have then is, how can/should I get the first instance of the row with the date/cost and then the last instance of the row with the date/cost? For example, the part number below, 80049 has 6 rows in my dataset. each with a different sale date and cost. I can easily get the cost/date of the first row but in my code below it's printing out every date/cost instead of the last one. I know why it's doing that with my code so my question is how can I fix that behavior?
The end goal is for the layout to look something like:
Part: Description: First Sold: Cost: Last Sold: Cost:
80049 blah blah 2022-04-20 0.755 2022-04-25 0.755
php with table code:
$current_part = "";
$data = $sql->fetchall((PDO::FETCH_ASSOC));
echo "<table id='main'>
<tr>
<th>Part</th>
<th>Description</th>
<th>Earliest Date</th>
<th>Earliest Cost</th>
<th>Last Date</th>
<th>Last Cost</th>
</tr>";
foreach ($data as $row){
if ($current_part != $row['part']){
$current_part = $row['part'];
echo "
<tr>
<td>";echo $current_part;echo"</td>
<td>";echo $row['description'];echo"</td>
<td>";echo $row['date_received'];echo"</td>
<td>";echo $row['cost'];echo"</td>";
} else {
echo "
<td>";echo $row['date_received'];echo"</td>
<td>";echo $row['cost'];echo"</td>
</tr>";
}
}
echo "</table>";
Solution
The answer is actually to change the pervasive query I was using. This was the query I was using to begin with:
select part,cost,description,date_received,qty_received
from v_po_history
where part not like '*%' and date_received >= '2022-04-01' and date_received <= '2022-04-30' and location = 'HS'
order by part, date_received asc
And the answer:
select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost, min(date_received) as First_date, max(date_received) as Last_Date, description, (max(cost) - min(cost)) as Difference
from v_po_history
where part not like '*%' and date_received >= '2022-04-01' and date_received <= '2022-04-30' and location = 'HS'
group by part,description
Answered By - SkylarP Answer Checked By - Senaida (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.