Applying Front-End Logic to a Database Query

PHP
1

Continuing the discussion from MySQL Getting two different total amounts based on criteria - #49 by jmyrtle.
…

$total_per_company = array();
//query code..
//start looping through results.
$total_per_company[$data->customer] = (isset($total_per_company[$data->customer])) ? $total_per_company[$data->customer] + $data->extra_charge : $data->extra_charge;

(If you don’t understand ternary logic, it’s basically an if-then-else crushed down into a single line: (if) ? then : else; )

2

Sorry, but I don’t understand where this is going?

3

Okay. Let me see if i can abstract this a bit into a coding challenge to help you understand.

Forget everything about databases for a moment. (Yes i’m going to ask the admins to shard this post off into a new thread.)

Let’s go back to just plain PHP. No queries, no databases… just PHP and data.

Name Co Value
Joe ABC 100
Joe XYZ 2000
Sally ABC 10000
Sally XYZ 200
Brian ABC 500
Brian XYZ 7000

Here’s my data. It has granularity to the level of per-salesperson-per-customer. Here it is in Array form in PHP:

<?php
$mydata = array(array("name"=>"Joe","co" => "ABC", "value" => "100"),
array("name"=>"Joe","co" => "XYZ", "value" => "2000"),
array("name"=>"Sally","co" => "ABC", "value" => "10000"),
array("name"=>"Sally","co" => "XYZ", "value" => "200"),
array("name"=>"Brian","co" => "ABC", "value" => "500"),
array("name"=>"Brian","co" => "XYZ", "value" => "7000"));

$sally = 0;
$ABCCo = 0;
$sales_over_1000

foreach($mydata AS $data) {
// Code goes here.
}

echo $sally."<br>".$ABCCo."<br>".$sales_over_1000

Add code to fill the variables such that at the end of the loop:
$sally contains the total amount of sales Sally did.
$ABCCo contains the total amount of sales that ABCCo had;
and $sales_over_1000 contains the total of all sales over 1000 made by Brian.

All of the data necessary is available, and your code does not need to go anywhere outside of the loop.

4

So this is the data…

This is the variable definition…

and this (essentially) is the query as well as the final output?

5

(Many thanks to Technobear for the split.)

The ‘query’ is what gave you the $mydata array.

If I equate it to your code (other thread, for anyone reading), $mydata ~= $stmt. (One is an array, the other is a PDOStatement with a waiting result array)

the foreach($mydata as $data) here equates to your while($stmt->fetch()) .

So what i’m trying to get you to see is that while looping through the results, you can accumulate variables in PHP that you can use later.

6

Yes that is true. I can use variables to come up with totals. I used them in a previous application I wrote like this:

<p>Total Delegates Recorded:
<b style="color: #d8342b;">

<?php
$total = $present['count'] + $absent['count'];
echo $total;
?>
7

Correct.

We’ve established that Sally’s sales start at 0. Because Sally doesnt get to cheat. ($sally = 0)

Inside the loop, we’re going to walk through all the data. We need to add the sales to our total;
BUT, we only want to do it if the current sale record we’re looking at ($data) belongs to Sally (if $data['name'] == "Sally").

8

So I would need to do the same thing to all of the salesmen?

9

In a way, yes. We can use variables to help us though.

Let’s go back to the line I gave you in the first post of this thread:

$total_per_company[$data->customer] = (isset($total_per_company[$data->customer])) ? $total_per_company[$data->customer] + $data->extra_charge : $data->extra_charge;

Or, if I break it back down out of the ternary format:

if(isset($total_per_company[$data->customer])) {
  $total_per_company[$data->customer] = $total_per_company[$data->customer] + $data->extra_charge;
} else { 
  $total_per_company[$data->customer] = $data->extra_charge;
}

So here, we need to talk about trying to reference an array key that doesnt yet exist.

$total_per_company is an array. it will be an associative array (meaning the keys of the array are strings).

At the end, we might have $total_per_company['ABC'], which holds the total for ABC Corp, $total_per_company['XYZ'] for XYZ Corp, etc.

But we don’t know ahead of time what company names exist. instead, what we do is, for each row in our data, we look at the $data[‘co’] name.

$total_per_company[$data[‘co’]] will be “the array entry for whatever company the current row is talking about.”

We dont know if there’s already an entry in our array for that company - the array starts empty, after all, so either this row is the first time we’ve encountered ABC Corp, or it’s not.
If it is NOT the first time we’ve seen ABC corp, we want to take our previous total, and add the new value.
If it IS the first time we’ve seen ABC corp, we create a new entry in the array for that corporation, and we can give it the value of the current row. Because 0+X is X.

So, we need to check if the entry exists already: isset($total_per_company[$data['co']]) (“Is the entry for this row’s company set/does it exist”) will return true (it exists) or false (it doesnt).

Let me know if i’m losing you here.

10

Okay, that makes a little bit more sense, but where does this go in the code? Am I substituting this for the query or am I adding it in with the query?

12

this code would go inside your query result loop.

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.