Pull up last entries on a product inventory table

[LambPatch]

I have been struggling for a few days on this one and don't know what to search for to get the answers I'm looking for.

 

I would like to be able to pull up the last entries of individual products in an inventory table. I already have things so the product names are uniform (product column) and that it has a currently updated total. What I want is for my users to be able to go to a page that pulls up only the last entries for each individual product. (not the whole database).

 

The following brings up everything. But I'm looking for a quick page where it only looks for the total (tot) of the most recent entry of EACH unique product. This way products can be added through a dropdown populated by another table.

$sql = "SELECT * FROM inventory";

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

echo "<table style='border: 2px;font-family: tahoma;'><caption><b>Entire Database Contents</b></caption><tr><td>ID</td><td>Time Stamp</td><td>Staff</td><td>Client</td><td>Needed</td><td>Product</td><td>Amount</td><td>Totals</td><td style='width: 200px;'>Comments</td></tr>";
    while($row = $result->fetch_assoc()){
        echo '<td>' . $row['id'] . '</td>';
        echo '<td>' . $row['timeStamp'] . '</td>';
        echo '<td>' . $row['staff'] . '</td>';
        echo '<td>' . $row['client'] . '</td>';
        echo '<td>' . $row['dateNeeded'] . '</td>';
        echo '<td>' . $row['product'] . '</td>';
        echo '<td>' . $row['amt'] . '</td>';
        echo '<td>' . $row['tot'] . '</td>';
        echo '<td>' . $row['comments'] . '</td></tr>';
}

echo "</table>";

thanks!

[cyberRobot]

In the query, have you tried using the ORDER BY clause to sort the matches. I assume that "last matches" means that you are looking for the newest entry(ies), correct?

 

If so, I imagine you have a date field to indicate when a record was added. That field can be sorted in descending order to make the newest entries appear first in the result set. Then you just need to use the LIMIT clause to have the query return the number of results you want.

Edited November 17, 2016 by cyberRobot

[LambPatch]

Yep, it has a timestamp column, but we sell many more of some items than others. So if I were to tell it to get say the last 4 entries, 3 of those may be one product, 1 entry another product and 2 products wouldn't even come up at all because they're aren't in the last 4.

 

I could probably get it to work by having it look for each individual product by writing code specific to each but it seems like it should be able to do this.

1. look through the product column, find unique products (currently there's only 4 but there will be more) .
   • Product item name comes from a dropdown populated from another table so names are uniform
2. Find the last entry on each of the unique products (timestamp)
3. List the product name and the total from that column.

In the end, what would happen is it would bring up the last entry for each product and the total for it so there's a running list of inventory.

[benanamen]

Try

 

SELECT DISTINCT product_name_column, total FROM products

Edited November 17, 2016 by benanamen

[Barand]

SELECT product

  , MAX(timestamp) as lastsale

FROM inventory

GROUP BY product

ORDER BY lastsale DESC

LIMIT 4

[LambPatch]

Barand! You did it! I had to add the tot to it but it works now. I didn't even realize it was possible to "make up new stuff" in mysql! very cool. Thanks so much! 

In the end, it looks like this: 

SELECT product, tot, MAX(timestamp) as lastsale
FROM inventory
GROUP BY product
ORDER BY lastsale DESC
LIMIT 4