PHP&MySQL: issue using php for and mysql query

Question

I would calculate the total profit of a DataBase for each month using 12 different function [one for each month(yes its probably not a nice solution but I am learning step by step )]

This is the .php code (x12 times)

$January="SELECT JanuaryProfit()";     //Function
echo "<b>January Profit: E</b>";
$January = mysql_query($giugno,$conn)
          or die("Query failed: " . mysql_error($conn));
$num_rows=mysql_num_rows($January);

if (! $num_rows)
      echo "<p>0</p>";
else {
      while ($row = mysql_fetch_row($January)) {
            $price=$row[0];
            echo "$price";
      };
}

Function JanuaryProfit() is declared this way [MySQL]:

BEGIN
DECLARE PriceTOT FLOAT;
SELECT SUM(o.PrezzoTot) INTO PriceTOT
FROM orders o
WHERE o.Data BETWEEN '2012-01-01' AND '2012-01-31';
RETURN PriceTOT;
END

And so I've declared singularly february,march,...,december.

I wouldn't paste 12 times the first code into the .php page.I've tried using a for cicle that use different function every time but i cannot figure how to solve this,correctly.Any suggest?

All I wanna do by now is changing the php part only,using a loop.

to clarify you have a procedure MarchProfit() and AprilProfit() etc in mysql? — NappingRabbit
– NappingRabbit, Commented Dec 5, 2012 at 20:57
This is not just inefficient, it's masochistic. That's a new function every month, every year. Here is the LASTDAY() function which is probably your stumbling block. — Sammitch
– Sammitch, Commented Dec 5, 2012 at 21:02

Sammitch · Accepted Answer · 2012-12-05 21:35:10Z

1

Oh my dear, sweet, flying spaghetti monster. Stop whatever you are doing, then burn it, and then use something like the below to avoid writing a function for every month of every year from the beginning of time until the end.

SELECT 
  YEAR(o.Data) as 'year',
  MONTH(o.Data) as 'month',
  SUM(o.PrezzoTot) as 'total'
FROM orders o
GROUP BY YEAR(o.Data), MONTH(o.Data)

I assume at some point you were having difficulty figuring out the last day of the month programmatically, so here's another link to LASTDAY() which is also the documentation page for all of the massively useful date functions.

edit

define('masochism', TRUE);

$ohgodwhy = array('January','February','March','April','May','June','July','August','September','October','November','December');

$aaahhh = 'SELECT %sProfit()';

foreach( $ohgodwhy as $pleaseno ) {
    $itburns = sprintf($aaahhh, $pleaseno);
    $hidethebodies = mysql_query($itburns) or die("Couldn't hide the bodies because: " . mysql_error());
    // etc...
}

edited Dec 5, 2012 at 21:35

answered Dec 5, 2012 at 21:09

Sammitch

32.5k7 gold badges57 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NappingRabbit Over a year ago

I agree with your solution but he did specify that he wanted to change ONLY the php file. (I still +1 your answer)

Sammitch Over a year ago

@NappingRabbit I've updated my answer with some of the finest code ever written.

Collectives™ on Stack Overflow

PHP&MySQL: issue using php for and mysql query

1 Answer 1

edit

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

edit

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related