Help With Reports

[tmurphy]

I need report that
list each month in the year across the top
then list distinct each rep across the left hand side
then tell how many contacts they recieved for each month.

[tmurphy]

thisd is what i have so far but when i try to view it i get

Warning: Invalid argument supplied for foreach() in /home/mdptoday/www/test2.php on line 33

<?php
require_once ("./application/backend.php");

// connect to db

$connection = @mysql_connect("$authlib->server", "$authlib->db_user", "$authlib->db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($authlib->database, $connection) or die("Couldn't select database.");

$query = "select reps.r_code, UNIX_TIMESTAMP(contacts.t_stamp) as getmonth, contacts.id from
reps,contacts where contacts.rep=reps.r_code order by t_stamp, r_code";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
$month = strftime("%m", $row['getmonth']);
$rep = $row['r_code'];
if(isset($report[$month]["$rep"])) {
$report[$month]["$rep"]++;
} else {
$report[$month]["$rep"] = 1;
}

}

// start table
echo "<TABLE ALIGN=\"CENTER\" CELLPADDING=\"3\" WIDTH=\"100 %\" BORDER=\"0\">";
echo "<TR>";

echo "<td></td>";

for ($i = 1; $i <= 12; $i++){
echo "<td>" . date("M", strtotime("2002-$i-22")) . "</td>\n";
}

foreach ($rep as $val) {
echo "<td>$rep</td>";
for ($i = 1; $i < 12;$i++){
if (isset($report[$i]["$rep"])){
echo "<td>$report[$i]['$rep']</td>";
}
else{
echo "<td>0</td>";
}

echo "</tr>";
}
}
echo "</table>";

?>

[Pl_yer]

Check out SQL!!

select distinct count(id) from ??? where ......
id= your primary_key or a part of it, just pick one
I shall check it out with the months and years because I don't know it by heart.

Greetz

<$Player>

[tmurphy]

this is what i have now and i'm getting a

Warning: Invalid argument supplied for foreach() in /home/mdptoday/www/test2.php on line 33

<?php
require_once ("./application/backend.php");

// connect to db

$connection = @mysql_connect("$authlib->server", "$authlib->db_user", "$authlib->db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($authlib->database, $connection) or die("Couldn't select database.");

$query = "select reps.r_code, UNIX_TIMESTAMP(contacts.t_stamp) as getmonth, count(contacts.id) as id from
reps,contacts where contacts.rep=reps.r_code order by t_stamp, r_code";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
$month = strftime("%m", $row['getmonth']);
$rep = $row['r_code'];
if(isset($report[$month]["$rep"])) {
$report[$month]["$rep"]++;
} else {
$report[$month]["$rep"] = 1;
}

}

// start table
echo "<TABLE ALIGN=\"CENTER\" CELLPADDING=\"3\" WIDTH=\"100 %\" BORDER=\"0\">";
echo "<TR>";

echo "<td></td>";

for ($i = 1; $i <= 12; $i++){
echo "<td>" . date("M", strtotime("2002-$i-22")) . "</td>\n";
}

foreach($rep as $val);
{
echo "<td>$rep</td>";
for ($i = 1; $i < 12;$i++){
if (isset($report[$i]["$rep"])){
echo "<td>$report[$i]['$rep']</td>";
}
else{
echo "<td>0</td>";
}

echo "</tr>";
}
}
echo "</table>";
?>

[br__ken]

doesnt look like $rep is an array, print_r it and see whats in it

[tmurphy]

something very wierd

Please View at

http://www.mdptoday.com/test2.php