Total margin for each month... (using MySQL sum())

[Julan]

Hi friends,

 

I'm looking to try and echo the profits from a MySQL "totals" table for each month of the year... the table bascically consists of a "totals" column which contains the total amount spent on each order, a "suppliertotals" column which contains the total amount that it costs us to buy the goods for that order and an "orderdate" column which stores the order date as "d/m/y" (so 01/01/01).

 

To work out the total margin from the complete table, I calculate the sum of totals minus the sum of suppliertotals. This is our margin.

 

I'm currently doing the calculation with MySQL's sum() function, and using PHP explode to get the date, but I'm obviously doing something wrong since PHP is returning the same month several times with different amounts...

 

Let me show you the code I have; I presume I need to use some kind of foreach statement, but I'm not sure where or how... hoping you can help!

 

    if (!$con)  { die('Could not connect: ' . mysql_error()); }

    mysql_select_db("onetelec_wsshop", $con);

     $result=mysql_query("SELECT orderdate FROM totals", $con);

    while($row = mysql_fetch_array($result))

    {

    $ordmonth = explode("/", $row['orderdate']);

    $ordmonth = $ordmonth[1];



    switch ($ordmonth) {

    case "01":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for January<br /><br />";

    break;



    case "02":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for February<br /><br />";

    break;



    case "03":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for March<br /><br />";

    break;



    case "04":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for April<br /><br />";

    break;



    case "05":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for May<br /><br />";

    break;



    case "06":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for June<br /><br />";

    break;



    case "07":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for July<br /><br />";

    break;



    case "08":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for August<br /><br />";

    break;



    case "09":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for September<br /><br />";

    break;



    case "10":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin += $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for October<br /><br />";

    break;



    case "11":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin += $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for November<br /><br />";

    break;



    case "12":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin += $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for December<br /><br />";

    break;

    }

}

[Julan]

Duuuh! 

 

	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/01/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for January<br /><br />";


	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/02/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for February<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/03/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for March<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/04/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for April<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/05/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for May<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/06/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for June<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/07/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for July<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/08/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for August<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/09/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin = $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for September<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/10/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin += $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for October<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/11/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin += $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for November<br /><br />";



	$stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/12/%'", $con);

while($strow = mysql_fetch_array($stresult))

{

$margin += $strow['sum(total)'] - $strow['sum(stotal)'];

}

echo $margin." for December<br /><br />";