MySQL Create Commission Reports per Salesperson

PHP
82

I tried this, but it didn’t change:

if(empty($data->customer) {
    $this->Cell(20, 10, ' ', 1, 0, 'C');
} else {
   $this->Cell(20, 10, $data->name, 1, 0, 'C');

image

I know it’s because of the word “Total” in the customer column. So I tried changing the query a little bit:

if($data->customer = 'Total') {
    $this->Cell(20, 10, ' ', 1, 0, 'C');
} else {
   $this->Cell(20, 10, $data->name, 1, 0, 'C');

but this changes the entire name column, not just the one value I need. Am I on the right track?

83

you’re missing a ) in that line.

Suddenly we’ve changed to having the word total there. Okay… you keep moving the goalposts.

Your problem now is that = is a declaration. == is a comparator.

84

That did the trick. However, I’m working on something else and I feel like it is something so simple but I can’t figure it out.

The header text needs to be changed from Total to the person’s name.

This code is what I managed to come up with. This code comes up with the name:

function viewAll() {
		global $pdo;
		$this->SetFont('Arial', '', 8);
		$sql = "SELECT DISTINCT initials FROM salespeople ORDER BY initials";
		$stmt = $pdo->prepare($sql);
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		foreach($result as $initials_ary) {
			$initials = implode(" ", $initials_ary);
			$name = implode(" ", $initials_ary);
			//var_dump($name);
	    	$this->AddPage('L', 'Letter', 0);
				$this->headerTotals($name);
			$this->viewTotals($initials);
    	}
	}

and this code puts it in place:

function headerTotals($name) {
		$x = $this->GetX();
		$this->SetX(70);
		$this->SetFont('Arial', 'B', 11);
		$this->Cell(135, 10, $name, 1, 0, 'C');
		$this->Ln();
		$this->SetX(70);
		$this->SetFont('Arial', 'B', 11);
		$this->Cell(20, 10, 'Cust. #', 1, 0, 'C');
		$this->Cell(70, 10, 'Name', 1, 0, 'C');
		$this->Cell(25, 10, 'Ttl. Sales', 1, 0, 'C');
		$this->Cell(20, 10, 'Ttl. Comm.', 1, 0, 'C');
		$this->Ln();
		$this->SetX($x);
	}

The problem I’ve got is that it grabs the data, but the table header is now showing the initials of the person rather than the name like this:

image

If I try to change the query to show the name, the query does not grab any data.

image

What am I doing wrong?

85

It did exactly what you told it to do.

86

Well, yeah… but I need it to show the name instead. So I change the query from

SELECT DISTINCT initials FROM salespeople ORDER BY initials;

to

SELECT DISTINCT name FROM salespeople ORDER BY initials;

and it doesn’t show any data.

87

Let me guess, you tried to stick $name in there, and it didnt work because your WHERE clause is now trying to use the name when the table doesn’t use the name.

88

Yeah, I guess so. So, I need to make a separate query for the table header?

89

well not necessarily, you can pull both the initials and name with a single query; feed the name into the output for the header, and feed the initials into your working commission query.

90

That’s what I’m trying to do with this function:

function viewAll() {
		global $pdo;
		$this->SetFont('Arial', '', 8);
		$sql = "SELECT DISTINCT initials FROM salespeople ORDER BY initials";
		$stmt = $pdo->prepare($sql);
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		foreach($result as $initials_ary) {
			$initials = implode(" ", $initials_ary);
			$name = implode(" ", $initials_ary);
			//var_dump($name);
	    	$this->AddPage('L', 'Letter', 0);
				$this->headerTotals($name);
			$this->viewTotals($initials);
    	}
	}

but I can’t figure it out.

91

(hint: you’re not doing that.)

===

this is not what you want to do with your result. implode takes all the elements of an array and smashes them together. Right now, your array is:
$initials_ary = array( 'initials' => "Brian Konoff" );
So if you take 1 thing, combine it with all the other things, you end up with… the same thing you started with. Because there’s only one thing.
If you fix your query, then you’ll be pulling two things (initials and name). So smashing them together doesn’t help you.
Instead of trying to declare new variables, put into these lines:

the correct reference to the individual members of the $initials_ary.

92

So I change the query to

SELECT DISTINCT initials, name FROM salespeople ORDER BY initials;

$this->headerTotals($name);
$this->viewTotals($initials);

I’m not sure what I need to change here… I thought this what was I was looking for?

93

okay, if I give you the following code:

$example  = array("stat" => "Strength", "value" => "13");

echo _______ .": ".__________;

What goes into the blanks?

94

I’m not sure.

95

https://www.php.net/manual/en/language.types.array.php#language.types.array.syntax.accessing

I suggest you have a read of the manual, as this is a concept you will need to know.

97

I’m aware of syntax. That would be saying:

$initials = $initials_ary[0]

or

$name = $initials_ary[1]

A var_dump() on the array produces this:

array(2) { ["initials"]=> string(2) "BK" ["name"]=> string(12) "Brian Konoff"  } }
98

So…

$this->headerTotals(____);

You want to send the “name” component of that array to this function. Fill in the blank.

This is an Associative Array (FETCH_ASSOC). The keys are strings, not numbers.

99 
$this->headerTotals($initials_ary[1]);
$this->viewTotals($initials_ary[0]);

Notice : Undefined offset: 1 in C:\xampp\htdocs\cascotax\api\reports\commission.php on line 114

Notice : Undefined offset: 0 in C:\xampp\htdocs\cascotax\api\reports\commission.php on line 115

100

See the last line i added to the previous post.

101

I didn’t see this until after I responded with the errors.

Why wouldn’t it work if the keys are strings?

102

Because the array doesn’t have a key 0. It has a key “initials”.
It doesn’t have a key 1, It has a key “name”.

(this is the definition of an associative array vs an indexed array.)