group repetitive values

Question

I've the following table called store_items

id store item qty cost sell date
1  A1     aa   12  5   10   2016-04-06
2  A2     cc   10  6   12   2016-04-06 // Store A2 and A3 have same item 
3  A1     bb   15  5   13   2016-04-01 // Store A1 and A3 have same item
4  A3     bb   10  5   13   2016-04-06
5  A3     cc   22  6   12   2016-04-06
6  A1     dd   17  2   12   2016-04-02
7  A1     ee   10  5   10   2016-04-01

Now what I'm expecting the output to be like :

Item cost sell A1 A2 A3
aa   5    10   12 0  0
bb   5    13   15 0  10
cc   6    12   0  10 22
dd   2    12   17 0  0
ee   5    10   10 0  0

As you can see that each item is shown once and its qty are listed below each store, I'm currently feeling lost on how to use group in query Code:

$allstore = $_POST['store']; //Store Name are collected from FORM submit

echo "<table>";
echo "<tr><td align=center>Item Number</td><td align=center>Cost</td><td align=center>Sell</td>";

foreach($allstore as $store => $value){ 
//I've used foreach to find out which store being selected from checkbox form
    echo "<td align=center>".$value."</td>";
}

echo "</tr>";


    $query = "SELECT * from store_items GROUP by item"; //??
    $result = mysql_query($query);

    while($row = mysql_fetch_assoc($result)){

        echo "<tr><td align=center>{$row['item']}</td><td align=center>{$row['cost']}</td><td align=center>{$row['sell']}</td>";

    foreach($allstore as $store => $value){
    echo "<td align=center>".$row['qty']."</td>";
}

    //End of While loop 
    }
echo "</tr>";

What should determine which `cost` and `sell` values should be used? For example, at store A2, cc cost is 6, and at store A3, cc cost is 2. 6 is used in your output. Is that because it's the maximum value, or what? — Don't Panic, Apr 05 '16 at 22:21
I'm sorry, Cost for same item should be the same. I'll edit it now — Ali Hamra, Apr 05 '16 at 22:22
Why are you using database functions that have been deprecated for more than 5 years? They aren't even present in the current version of PHP. [Stop using mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php)! — miken32, Apr 05 '16 at 22:24

Adam Silenko · Accepted Answer · 2016-04-05T22:45:22.277

3

you should use query like this:

SELECT Item, cost, sell
, Sum(CASE WHEN store = 'A1' then qty end) A1
, Sum(CASE WHEN store = 'A2' then qty end) A2
, Sum(CASE WHEN store = 'A3' then qty end) A3
FROM store_items 
GROUP BY Item, cost, sell

EDIT:
query for data:

SELECT Item, cost, sell, store, Sum(qty)
FROM store_items 
GROUP BY Item, cost, sell, store

or

SELECT Item, cost, sell, Sum(qty)
FROM store_items 
WHERE store = 'A1' --for example
GROUP BY Item, cost, sell

edited Apr 05 '16 at 22:45

answered Apr 05 '16 at 22:22

Adam Silenko

3,025
1
14
30

1

Thanks for your answer, but as I've mentioned I'm collecting Stores names as an array taken from previous Form page that have a checkbox with a `name = store`. That's why I used `foreach` – Ali Hamra Apr 05 '16 at 22:31
So, you need two query's, one for columns name, and second for data, true? – Adam Silenko Apr 05 '16 at 22:34
2

@AliHamra you asked for query results in this format, so I don't understand your complaint. Try this and tell us if it doesn't work. – Buttle Butkus Apr 05 '16 at 22:35
This query works as expected. You can replace `A1` etc with your variables. – fusion3k Apr 05 '16 at 22:42
Thanks @AdamSilenko I've used your code and edited to fit my code and it works like a charm. +1 – Ali Hamra Apr 05 '16 at 22:51

group repetitive values

1 Answers1