having problems with grouping foreach results

[orion3]

Hi All, I am having an issue trying to figure out how to turn the below array into a table by grouping it by customer name. I hope this makes sense. Unfortunately I cant do this with SQL because I have to run calculations on date ranges with the SQL results. 

 

Array (

  [2year] => Array ([internal] => 16)

  [1year] => Array ([internal] => 16)

  [less1year] => Array ([CDR] => 4 [internal] => 171 [Other] => 11 [TPI] => 1)

)

 

Into a table that looks like this:

 

CUSTOMER     <1 YR          1 YR           2 YR

Internal               171             16              16

CDR                     4

Other                  11

TPI                        1

 

 

 

So far here is the code that I have, im thinking im close, but I cant figure out how to get the same customer names to not repeat on a new <TR> but instead not repeat the customer name but show the value for the correct column. Any help is greatly appreciated.

 

                            $sql = "
				SELECT
					per_id as 'Personnel #',
					request_customer as 'Customer'
				FROM submissions, requests, personnels,
						(
						SELECT max(end) as maxdate, personnel_id
						FROM personnel_submission_link, submissions, requests
						WHERE sid = submissions_id
						AND request_id = submissions_rid
						GROUP BY pl_personnel_id
						) as end
				WHERE
					request_end = end.maxdate
					AND end.pl_personnel_id = per_id
					AND per_id = cv_personnel_submission_link.pl_personnel_id
					AND pl_sid = submissions_id
					AND request_id = submissions_rid
                                       ORDER BY request_customer
			";



                            $result_id = mysql_query
                                    ($sql)
                                    or die(mysql_error());
								
									

	  $pernumc1 = 0;
	  $pernumc2 = 0;
	  $pernumc3 = 0;
	  while($row = mysql_fetch_array($result_id))
      {
		$customers = $row[1];
		$customernames[] = $customers;
	  
		   $calctoday = date("Y-m-d");

		   $sd =  start_check_gap($row[0],45);
		   $dateDiff = strtotime($calctoday) - strtotime($sd);
		   $totaldays = floor($dateDiff/(60*60*24));
			$data = $dateDiff / 86400;
			$data = number_format($data, 0, '.', ''); // returns a number of days after calculations against other orders are found
				
				if ($data > 548)
				{
						$pernum3 = $pernumc3;
						$pernumc3++;
						$pernumname[] = "2 YR";
						$custnames3[] = $row[1];
				}
				elseif ($data > 365)
				{	
						$pernum2 = $pernumc2;
						$pernumc2++;
						$pernumname[] = "1 YR";
						$custnames2[] = $row[1];
				}
				elseif ($data < 365)
				{
						$pernum1 = $pernumc1;
						$pernumc1++;
						$pernumname[] = "< 1 YR";
						$custnames1[] = $row[1];
				}
	  }	  
				$pernum3[] = $pernum3;  
				$pernum2[] = $pernum2;
				$pernum1[] = $pernum1;
				$ppernum1 = $pernum1;

$NewArray3 = array(
"2year"=>array_count_values($custnames3),
"1year"=>array_count_values($custnames2),
"less1year"=>array_count_values($custnames1)
);
print_r($NewArray3);
				
$NewArray = array(
"More2"=>array_count_values($customernames)
);
      
      $newArray2 = array_count_values($pernumname);

                        print("<TABLE WIDTH=100% BORDER=1 BORDERCOLOR=#6c9fc6 CELLSPACING=0>");
                        print ("<TR BGCOLOR=#c2d6e7>");
                            print("<TH>CUSTOMER</TH>");
foreach ($newArray2 as $key => $value) {
                    $pernumname = $key;
                    $pernumnum = $value;
                     print("<TH>$pernumname</TH>");
                }
                              print("</TR>");

   foreach ($NewArray3 as $key => $narray) {
				  foreach ($narray as $key => $value) {
						  $customer = $key;
						   print("<TD VALIGN=TOP>$customer</TD>");
						  $numpersonnel = $value;
						   print("<TD VALIGN=TOP>$numpersonnel</TD>");
						   print("</TR>");
				  }
					
	}   
	
	
    print("</tr>

 

 

[Jessica]

Instead of organizing your table by years, then customers, do it by customers, then years.

$NewArray3 = array(
"2year"=>array_count_values($custnames3),
"1year"=>array_count_values($custnames2),
"less1year"=>array_count_values($custnames1)
);

That's the part you'll want to change first.

 

Edit: And I see no reason you can't do the entire thing in SQL.

Edited March 11, 2013 by Jessica

[akphidelt2007]

Whenever I have to do crosstabs like this I break it up in to three arrays. One for columns, one for rows, and one for data. For example...

 

 

$customerArray = Array('Internal','Other','CDR','TPI'); //you can create this array from a query also
$rangeArray = Array('lessthan1','1year','2year');
 
//then query your data and loop through it storing the data in to a data array with the customer and ranges as the keys
$data[$row[1]]['1year'] = 16; //so this would basically be $data['Internal']['1year']
 
//then loop through the rows and columns and place the data in to the cells
foreach($customerArray as $customer)
{
    foreach($rangeArray as $range)
    {
         //this is where you check to see if the data exists and you place it in, otherwise show 0 or ""
         $value = isset($data[$customer][$range]) ? $data[$customer][$range] : "";
       
         //then place this value within the <td>
     }

}

 

This is just a basic explanation with out all the typing of creating the table and everything else. Hope it gives you some ideas.

Edited March 11, 2013 by akphidelt2007

[orion3]

HI Jessica,

 

Thanks for the idea. I'll have to play with the arrays to try and change how its organized. Unfortunately my SQL knowledge is a little limited, which is why I had to create the function "start_check_gap" to figure out the what the total count in days should be across multiple date ranges. Ideally i would like for all this to be done with SQL though.  

 

 

@akphidelt: yes this definately helps. Let me try this out.

[Jessica]

Can you dump the structure and some sample data?

[orion3]

Sure, below is the function that performs that date range calculations. 

 

Basically this is taking start dates and end dates from a requests table, identifying from a personnel submission link table if  the requests = the same submission. Then it takes all the matching requests and submissions and is adding up the number of days across all requests. Also keep in mind the customer data is also stored in the requests table. I'll try and illustrate this below, the tables have alot of data and other fields, I can provide a dump if needed though.

 

   requests                          submissions                                  personnel_submission_link

request_id                        submissions_rid                             personnel_id

request_start                    submissions_id                               submissions_id

request_end

request_customer

 

So an example of the result for 1 set would be

 

 

submissions id     personnel id    request_start     request_end   request_id

139698               700010            2011-10-10       2012-05-28     20005

132561               700010            2011-06-28       2011-10-09     16457

124788               700010            2010-12-27       2011-04-29     13405

106768               700010            2009-01-05       2010-12-26      4230

 10766                700010           2005-05-02        2009-01-04      1134

 

 

  function start_check_gap($pid, $tgap)
  {
    if ($pid != False)
    {
      $sql =
      "
        SELECT submissions_id, pl_personnel_id, request_start, request_end, request_id
        FROM personnel_submission_link, submissions, requests
        WHERE
          pl_sid = submissions_id AND
          request_id = submissions_rid AND
          pl_personnel_id = '$pid'
        ORDER BY request_end DESC
      ";
      $result_id = mysql_query($sql) or die(mysql_error());

      $anymatches = mysql_num_rows($result_id);
      if ($anymatches == 0)
      {
        RETURN "n/a";
      }
      else
      { 
        $gaps = "";
        $start = FALSE;
        while ($row = mysql_fetch_array($result_id))
        {
          if ($start != FALSE)
          {
            $gap = dateDiff($start, $row['request_end']);
            $gaps = $gaps . "**" . $start . "," . $row['request_end'] . "," . $gap;
            if ($gap < -$tgap)
            {
              break;
            }
          }
          $start = $row['request_start'];
        }
        RETURN $start;
      }
    }
  }

[Jessica]

That's not at all what I was asking for, but it does expose that you're running queries in loops. BAD. Stop that.

[Barand]

When you have dates avilable in the data why are you calculating the 1 year, 2 year from the personnel ID?

[orion3]

This is because the personnel ID is supposed to be a constant regardless of how many different requests or submissions. So the personnel id is what ties the personnel entry to lets say multiple submissions. In the example above you can have 1 personnel id that is connected to several submission ids, and the submission ids are connected to the request ids, which the request ids is what has the dates and customers in it.

 

Ideally they way the entire thing should work is grabs a personnel id, then looks up all the different submission and request ids tied to it, calculates the number of days and returns # of days and customer, then moves on to the next personnel id. Unfortunately as Jessica stated ive reverted to php functions and query loops to make up for not knowing how to do this in sql

[Barand]

So as well as running the other queries inside loops you also have a query inside the start_check_gap() function to find a date based on the pers_id?

Edited March 11, 2013 by Barand