StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think a better way would be to update the tracking table with the number of items changed. For instance in your example instead of the second entry saying item #12 has 50, you would say it has -150 because that is the change from the previous operation.</p> <p>You could have the script that updates this table take that into account. Get the total number of items using <code>SUM</code> and then update with the change.</p> <pre><code>SELECT SUM(quantity) FROM tracking WHERE item=12; </code></pre> <p>(get the value of that query)</p> <pre><code>$change_value = $new_quantity - $quantity_from_db; </code></pre> <p>Then update the tracking table with the $change_value;</p> <p>To get a report the query is the same as @benisu's. You could do it by date by using a range rather than selecting a particular date. For example get your snapshot on December 5:</p> <pre><code>SELECT item, SUM(quantity) FROM tracking WHERE date <= '2010-12-05' GROUP BY item </code></pre> <p>Hope that helps.</p> <p>EDIT: I see you want them returned by date and total items. Unfortunately I don't believe you can do that in a single query because you have to tell MySQL to add the values together over a range. Grouping by date will give you a snapshot only. Your best bet would be to loop through a range of dates and then issue a query for each one:</p> <pre><code>$array_of_dates = array('2010-12-05', '2010-12-08', '2010-12-11'); foreach( $array_of_dates as $date ) { $query = "SELECT SUM(quantity) FROM tracking WHERE date <= '$date'"; // don't use this unless your date input is static or sanitized! // your database layer of choice here to execute the query echo "$date: $quantity_from_query"; } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload