Avoid using the same query in multiple functions?

[donboe]

For a marketing company, on the personal page of each marketeer I need to output the number of sales (made by the marketeer) from the current week, the commision, which is based on the number of sales made, and the amount of commision a marketeer made (number of sales * commision). Right now I have the following function for the number of sales from the current week:

function get_week(){
    $sql = "SELECT SUM(sales_made)
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";
                
    $stmt = &this->pdo->prepare($sql);
    $stmt->execute(array(1));
    $row = $stmt->fetch();
                                
    $num_of_sales = $row['SUM(sales_made)'];    
    return $num_of_sales;                                
}

I have the functions for the commision (get_commision()) and amount (get_deserves()) as well, but in both I am using the exact same query as in the get_week() function and in the get_deserves() function, I even used the values from the get_commision() function, to come to get results as you can see below.

function get_commision(){
    $sql = "SELECT SUM(sales_made)
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";
                                                                            
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array(1));
    $row = $stmt->fetch();
                                
    $num_of_sales = $row['SUM(sales_made)'];    
              
    if ($num_of_sales <= 250){
        $commision = "€ 1.50";
    }elseif($num_of_sales >= 251 && $num_of_sales <= 500){
        $commision = "€ 1.75";
    }elseif($num_of_sales >= 501 && $num_of_sales <= 750){
        $commision = "€ 2.00";
    }elseif($num_of_sales >= 751 && $num_of_sales <= 1000){
        $commision = "€ 2.25";
    }elseif($num_of_sales >= 1001 && $num_of_sales <= 1250){
        $commision = "€ 2.50";
    }elseif ($num_of_sales >= 1251){
        $commision = "€ 3.00";                
    }
    return $commision;
}

function get_deserves(){

    $sql = "SELECT SUM(sales_made)
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array(1));
    $row = $stmt->fetch();
                                
    $num_of_sales = $row['SUM(sales_made)'];

    if ($num_of_sales <= 250){
        $commision  = "€ 1.50";
        $deserved = $num_of_sales * $commision;
        $deserved = number_format($deserved,2);
    }elseif($num_of_sales >= 251 && $num_of_sales <= 500){
        $commision = "€ 1.75";
        $deserved = $num_of_sales * $commision;
        $deserved = number_format($deserved,2);
    }...............

    etc...........
}

This is working but, I’m sure there is a way more effective way, to get the same results by using the values from get_week() in the other two functions, without having to use the same query again but I can’t think of one

Any advise and/or tips would be highly appreciated.

Thank you in advance!

[xMog]

You could create a function that only returns the result set and use that function within your 3 other functions…

For example:

function getMarketeerCommissions()
    $sql = "SELECT SUM(sales_made)
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array(1));
    $row = $stmt->fetch();
   return $row;
}

And then:

function get_commission() {
  $data = getMarketeerCommission();
  ... your code here...
}  

function get_week() {
  $data = getMarketeerComission();
  $num_of_sales = $row['SUM(sales_made)'];    
  return $num_of_sales;   
}

Did I understand your question correctly?

[donboe]

Hi xMog. I am not sure if you understood the question correctly

@xMog

function get_commission() {
$data = getMarketeerCommission();
… your code here…
}

What do yoe mean with: …your code here?

The num_of _sales are correct, how could I use those values in the other two functions without repeating the same function again

[ahundiak]

function get_commision()
{
    $num_of_sales =  get_week();  // This is all you need to get number of sales

    if ($num_of_sales <= 250){ ...

[xMog]

I read your post a little bit quickly…

So, the number of sales is always the same. Why not make a function ‘get_number_of_sales()’?

Basically, you would just rename the function get_week() for get_num_sales().

function get_num_sales(){
    $sql = "SELECT SUM(sales_made)
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";
    $stmt = &this->pdo->prepare($sql);
    $stmt->execute(array(1));
    $row = $stmt->fetch();
    $num_of_sales = $row['SUM(sales_made)'];    
    return $num_of_sales;                                
}

You could also leave the name ‘get_week’ but IMO this function returns the number of sales, not ‘weeks’? Maybe call it get_num_sales_for_week() or something like that.

Anyway, this function returns the number of sales, which is what you need in your other functions?

So, you could change your 2 functions for:

function get_commision(){       
    $num_of_sales = get_num_sales();
    if ($num_of_sales <= 250){
        $commision = "€ 1.50";
    }elseif($num_of_sales >= 251 && $num_of_sales <= 500){
        $commision = "€ 1.75";
    }elseif($num_of_sales >= 501 && $num_of_sales <= 750){
        $commision = "€ 2.00";
    }elseif($num_of_sales >= 751 && $num_of_sales <= 1000){
        $commision = "€ 2.25";
    }elseif($num_of_sales >= 1001 && $num_of_sales <= 1250){
        $commision = "€ 2.50";
    }elseif ($num_of_sales >= 1251){
        $commision = "€ 3.00";                
    }
    return $commision;
}
function get_deserves(){
    $num_of_sales = get_num_sales();
    if ($num_of_sales <= 250){
        $commision  = "€ 1.50";
        $deserved = $num_of_sales * $commision;
        $deserved = number_format($deserved,2);
    }elseif($num_of_sales >= 251 && $num_of_sales <= 500){
        $commision = "€ 1.75";
        $deserved = $num_of_sales * $commision;
        $deserved = number_format($deserved,2);
    }...............
    etc...........
}

[StarLion]

I’m not entirely sure why you’re calling all these functions. If the information’s gonna be displayed regardless, seems a bit of a waste to hand things back and forth.
Execute the query once (any time you execute a query more than once should be a red flag in your coding process!); store the results, and if necessary to keep everything in functions, pass the relevant information into the function as a parameter.

Maths time!
You’re using a system that has a pattern, and can be simplified as follows:

$commission = ($num_of_sales > 1250) ? 3 : 1.5 + (floor($num_of_sales / 250) * 0.25);

(and then $deserved = $num_of_sales * $commission is globally true, and we can get rid of all those if…elseif…else’s.)

(Note: $commission should be a FLOAT. Not a string. When you want to display the commission, use the money_format() function of PHP to show it off.)

[DaveMaxwell]

What we’re missing is how you’re calling this information. Are these calculated all at once, or are they called at separate time? The answer changes depending on how it’s called.

If it’s separately, then the three method approach is appropriate. If it’s all at once, then the single hit approach is best:

function get_sales_info($marketeerID){
    $sql = "SELECT SUM(sales_made) AS TotalSales
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";
    $stmt = &this->pdo->prepare($sql);
    $stmt->execute($marketeerID);
    $row = $stmt->fetch();
    $num_of_sales = $row['TotalSales'];    

    if ($num_of_sales <= 250){
        $commision = 1.50;
    }elseif($num_of_sales >= 251 && $num_of_sales <= 500){
        $commision = 1.75;
    }elseif($num_of_sales >= 501 && $num_of_sales <= 750){
        $commision = 2.00;
    }elseif($num_of_sales >= 751 && $num_of_sales <= 1000){
        $commision = 2.25;
    }elseif($num_of_sales >= 1001 && $num_of_sales <= 1250){
        $commision = 2.50;
    }elseif ($num_of_sales >= 1251){
        $commision = 3.00;
    }

	$deserved = $num_of_sales * $commision;
	$deserved = number_format($deserved, 2);

	// return an array with all the appropriate salesInformation
	$salesArray = array();
	$salesArray["sales"] = $num_of_sales;
	$salesArray["commission"] = "€ " + $commission;
	$salesArray["deserved"] = $deserved;
	
    return $salesArray;
}

[StarLion]

@DaveMaxwell

$salesArray[“commission”] = "€ " + $commission;

ewww, Dave.

[DaveMaxwell]

@StarLion

ewww, Dave.

So sue me…I don’t do PHP anymore…

OK, to make StarLion happy, here would be a happy happy joy joy combination of his lines and mine:

function get_sales_info($marketeerID){
    $sql = "SELECT SUM(sales_made) AS TotalSales
              FROM marketeer_sales
             WHERE marketeer_id = ?
               AND WEEKOFYEAR(sales_date) = WEEKOFYEAR(CURDATE())";
    $stmt = &this->pdo->prepare($sql);
    $stmt->execute($marketeerID);
    $row = $stmt->fetch();
    $num_of_sales = $row['TotalSales'];    

	$commission = ($num_of_sales > 1250) ? 3 : 1.5 + (floor($num_of_sales / 250) * 0.25);

	// to get €
	setlocale(LC_MONETARY, 'en_GB');
	
	// return an array with all the appropriate salesInformation
	$salesArray = array();
	$salesArray["sales"] = $num_of_sales;
	$salesArray["commission"] =  utf8_encode(money_format('%n', $commission));
	$salesArray["deserved"] = $number_format($num_of_sales * $commision, 2);		
        return $salesArray;
}

[StarLion]

You know i do it with love, Dave

A few final tweeks though: the currency of Great Britain is the Pound not the Euro. Try

setlocale(LC_MONETARY, 'de_DE.utf8');

instead (the .utf8 makes sure you get the symbol, rather than the letters “EUR”).

@DaveMaxwell

$salesArray[“deserved”] = $number_format($$num_of_sales * $commision, 2);

that double $ is gonna cause issues; it should just be a single.

[DaveMaxwell]

@StarLion

You know i do it with love, Dave

Yeah, I know. That’s why you got the tongue instead of some other rude emoji

@StarLion

A few final tweeks though: the currency of Great Britain is the Pound not the Euro. Try

setlocale(LC_MONETARY, ‘de_DE.utf8’);

instead (the .utf8 makes sure you get the symbol, rather than the letters “EUR”).

Huh. I got that code from a different site (I’m US, so I didn’t know how to do it). It said it would return the pound symbol. and not the Euro.

@StarLion

that double $ is gonna cause issues; it should just be a single.

That was a typo from combining lines to get rid of an unneeded variable. I fixed this one…

[StarLion]

@DaveMaxwell

It said it would return the pound symbol. and not the Euro.

It would. But the OP wants the Euro, not the pound.

[DaveMaxwell]

@StarLion

It would. But the OP wants the Euro, not the pound.

sigh. I shoulda stayed in bed.

[donboe]

Hi guys thank you all for the replies, I am looking into all possibilities right now!

@StarLion

@StarLion

Maths time !You’re using a system that has a pattern, and can be simplified as follows:
$commission = ($num_of_sales > 1250) ? 3 : 1.5 + (floor($num_of_sales / 250) * 0.25);
(and then $deserved = $num_of_sales * $commission is globally true, and we can get rid of all those if…elseif…else’s.)

I try to understand this math. The first part

@StarLion

$commission = ($num_of_sales > 1250)

and the last part

@StarLion

(floor($num_of_sales / 250) * 0.25);

I understand. But I am confused about the middle part

@StarLion

? 3 : 1.5 +

Can you please explain?

Thank you in advance

[DaveMaxwell]

@donboe

I understand. But I am confused about the middle part

StarLion said:
? 3 : 1.5 +

Can you please explain?

Thank you in advance

It’s short hand for an if statement. Basically, it’s saying IF the number of sales is over 1250, use 3. Otherwise use the formula.

[donboe]

@DaveMaxwell

It’s short hand for an if statement. Basically, it’s saying IF the number of sales is over 1250, use 3. Otherwise use the formula.

Do you mean this formula:

1.5 + (floor($num_of_sales / 250) * 0.25);

[DaveMaxwell]

yes

[donboe]

@DaveMaxwell

yes

Hi Dave, but where is the 1.5 standing for?

Edit. I see now what it means 3 is the € 3.00 and 1.5 the € 1.50, thank you so much

[felgall]

@donboe

I try to understand this math. The first part

StarLion said:
$commission = ($num_of_sales > 1250)

and the last part

StarLion said:
(floor($num_of_sales / 250) * 0.25);

I understand. But I am confused about the middle part

StarLion said:
? 3 : 1.5 +

You have broken it up incorrectly so you can’t understand the first and last part.

Here’s the way you break up a ?: statement like that into it’s component parts. It brreaks into three parts marked by the ? and the : and the part before the = applies to the parts on both sides of the?:
The if portion of the ?: is:

if ($num_of_sales > 1250)

The true part is:

$commission = 3;

The false (else) part is:

$commission = 1.5 + (floor($num_of_sales / 250) * 0.25);

[donboe]

Like I said felgall, the entire middle part was confusing for me but after Dave’s answer I understood the principle. But thank you for the clear explanation