Report via PHP/MYSQL

[Ryflex]

Hi all,

 

For a logging tool I need to make some automated reports.

On 1 page it needs to give for 4 clients the amount of calls per month for the last 12 months.

Also it needs to give the amount of calls for the employees per month for the last 12 months.

 

I was hoping you could help me with a better solution than seperate queries.

 

Gr Ryflex

[OOP]

Hi there,

I don't know if I got your requirements right or not but as far as I undestood, you just need to get a list of all calls for the last 12 months and then manipulate, organize and display the data however you want in your script.

Regards

[Ryflex]

That sounds about right.

 

Maybe it's easier if i kinda show what I have/want.

 

TABLE:

---------------------------------------------------

| id | name | client | day | month | year |

---------------------------------------------------

 

Output

per client

-----------------------------------------------------------

| months | client 1 | client 2 | client 3 | client 4 |

-----------------------------------------------------------

| jan      |        20 |        5  |      100 |        55 |

-----------------------------------------------------------

etc

 

per employee

-----------------------------------------------------------

| months | emplo1 | emplo2 | emplo3 | emplo4 |

-----------------------------------------------------------

| jan      |        10 |        85  |        75 |        35 |

-----------------------------------------------------------

etc

 

 

 

 

 

 

[OOP]

Hi,

Please execuse me if this is a stupid question but where is the employee column in your table? is it the same as name column? Is is possible to give more details about the table that you want to extract the information from? I mean how do you differentiate between a call to a client and a call to an employee?

 

regards

[Ryflex]

My mistake the name column is the employee name.

it is only one table i want to pull the info from.

When a call is logged the name of the logger and the client is put in the row of the call.

[OOP]

Okay, maybe this will work

assuming that the calls list is in the below formate

$calls_list = array(
               array('name'=> 'john', 'client'=>'c1','month'=> 'Jan', 'year'=>'2011'),
      array('name'=> 'john', 'client'=>'c1','month'=> 'Jan', 'year'=>'2011'),
      array('name'=> 'john', 'client'=>'c1','month'=> 'Jan', 'year'=>'2011'),
      array('name'=> 'john', 'client'=>'c2','month'=> 'Mar', 'year'=>'2011'),
      array('name'=> 'tom', 'client'=>'c1','month'=> 'Mar', 'year'=>'2011'),
      array('name'=> 'tom', 'client'=>'c4','month'=> 'Sep', 'year'=>'2011'),
         array('name'=> 'tom', 'client'=>'c1','month'=> 'Sep', 'year'=>'2011')
             );

Then maybe we can do somthing like the below to have two different lists

$employees_list = array();
$clients_list = array();
foreach ($calls_list as $call){
if(isset($employees_list[$call['name']][$call['year']][$call['month']])){
  $employees_list[$call['name']][$call['year']][$call['month']]++;
}else{
  $employees_list[$call['name']][$call['year']][$call['month']] = 1;
} 

if(isset($clients_list[$call['client']][$call['year']][$call['month']])){
  $clients_list[$call['client']][$call['year']][$call['month']]++;
}else{
  $clients_list[$call['client']][$call['year']][$call['month']] = 1;
}
}
var_dump($employees_list);
echo '<br/>';
var_dump($clients_list);

 

regards

 

[Ryflex]

Hi OOP

 

This seems to work with the data you provided but here's comes the stupid question:

How do I need to query the DB to get the $calls_list array that way.

 

Ryflex

[Ryflex]

Nevermind I found it

$calls_list = array();
$i = 0;
while($row = mysql_fetch_assoc($qry)) {
$calls_list[$i] = array('name'=>$row['user'],'client'=>$row['klant'],'month'=>$row['maand'],'year'=>$row['jaar']);
$i++;
}

[OOP]

I am glade you figured it out  hope that solved your problem

 

regards