-1

I have a database of a restaurant and I want to generate the daily sales report of the products sold. So basically the columns are:

Product name | Qty | Amount

Now when I apply a while loop using php it shows me all the records available in database such as if I have Green Tea 15 times in a table it will display it 15 times with quantity 1.

I simply want to merge it and want to show quantity as 15.

My PHP code is:

<?php

    $bt = $_POST['bt'];
    $check_in = $_POST['check_in'];

    if($bt != '') {
        $g = mysql_query("select * from bill2 where date='{$check_in}'");
        echo '
            <div class="col-md-12">
            <div class="box">
            <div class="box-title">
            </div>
            <div class="box-body">
            <table class="table table-bordered">
                    <tr>
                    <th>#</th>
                    <th>Item Name</th>
                    <th>Quantity Sold</th>
                    <th>Total Amount</th>
                    </tr>';

        $x=0;
        while($gff = mysql_fetch_assoc($g)) {
            $x++;

            echo '<tr>
                    <td>' . $x . '</td>
                    <td>' . $gff['itemid'] . '</td>
                    <td>' . $gff['qty'] . '</td>
                    <td>' . $gff['amount'] . '</td>
                  </tr>';
        }       
        echo '</table></div></div></div>';

    }

?>

output:

#   Item Name        Quantity   Total Amount

1   Green Mint Tea      1            30

2   Green Mint Tea      1            30

3   Vanilla Tea         1            35

4   Black Tea           1            30

expected output:

#   Item Name        Quantity   Total Amount
1   Green Mint Tea      2            60
2   Vanilla Tea         1            35
3   Black Tea           1            30
Rizier123
  • 58,877
  • 16
  • 101
  • 156
Amit Bhatnagar
  • 1
  • 5

3 Answers3

2

You can easily do this using GROUP BY

SELECT `product_name`, count(`product_name`) AS `product_count`, sum(`amount`) AS `AMT`
FROM `menu_items`
GROUP BY `product_name`

For your PHP you would use the variable names you're getting from the database. I matched the below example with the query I wrote above - 

echo '<tr>
      <td>'.$x.'</td>
      <td>'.$gff['product_name'].'</td>
      <td>'.$gff['product_count'].'</td>
      <td>'.$gff['AMT'].'</td>
      </tr>';

In addition you need to stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO.

Community
  • 1
  • 1
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • Hi sir, I have edited my question to elaborate my conflict.. kindly help with it. Thanks a ton in advance. – Amit Bhatnagar Apr 20 '15 at 21:06
  • Sir i want the PHP sample could you please help me with php code snippet. I am not able to get the answer you posted. Please refer to my php code. – Amit Bhatnagar Apr 20 '15 at 21:21
  • Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). Your original question asked about the SQL, the PHP would be a new question. – Jay Blanchard Apr 20 '15 at 21:23
  • I will keep your tips in mind. thanks sir.. But unfortunately i can not open another thread of question because of time limit of 90 minutes. It would be a great help if you can solve my issue right here. Thanks. – Amit Bhatnagar Apr 20 '15 at 21:34
  • Thanks a lot sir for your precious time. – Amit Bhatnagar Apr 20 '15 at 21:44
0

What you probably want to do is use one query to get your results:

select Productname, sum(Qty), sum(Amount) from table GROUP BY Productname
Michael
  • 1,247
  • 1
  • 8
  • 18
0

Updated per your comment below.

Change

select * from bill2 where date='{$check_in}'

to

select itemid, SUM(qty), SUM(amount) from bill2 where date='{$check_in}' GROUP BY itemid

Then in the while loop you can change it to the following and it should work.

<td>'.$gff[0].'</td>
<td>'.$gff[1].'</td>
<td>'.$gff[2].'</td>
dstudeba
  • 8,878
  • 3
  • 32
  • 41