Adding SQL table vaules in PHP?

[ncncnc]

Hi all,

 

I'm working on a webpage that dynamically shows sales of products over a number of years. It's going well apart from the data isn't showing as I want it to. The data is showing the number of sales per month in a given year.

 

What I want to do is add up all sales of the same product within a year to give one row for each product for each year. The below screenshot shows what my current output is.

 

 

I want to be able to have one row showing sales all year rather than month by month.

 

  <?php 

error_reporting(E_ERROR | E_PARSE); 

$desiredProduct = $_POST['product'];
$server = 'SQL2008';
$connectionInfo = array( "Database"=>"rde_400792");
$conn = sqlsrv_connect($server,$connectionInfo);


$describeQuery="select ProductCode , SalesVolume, Year, Month from MonthlySales where ProductCode = $desiredProduct";
$results = sqlsrv_query($conn, $describeQuery);

echo '<table border="1" BORDERCOLOR=Black>';
echo '<tr><th bgcolor = "LightBlue">Product Code</th><th bgcolor = "LightBlue" >Sales Volume</th><th  bgcolor = "LightBlue">Month</th><th bgcolor = "LightBlue">Year</th></tr>';


while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) 
{
    echo '<tr>';
echo '<td >' .$row['ProductCode'].'</td>'; 
echo '<td>' .$row['SalesVolume'].'</td>'; 
echo '<td >' .$row['Month'].'</td>'; 
echo '<td>' .$row['Year'].'</td>'; 
echo '</tr>';
} 

echo '</table>';
sqlsrv_close($conn); 

 

Can somebody help me?

[seanlim]

You can do this either via PHP or MySQL. But since your data is already nicely stored in MySQL, I would suggest using MySQL for this:

 

select ProductCode , SUM(SalesVolume) as SalesVolume, Year from MonthlySales GROUP BY ProductCode, Year

 

Alternatively, if you want a PHP solution for any reason, you could maintain an array of the sales volume by product and year. Then, loop through the values of each row of your query, and add the sales volume for each month to the correct array entry.

 

[ncncnc]

Thanks a lot for the reply.

 

It's on the right track but now when I try and filter it it get this error message:

 

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\1.php on line 51

 

I know this means there's shomething wrong with my query.

 

The code I'm trying is

 

 $describeQuery="select ProductCode , SUM(SalesVolume) as SalesVolume, Year from MonthlySales GROUP BY ProductCode, where year = 1990";  

 

Any ideas?

[seanlim]

i don't think that is valid MySQL syntax!

 

You will need:

 

$describeQuery="select ProductCode , SUM(SalesVolume) as SalesVolume, Year from MonthlySales WHERE Year=1990 GROUP BY ProductCode, Year";

[ncncnc]

Thanks a lot!

 

It works now.