Lost in the 3rd dimension......-al array

[ccchan]

I'm working on this code that will query the MySQL database and pull a series of information to generate a report. The data is stored in a per incident method where when anything happens an item name and a sales representative that worked on it is written to the table. The report that I need to make will need to show stats per sales rep for each item. So I'm trying to attain unique values for the sales reps and the item names and then evaluate each data entry for each combination of sales rep and item name. Here is what I have:

 

//connection data omitted
$getsalesrep = "SELECT * FROM efi_customers_test WHERE cms_type = 'Booking' AND Date BETWEEN '2010-03-24' AND '2010-04-24' GROUP BY salesperson";
$getfundraiser = "SELECT * FROM efi_customers_test WHERE cms_type = 'Booking' AND Date BETWEEN '2010-03-24' AND '2010-04-24' GROUP BY FundraiserName";
$getdata = "SELECT * FROM efi_customers_test WHERE cms_type ='Booking' AND Date BETWEEN '2010-03-24' AND '2010-04-24'";

$resultsalesrep = mysql_query($getsalesrep) or die(mysql_error());
$resultfundraiser = mysql_query($getfundraiser) or die(mysql_error());
$result = mysql_query($getdata) or die(mysql_error());


echo '
<table>
<tr>
	<td>
		 
	</td>';

$salesrepinc = 0;
while ($rowsalesrep = mysql_fetch_array($resultsalesrep)){
echo '<td colspan="3">'. $rowsalesrep['salesperson']. '</td>';
$salesrep[$salesrepinc] = $rowsalesrep['salesperson'];
$salesrepinc++;
}

echo '
	<td colspan="3">
		TOTAL
	</td>
</tr>';

$j = 0;
$bookings = 0;
$booked = 1;
$percentage = 2;
$info[0][0][0] = 0;
$salesreptotals[0][0][0] = 0;
$fundraisertotals[0][0][0] = 0;
$totaltotals[0][0][0] = 0;
while ($rowfundraiser = mysql_fetch_array($resultfundraiser)){
echo "<tr><td>". $rowfundraiser['FundraiserName']. "</td>";
$j++;
for ($i = 0; $i < $salesrepinc; $i++){
	while ($row = mysql_fetch_array($result)){
		if ($row['salesperson'] == $salesrep[$i] && $row['FundraiserName'] == $rowfundraiser['FundraiserName']){
			$info[$i][$j][$bookings] = $info[$i][$j][$bookings] + 1;
			$salesreptotals[$i][$bookings] = $salesreptotals[$i][$bookings] + 1;
			$fundraisertotals[$j][$bookings] = $fundraisertotals[$j][$bookings] + 1;
			$totaltotals[$bookings] = $totaltotals[$bookings] + 1;
			if ($row['booked'] == 'checked'){
				$info[$i][$j][$booked] = $info[$i][$j][$booked] + 1;
				$salesreptotals[$i][$booked] = $salesreptotals[$i][$booked]+ 1;
				$fundraisertotals[$j][$booked] = $fundraisertotals[$j][$booked]+ 1;
				$totaltotals[$booked] = $totaltotals[$booked] + 1;
			}
		}
	}
	$info[$i][$j][$percentage] = $info[$i][$j][$booked] / $info[$i][$j][$bookings];
	$salesreptotals[$i][$percentage] = $salesreptotals[$i][$booked] / $salesreptotals[$i][$bookings];
	echo '<td>'. $info[$i][$j][$bookings]. '</td><td>'. $info[$i][$j][$booked]. '</td><td>'. $info[$i][$j][$percentage]. '%</td>';
}
$fundraisertotals[$j][$percentage] = $fundraisertotals[$j][$booked] / $fundraisertotals[$j][$bookings];
echo '<td>'. $fundraisertotals[$j][$bookings]. '</td><td>'. $fundraisertotals[$j][$booked]. '</td><td>'. $fundraisertotals[$j][$percentage]. '%</td></tr>';
}

echo "
<tr>
	<td>
		TOTALS
	</td>";
for($n = 0; $n < $salesrepinc; $n++){
echo '<td>'. $salesreptotals[$n][$bookings]. '</td><td>'. $salesreptotals[$n][$booked]. '</td><td>'. $salesreptotals[$n][$percentage]. '</td>';
}
$totaltotals[$percentage] = $totaltotals[$booked] / $totaltotals[$bookings];
echo '
	<td>'. $totaltotals[$bookings]. '</td><td>'. $totaltotals[$booked]. '</td><td>'. $totaltotals[$percentage]. '%
</tr>
</table>';


?>

 

The following code returns this error

 

PHP Notice:  Undefined offset:  1 in E:\Websites\easy-fundraising-ideas.com\wwwroot\cms\report1_process_test.php on line 58

PHP Fatal error:  Unsupported operand types in E:\Websites\easy-fundraising-ideas.com\wwwroot\cms\report1_process_test.php on line 59

 

 

where

				$info[$i][$j][$bookings] = $info[$i][$j][$bookings] + 1;
			$salesreptotals[$i][$bookings] = $salesreptotals[$i][$bookings] + 1;

are lines 58 and 59

 

Initially I had a lot more "Undefined offset" notices so that's when I started using "$variable[][][] = $variable[][][] + 1;" instead of just "$variable[][][]++;". Does the ++ operand not work when you're using (multidimensional)arrays?

 

Also, initializing the arrays with

$info[0][0][0] = 0;
$salesreptotals[0][0][0] = 0;
$fundraisertotals[0][0][0] = 0;
$totaltotals[0][0][0] = 0;

  helped to reduce some of the errors also. I tried it and it helped, but I don't really understand why.

 

My eternal gratitude is offered up to the one that helps me fix and understand this mess. Thank you!!!

 

-Chelsea

[thomashw]

I don't think it works because you can't add '1' to something that doesn't previously exist.

 

This helped you get rid of some errors:

 

$info[0][0][0] = 0;
$salesreptotals[0][0][0] = 0;
$fundraisertotals[0][0][0] = 0;
$totaltotals[0][0][0] = 0;

 

because at the beginning of your loops $i = 0, $j = 0, and $bookings = 0.

 

So the first time through the loops, the statements looked like this:

 

$info[0][0][0] = 0 + 1;
$salesreptotals[0][0] = 0 + 1;

 

But what about the next time it goes through? Your $i and your $j are incrementing. The next time it goes through this will happen:

 

$info[1][1][0] = ?? + 1;
$salesreptotals[1][0] = ?? + 1;

 

Do you get it? If there's no previous value there, you can't add '1' to it, because that doesn't make sense. It doesn't equal anything.

[ccchan]

Oh, well that makes some sense. Here's how I changed it:

$getsalesrep = "SELECT * FROM efi_customers_test WHERE cms_type = 'Booking' AND Date BETWEEN '2010-03-24' AND '2010-04-24' GROUP BY salesperson";
$getfundraiser = "SELECT * FROM efi_customers_test WHERE cms_type = 'Booking' AND Date BETWEEN '2010-03-24' AND '2010-04-24' GROUP BY FundraiserName";
$getdata = "SELECT * FROM efi_customers_test WHERE cms_type ='Booking' AND Date BETWEEN '2010-03-24' AND '2010-04-24'";

$resultsalesrep = mysql_query($getsalesrep) or die(mysql_error());
$resultfundraiser = mysql_query($getfundraiser) or die(mysql_error());
$result = mysql_query($getdata) or die(mysql_error());


echo '
<table>
<tr>
	<td>
		 
	</td>';

$salesrepinc = 0;
while ($rowsalesrep = mysql_fetch_array($resultsalesrep)){
echo '<td colspan="3">'. $rowsalesrep['salesperson']. '</td>';
$salesrep[$salesrepinc] = $rowsalesrep['salesperson'];
$salesrepinc++;
}

echo '
	<td colspan="3">
		TOTAL
	</td>
</tr>';

$j = 0;
$bookings = 0;
$booked = 1;
$percentage = 2;
while ($rowfundraiser = mysql_fetch_array($resultfundraiser)){
echo "<tr><td>". $rowfundraiser['FundraiserName']. "</td>";
$j++;
for ($i = 0; $i < $salesrepinc; $i++){
	$info[$i][$j][0] = 0;
	$salesreptotals[$i][0] = 0;
	$fundraisertotals[$j][0] = 0;
	$totaltotals[0] = 0;
	$info[$i][$j][1] = 0;
	$salesreptotals[$i][1] = 0;
	$fundraisertotals[$j][1] = 0;
	$totaltotals[1] = 0;
	$info[$i][$j][2] = 0;
	$salesreptotals[$i][2] = 0;
	$fundraisertotals[$j][2] = 0;
	$totaltotals[2] = 0;
	while ($row = mysql_fetch_array($result)){
		if ($row['salesperson'] == $salesrep[$i] && $row['FundraiserName'] == $rowfundraiser['FundraiserName']){
			$info[$i][$j][$bookings] = $info[$i][$j][$bookings] + 1;
			$salesreptotals[$i][$bookings] = $salesreptotals[$i][$bookings] + 1;
			$fundraisertotals[$j][$bookings] = $fundraisertotals[$j][$bookings] + 1;
			$totaltotals[$bookings] = $totaltotals[$bookings] + 1;
			if ($row['booked'] == 'checked'){
				$info[$i][$j][$booked] = $info[$i][$j][$booked] + 1;
				$salesreptotals[$i][$booked] = $salesreptotals[$i][$booked]+ 1;
				$fundraisertotals[$j][$booked] = $fundraisertotals[$j][$booked]+ 1;
				$totaltotals[$booked] = $totaltotals[$booked] + 1;
			}
		}
	}
	if ($info[$i][$j][$booked] != 0 && $info[$i][$j][$bookings] != 0){
		$info[$i][$j][$percentage] = $info[$i][$j][$booked] / $info[$i][$j][$bookings];
	}
	if ($salesreptotals[$i][$booked] != 0 && $salesreptotals[$i][$bookings] != 0){
		$salesreptotals[$i][$percentage] = $salesreptotals[$i][$booked] / $salesreptotals[$i][$bookings];
	}
	echo '<td>'. $info[$i][$j][$bookings]. '</td><td>'. $info[$i][$j][$booked]. '</td><td>'. $info[$i][$j][$percentage]. '%</td>';
}
if ($fundraisertotals[$j][$booked] != 0 && $fundraisertotals[$j][$bookings] != 0){
	$fundraisertotals[$j][$percentage] = $fundraisertotals[$j][$booked] / $fundraisertotals[$j][$bookings];
}
echo '<td>'. $fundraisertotals[$j][$bookings]. '</td><td>'. $fundraisertotals[$j][$booked]. '</td><td>'. $fundraisertotals[$j][$percentage]. '%</td></tr>';
}

echo "
<tr>
	<td>
		TOTALS
	</td>";
for($n = 0; $n < $salesrepinc; $n++){
echo '<td>'. $salesreptotals[$n][$bookings]. '</td><td>'. $salesreptotals[$n][$booked]. '</td><td>'. $salesreptotals[$n][$percentage]. '</td>';
}
if ($totaltotals[$booked] != 0 && $totaltotals[$bookings] != 0){
$totaltotals[$percentage] = $totaltotals[$booked] / $totaltotals[$bookings];
}
echo '
	<td>'. $totaltotals[$bookings]. '</td><td>'. $totaltotals[$booked]. '</td><td>'. $totaltotals[$percentage]. '%
</tr>
</table>';
?>

 

Basically I initialized the arrays after the $i and $j incrementation but before the data counting. After that was fixed I had to add some if statements to control division by zero errors. No more syntax errors but it's not really doing what it's supposed to. 

 

Here's it running: http://www.easy-fundraising-ideas.com/cms/report1_process_test.php and most of the values are returned 0... according to the data, this is not the expected result. At brief glance, does anyone see something that doesn't seem right in my logic?

 

Thank you so much for the info on adding to nothing. Seems obvious, not really sure why I didn't realize that. You really helped me out thomashw!!

[thomashw]

One tip - use the "isset" function. You can use it to check if a variable is set, and if it isn't, then you can set it to an initial value (in your case 0). This would let you get rid of all those declarations, as well as initialize any you may have forgotten.

 

Can I ask why you used variables for almost all your array keys? It doesn't really make sense, and is fairly confusing to read through.

[jcbones]

ccchan,

 

I was somewhat bored today, and decided to try and sort through this code.  It seemed somewhat confusing, so I wrote a version for you to study.  This attempts to do what you are trying to do, with a lot less code.  It probably isn't what you are looking for, but it isn't suppose to be.

 

Merely an example of processing an array.

<?php
include('config.php');

function percentage($low, $high) {
return number_format(($low / $high)*100,1); //the last number sets how many spaces after the decimal to return.
}

$getsalesrep = "SELECT * FROM efi_customers_test WHERE cms_type = 'Booking' AND Date BETWEEN '2009-03-24' AND '2010-04-24'";

//echo $getsalesrep;
$resultsalesrep = mysql_query($getsalesrep) or die(mysql_error());

while ($row = mysql_fetch_array($resultsalesrep)){
$people[] = $row['salesperson'];
@$sales[$row['FundraiserName']][$row['salesperson']][$row['booked']] += 1;
}
$people = array_values(array_unique($people));
$count = count($people);
//echo '<pre>'; print_r($sales); echo '</pre>';
echo '<table><tr><td style="font-weight: bold;">Fundraisers</td>' . "\n";

if(is_array($people)) {
foreach($people as $salesperson) {
	echo '<td colspan="3">'. $salesperson . '</td>' . "\n";		
}
echo '<td colspan="3">TOTAL</td></tr>';
}

if(is_array($sales)) {
foreach($sales as $fundraiser => $v2) {
	echo '<tr><td>' . $fundraiser . '</td>';
		$totalBooking = 0;
		$totalBooked = 0;
		$totalPercentage = 0;

	for($i = 0; $i < $count; $i++) { 
		//------------------------------------
		//might need to edit the line below, as I know that $row['booked'] could hold 'checked', I don't know if the other value is null, or 'not checked'.
		$bookings = (isset($sales[$fundraiser][$people[$i]]['']) && isset($sales[$fundraiser][$people[$i]]['checked'])) ? $sales[$fundraiser][$people[$i]][''] + $sales[$fundraiser][$people[$i]]['checked'] : 0; 
		//------------------------------------
		$booked = (isset($sales[$fundraiser][$people[$i]]['checked'])) ? $sales[$fundraiser][$people[$i]]['checked'] : 0;
		$percentage = percentage($booked,$bookings);
		echo '<td>'. $bookings . '</td><td>'. $booked. '</td><td>'. $percentage . '%</td>';

		$totalBooking += $bookings;
		$totalBooked += $booked;

		@$vert[$i]['booking'] += $bookings;
		@$vert[$i]['booked'] += $booked;			

	}
	echo '<td>'. $totalBooking. '</td><td>'. $totalBooked. '</td><td>'. percentage($totalBooked,$totalBooking). '%</td></tr>';
	@$totalTotalBooking += $totalBooking;
	@$totalTotalBooked += $totalBooked;			
}
}
echo '<tr><td>TOTALS</td>';
if(is_array($vert)) {

for($i = 0; $i < $count; $i++){
	echo '<td>'. $vert[$i]['booking']. '</td><td>'. $vert[$i]['booked'] . '</td><td>'. percentage($vert[$i]['booked'],$vert[$i]['booking']) . '%</td>';
}

}

echo '<td>'. $totalTotalBooking. '</td><td>'. $totalTotalBooked . '</td><td>'. percentage($totalTotalBooked,$totalTotalBooking). '%
</tr>
</table>';
?>

[ccchan]

Can I ask why you used variables for almost all your array keys? It doesn't really make sense, and is fairly confusing to read through.

 

Do you mean use of variables $booked, $bookings, and $percentage? The way that my chart will ideally work will result in a table that looks like this:

 

salesrep1

salesrep2

salesrep3

total

item name1

salesrep1 stats for bookings

stats for booked

% of booked to bookings

salesrep2 stats for bookings

stats for booked

% of booked to bookings

salesrep3 stats for bookings

stats for booked

% of booked to bookings

totals for that item

Totals

totals for salesrep1

totals for salesrep2

totals for salesrep3

grand totals

 

so for what seemed like it'd make it easier to read in the code, the third key will indicate which kind of info it is and rather than using 0, 1, 2 I just set those to variables and used names instead. 

 

I'll try the isset() function for the initialization. Thanks for the tip.

 

 

@jcbones

Wow! I'll totally look through that. Just looking at it briefly it looks more effective than mine. If it doesn't do what I'm looking for I might be better off altering it to suit my needs. Thank you for so much help!

 

 

 

you guys rock! 

[thomashw]

Post back if you're having trouble after going through the above code. I'm willing to help.

[jcbones]

Well, I should have done this the first time:

 

Full commented script:

<?php
include('config.php');

//This function returns a percentage, nothing more.
function percentage($low, $high) {
return number_format(($low / $high)*100,1); //the last number sets how many spaces after the decimal to return.
}

//SQL query, only need one, we are processing the data with an array.
$getsalesrep = "SELECT * FROM efi_customers_test WHERE cms_type = 'Booking' AND Date BETWEEN '2009-03-24' AND '2010-04-24'";

//DEBUG, un-comment the next line;
//echo $getsalesrep;

//Run the query, or die trying.
$resultsalesrep = mysql_query($getsalesrep) or die(mysql_error());

//Loop through the data.
while ($row = mysql_fetch_array($resultsalesrep)){
//Set an array for the sales people.  I tried doing it with only one array
//But, alas, you wanted them all on the top row.  No problems.
$people[] = $row['salesperson'];

//Set another array holding the rest of the data, and tying it in with the 
//sales person. The @ just suppresses the notice the variable gives on it's 
//creation, since it wasn't set before.
@$sales[$row['FundraiserName']][$row['salesperson']][$row['booked']] += 1;
}

//Now the $people array will have multiple people in it, so we need to get
//only one instance of a persons name.  array_unique() does that, but then
//we need to run array_values() to get the key's to line up for a for loop.
$people = array_values(array_unique($people));

//Store the number of salespeople in $count.
$count = count($people);

//DEBUG un-comment next line.
//echo '<pre>'; print_r($sales); echo '</pre>';

//Start our table.
echo '<table><tr><td style="font-weight: bold;">Fundraisers</td>' . "\n";

//is_array just makes sure $people is an array.
if(is_array($people)) {
//loop through the array, and echo the salespeople.
foreach($people as $salesperson) {
	echo '<td colspan="3">'. $salesperson . '</td>' . "\n";		
}
echo '<td colspan="3">TOTAL</td></tr>';
}

//just checking the sales array.
if(is_array($sales)) {

//Now things get interesting.  Looping through the sales array.
foreach($sales as $fundraiser => $v2) {

	//echo our the fundraiser.
	echo '<tr><td>' . $fundraiser . '</td>';

		//These next 3 variables will set/reset to 0 on each loop.
		$totalBooking = 0;
		$totalBooked = 0;
		$totalPercentage = 0;

	//Running a for loop, This is because we need to cycle through the 
	//people array, and pull data so it is lined up with the sales person
	//at the top of the current column.
	for($i = 0; $i < $count; $i++) { 

		//IN DEPTH: $people[$i] will pass the name of the salesperson at the top of the current column
		//so that we can make sure the columns will line up right.

		//------------------------------------
		//might need to edit the line below, as I know that $row['booked'] could hold 'checked', I don't know if the other value is null, or 'not checked'.
		$bookings = (isset($sales[$fundraiser][$people[$i]]['']) && isset($sales[$fundraiser][$people[$i]]['checked'])) ? $sales[$fundraiser][$people[$i]][''] + $sales[$fundraiser][$people[$i]]['checked'] : 0; 
		//------------------------------------
		$booked = (isset($sales[$fundraiser][$people[$i]]['checked'])) ? $sales[$fundraiser][$people[$i]]['checked'] : 0;

		//Get the percentage from the function at the top of the page.
		$percentage = percentage($booked,$bookings);

		//echo the findings out.
		echo '<td>'. $bookings . '</td><td>'. $booked. '</td><td>'. $percentage . '%</td>';

		//add the bookings/booked together so we can write them to the end of this row.
		$totalBooking += $bookings;
		$totalBooked += $booked;

		//now add the bookings/booked to an array, using the column number to keep `em straight.
		@$vert[$i]['booking'] += $bookings;
		@$vert[$i]['booked'] += $booked;			

	}

	//echo out the total column for this row.  Again using percentage function from the top of the page.
	echo '<td>'. $totalBooking. '</td><td>'. $totalBooked. '</td><td>'. percentage($totalBooked,$totalBooking). '%</td></tr>';

	//counting the totals for the final row's last column.  $totalBooking, and $totalBooked will be reset on the next loop.
	@$totalTotalBooking += $totalBooking;
	@$totalTotalBooked += $totalBooked;			
}
}

//echo'ing the last row.
echo '<tr><td>TOTALS</td>';
if(is_array($vert)) {
//Run a for loop to line up the columns correctly.  Again using percentage function at top of page.
for($i = 0; $i < $count; $i++){
	echo '<td>'. $vert[$i]['booking']. '</td><td>'. $vert[$i]['booked'] . '</td><td>'. percentage($vert[$i]['booked'],$vert[$i]['booking']) . '%</td>';
}

}

//echo out the last column of the last row, we kept count of it at the end of each loop above.
echo '<td>'. $totalTotalBooking. '</td><td>'. $totalTotalBooked . '</td><td>'. percentage($totalTotalBooked,$totalTotalBooking). '%
</tr>
</table>';
?>

[ccchan]

Your code worked like a charm, actually! I learned quite a few things while going through it. One thing that I'd like to understand a little better is what exactly this variable handling does:

$bookings = (isset($sales[$fundraiser][$people[$i]]['']) && isset($sales[$fundraiser][$people[$i]]['checked'])) ? $sales[$fundraiser][$people[$i]][''] + $sales[$fundraiser][$people[$i]]['checked'] : 0;	

$booked = (isset($sales[$fundraiser][$people[$i]]['checked'])) ? $sales[$fundraiser][$people[$i]]['checked'] : 0;

 

I don't really have any familiarity with "&&", "?", and ": 0" when it comes to assigning value to a variable. I'm intrigued.

 

If someone could explain this to me, I'd really appreciate it. You guys have already bailed me out, big time! Thanks!!