SharkBait Posted September 27, 2006 Share Posted September 27, 2006 I am trying to count up how many instances something happens.I have an query that pulls values from a table. The values are Items and Qty. What I'm trying to do is create an array of the Items and their total quantities.[code]<?php$UNITS = array();$UNITS[$result['Item']] .= $result['Qty'];?>[/code]Now this just adds the number to the number that already exists in the array.How do I add the values of waht is in the array to the new value and put it back into the array?List is something like:Widget1 - 14Widget2 - 3Widget2 - 5Widget3 - 2Widget1 - 6So I would have:20 Widget1s8 Widget2s2 Widget3 Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 27, 2006 Share Posted September 27, 2006 why not do that in your query?[code]SELECT SUM(qty) AS qty, item FROM table GROUP BY item;[/code] Quote Link to comment Share on other sites More sharing options...
SharkBait Posted September 27, 2006 Author Share Posted September 27, 2006 I'd have to do a seperate query.Right now my query is this:[code]SELECT * , COUNT(*) FROM RMAs GROUP BY RMANumber[/code]There can be multiple items for a single RMANumber. Can I somehow incorporate the SUM in to my SELECT? Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 27, 2006 Share Posted September 27, 2006 if you're summing the same thing you're counting, sure:[code]SELECT RMANumber, COUNT(RMANumber) AS num, SUM(qty) AS qty FROM table GROUP BY RMANumber[/code] Quote Link to comment Share on other sites More sharing options...
SharkBait Posted September 27, 2006 Author Share Posted September 27, 2006 Maybe I explained it wrong or I'm a bit confused.an RMA entry might look like thisLine # | Unit |RMANumber | Notes----------------------------------------------1 | Widget1 | 9090999 | Blah----------------------------------------------2 | Widget2 | 9090999 | Mroe Blah----------------------------------------------3 | Widget1 | 9090999 | WOo Blah----------------------------------------------So in the RMA above it has 2x Widget1s and 1x Widget2sMy main query looks like this:[code]SELECT *, COUNT(*) FROM RMAs WHERE Received >= '{$MyDate}' AND Received != '0000-00-00 00:00:00' GROUP BY RMANumber[/code]Where MyDate is a user inputed date like 2006-06-01 00:00:01Then I loop through the result to print out entries and group them by their RMANumbers and output them to the screen. At the same time I need a running total of how many Units of each time this loops sees. Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 27, 2006 Share Posted September 27, 2006 ah, ok, i think i see... so, something like this might help:[code]<?php$totals = array();while ($x = mysql_fetch_array($sql)) { $item = $x['unit']; if (isset($totals[$item])) $totals[$item]++; // increment it since it's already set else $totals[$item] = 1; // otherwise, set it to one since it's the first one we've seen}?>[/code]does that help at all? Quote Link to comment Share on other sites More sharing options...
SharkBait Posted September 27, 2006 Author Share Posted September 27, 2006 That makes sense. I think because I am grouping it by RMANumber it won't see the individual items, so I'll have to do another query after this one and then use your above code to tally the items up.Thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.