Looking to add a column and show results in end?

Question

I am looking to add a qty column results fetched from MySQL database. Also I am looking to group the results by center and medicine.

I'd like to show the next results in new row within a HTML table. check this again

       SNoMedicineName  QTY Center
   3    7            3  Bhop
   4    7            3  Bhop
   5    7            3  Bhop
   6    1            2  Bhop
   TOTAL QTY2 TOTAL QTY9 
   6Medicine1CenterBhopalQTY2 3Medicine7CenterBhopalQTY3   
   //Here i am looking for sno to 1 med name to 7 qty to 9 cent to bhop
   then    sno to 2 med name to 1 qty to 2 cent to bhop

My code is

    <?php

include_once('db.php');

$sql = "SELECT * FROM medical_new  ";

if(isset($_Post['submit']));

{
$cent=$_POST['center'];
$sql .= "where center= '{$cent}'";
}


$query=mysql_query($sql) or die (mysql_err());



?>

<table border='1'>
<tr>
<th><b>SNo</b></th>
<th><b>MedicineName</b></th>
<th><b>QTY</b></th>
<th><b>Center</b></th>
</tr>
<?php
while( $row = mysql_fetch_array($query) )
 {
  echo "<tr>"; 
echo "<td>" . $row['CustomerID'] . "</td>";
echo "<td>" . $row['MedicineName'] . "</td>";
echo "<td>" . $row['Qty'] . "  </td>";
echo "<td>" . $row['Center'] . "</td>";
  echo "</tr>";
}
echo "</table>";

?>

Can someone give me some tips or suggestions on how to do this? Thanks.

Table

    CustomerID  MedicineName    Date    Qty     RRP     Net RRP     MRP Price   Net MRP     Center
    1       1           0000-00-00   3      250     750             360        1080     2
    2       3           0000-00-00   3      111     333              211        633     3
    3       7           0000-00-00   3      222     666              211       633     Bhop
    4       7           10/10/2013   3      222     666              211       633      Bhop

[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). — zessx, Oct 10 '13 at 07:17
@zessx so i should replace mysql with mysqli in all scripts or need to add a extra code — Eugene, Oct 10 '13 at 08:02
It's a little bit more complex. I suggest you to have a look on [PHP doc](http://php.net/manual/en/book.mysqli.php). There's nothing hard, you just need to use it once to understand how it works. — zessx, Oct 10 '13 at 08:09
___I will change mysql with mysqli___ trust me it wont save you from ___sql injection___ .. you need to escape/sanitize all request properly or better to use Prepared statements and IMHO use PDO — NullPoiиteя, Oct 10 '13 at 08:28
coming to my prob can anyone give me a solution.Below cristina gave me solu but not working — Eugene, Oct 10 '13 at 08:31

Cristiana Chavez · Answer 1 · 2013-10-10T08:54:40.847

0

this can help

<table border='1'>
<tr>
<th><b>SNo</b></th>
<th><b>MedicineName</b></th>
<th><b>QTY</b></th>
<th><b>Center</b></th>
</tr>
<?php
while( $row = mysql_fetch_array($query) )
 {
  echo "<tr>"; 
echo "<td>" . $row['CustomerID'] . "</td>";
echo "<td>" . $row['MedicineName'] . "</td>";
echo "<td>" . $row['Qty'] . "  </td>";
echo "<td>" . $row['Center'] . "</td>";
  echo "</tr>";
}
////
 $sql2 = "SELECT *,SUM(Qty)AS totalQty  FROM medical_new where center= '{$cent}' GROUP BY MedicineName";
 $query2=mysql_query($sql2) or die (mysql_err());
 while( $row = mysql_fetch_array($query) )
 {
 echo "<tr>"; 
echo "<td>" "</td>";
echo "<td></td>";
echo "<td></td>";
echo "<td>TOTAL QTY" . $row['totalQty'] . "  </td>";

  echo "</tr>";
}
echo "</table>";

?>

edited Oct 10 '13 at 08:54

answered Oct 10 '13 at 07:28

Cristiana Chavez

11,349
5
55
54

Thanks,** do i need this – Eugene Oct 10 '13 at 07:35
hi @Cristiana214 ,Parse error: syntax error, unexpected '*' in if i remove that he Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, – Eugene Oct 10 '13 at 07:37
$sql2 = "SELECT *,SUM(Qty)AS totalQty FROM medical_new GROUP BY center ,MedicineID "; there is a typo error in ** – Cristiana Chavez Oct 10 '13 at 08:19
no its not working and here we have MedicineName and not MedicineID – Eugene Oct 10 '13 at 08:28
Please explain why this will help and why code in question is not working – NullPoiиteя Oct 10 '13 at 08:30
1Medicine1Center2TOTAL QTY3 2Medicine3Center3TOTAL QTY3 6Medicine1CenterBhopalTOTAL QTY2 3Medicine7CenterBhopalTOTAL QTY9 its adding up in a false way i need totalQty 9 – Eugene Oct 10 '13 at 08:36
@Cristiana214 thanks pl check above what i am looking for.I have updated – Eugene Oct 10 '13 at 09:21
with display of Sr No – Eugene Oct 10 '13 at 09:23
SELECT *,SUM(Qty)AS totalQty FROM medical_new where GROUP BY MedicineName – Cristiana Chavez Oct 10 '13 at 09:45
1

SELECT *,SUM(Qty)AS totalQty FROM medical_new GROUP BY MedicineName; another typo i deleted the where now – Cristiana Chavez Oct 10 '13 at 11:21

Looking to add a column and show results in end?

1 Answers1