Hello all,

I'm working on a report and got stuck.

I'm trying to set up a report section on an update for a PHP application. It is for a auto repair shop. The database is to track customer phone calls. The call_log table has all the information in it, with some of the data as foreign keys. The foreign keys are used because they want to be able to update some of the information and have it automatically populate in all the records that use that piece of information.

They want to have a report generated where they can type in the store number and a date range and have the following information displayed:

Store Number |Oil Change |Call Reason 2 |Total Calls
4315 (Hopkins) |3 total calls |2 total calls |5 total calls
5501 (Minneap) |1 total calls |6 total calls |7 total calls

(They also want to be able to pull up this report for the district within a given date range, and have a complete total for the entire history.)

With the call reasons (oil change) as a foreign key and have call reason 2 automatically generate when they add another reason for the call. The location in the call_log table is also a foreign key to the store table which pulls the store number and store name.

They also want to have the different promotions (i.e. pay per click, toll free, etc.) listed in this table as well as secondary data being displayed (so that information listed after the total calls column) with the headers being pay per click and so on. However, if I know how to do it once, I can have the promotional data listed in another table just under this one.

They do not want to change the foreign keys because they want the flexability to add, modify, or remove call reasons, store information, or promotions without having to change thousands of records.

What I've got so far to code the page for the total history of every store (I've already got another page for the search criteria, and it works just fine except for the problem I'm having on this page) is:

<?php


//Query the unique works to generate the headers


$query = "SELECT DISTINCT reason FROM `call_log` ORDER BY reason";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
$reasons[] = $record['reason'];
}


//Start the report table including headers

$report = "<table width=\"75%\" cellpadding=\"5\" cellspacing=\"5\" border=\"1\">\n";
$report .= " <tr><th>Store</th><th>" . implode('</th><th>', $reasons) . "</th></tr>\n";


//Query the records

$query = "SELECT t2.location, t2.reason, COUNT(t1.reason) as total
FROM (
SELECT tt1.location, tt2.reason
FROM (SELECT DISTINCT location FROM `call_log`) tt1,
(SELECT DISTINCT reason FROM `call_log`) tt2) t2
LEFT JOIN `call_log` t1
ON t1.location = t2.location AND t1.reason = t2.reason
GROUP BY t2.reason, t2.location
ORDER BY t2.location, t2.reason";
$result = mysql_query($query) or die(mysql_error());

//Add the customer records
$currentName = '';

while($record = mysql_fetch_assoc($result))
{
if($currentName!=$record['location'])
{
if ($currentName!=false)
{
$report .= "</tr>\n";
}
$currentName=$record['location'];
$query = "SELECT * from store WHERE store_id = '$currentName' ";
$result1 = mysql_query($query);
while($row = mysql_fetch_array($result1, MYSQL_ASSOC))
{
$store_id = $row ['store_id'];
$store_number = $row ['store_number'];
$store_name = $row['store_name'];
}

$report .= " <tr>\n";
$report .= " <td>{$store_number} - {$store_name}</td>\n";
}

$report .= " <td>{$record['total']}</td>\n";
}
$report .= " </tr>\n";
$report .= "</table>\n";

echo $report;

?>

The problem I'm having is that the column headers for the report display the reason_id. What I want to display is the reason_name. The reason name is in a different table, and is a foreign key to the call_log table. How do I get the headers to display the reason_name and not the reason_id?

Thanks.