Report totals for non-numerical data

Report · Jul 12, 2009

Hello all,

I am trying to update a site for an auto shop. The site is in PHP, MySQL.

What they have in the database is records for each customer that comes in to their shop. So, they will have John Q. Public having multiple entries for the date he came in, and what was done to the car. They want a report that will display the totals for each item that John Public has had done.

So for example the report would be displaying the following:

breaks oil change shocks tune up total visits

John Public 2 4 0 2 8

Jack Private 1 6 4 8 19

The database has this information in it to get the above report:

Name Date Work Cust. Review

John Public 02-05-2007 breaks satisfied

John Public 03-15-2008 breaks satisfied

John Public 01-09-2008 oil change satisfied

Is there a way to do this?

Report · Jul 12, 2009

This actually is numeric data - you are talking about counts. The basic SQL for this would be

SELECT count(*), work, name from mytable

Group by work, name

The problem is the report format you want. You have pivoted basic sql results into a crosstab. Some DMBS do support crosstab queries natively, but I do not believe this is true of MySQL. So now you will either need to use PHP to format the recordset above as a crosstab, or use a more complicated database schema. The PHP solution would involve looping through the recordset, evaluate the values, and create a corresponding html table column.

Creating a crosstab using SQL technique can be found here:

http://dev.mysql.com/tech-resources/articles/wizard/index.html

Or maybe there is a DW Extension that can perform this.

Good luck.

Report · Jul 13, 2009

Thank you very much bregent. That link was very helpful. Do you happon to know another place I can go to add a little bit of complexity to it? For example if I wanted to do the same thing, but have some of the data as foreign keys (for example the work be a foreign key); can that be done? Thanks again.

Report · Jul 13, 2009

Sure, you can use more than one table. In the example provided it is:

SELECT location, SUM(IF(gender='M',1,0)) AS M,
     SUM(IF(gender='F',1,0)) AS F, COUNT(*) AS total
     FROM locations INNER JOIN employees USING (loc_code) GROUP BY location;

So you just need to substitute your table names, columns, and join conditions and include the SUM expression for each repair job you want to include in the report.

Report · Jul 13, 2009

Thanks again. I've been doing a lot of research, and asking a lot of questions. I got it almost working perfectly. I'm having problems getting the heading to read correctly. The reason is a foreign key, so with what I've got below, it only displays the number associated with the reason_id from another table. I can't find anything to help me figure out how to get the headings to display as the reason name found in the call_reason table, and not the id.

I've got so far:

<?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;

?>

Report · Jul 13, 2009

What are you table names and column names? I will try to put a query together for you.

Report · Jul 13, 2009

Thank you so much. The tables are the following:

call_log (table that contains the following:)

call_id

conv_number

date

camp_type (foreign key to the campaign table so it displays the campaign_id)

reason (foreign key to the call_reason table so it displays the reason_id)

location (foreign key to the store table so it displays the store_id)

associate

call_summary

trainig_issue (foreign key to the training_points table so it displays the training_key_id)

visit_closed

example (foreign key to the example_type table so it displays the example_id)

campaign (table that contains the following:)

campaign_id

campaign_name

call_reason (table that contains the following:)

reason_id

reason

store (table that contains the following:)

store_id

store_number

store_name

training_points (table that contains the following:)

training_key_id

key_issue

example_type (table that contains the following:)

example_id

example_name

example_image

Report · Jul 15, 2009

Well, I have done a lot of playing around, and got it to work. Although, I don't completely understand why it works. If someone would be kind enough to give me some insight, I would greatly appreciate it. I have not done JOINS in the past, so I'm trying to learn.

This is what I had originally:

<?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());

And this is what I changed it to:

<?php

//Query the unique works to generate the headers
$query = "SELECT DISTINCT call_log.reason, call_reason.reason_id, call_reason.reason
   FROM call_log LEFT JOIN call_reason
   ON call_log.reason = call_reason.reason_id
   ORDER BY call_log.reason";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
$reasons[] = $record['reason'];
}

// Query to get the top headers to have the correct column span
$query = "SELECT COUNT(*) AS reason_id FROM call_reason";

// Execute Query for the correct column span
$result = mysql_query($query);

// Get the result of query named count
$count = mysql_result($result,0);

//Start the report table including subheaders for the reason totals
$report = "<table width=\"75%\" cellpadding=\"5\" cellspacing=\"5\" border=\"1\">\n";
$report .= " <tr><th style=\"border-color:#000;\">Store</th><th colspan=\"$count\" style=\"border-color:#000;\">Reasons for the Call</th></tr>\n";
$report .= " <tr><th></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());

It's the first section of:

$query = "SELECT DISTINCT call_log.reason, call_reason.reason_id, call_reason.reason
   FROM call_log LEFT JOIN call_reason
   ON call_log.reason = call_reason.reason_id
   ORDER BY call_log.reason";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
$reasons[] = $record['reason'];
}

that I'm wodering about. Since I didn't realize that I defined the vairable for call_reason.reason how did it know to display the data from that field? I know I joined the tables, but why would it not display the call_reason.reason_id?

Thank you very much.

Report · Jul 15, 2009

>$reasons[] = $record['reason'];
>}

>

>that I'm wodering about. Since I didn't realize that I defined the vairable for call_reason.reason how did it know to display the data from that field? I know >I joined the tables, but why would it not display the call_reason.reason_id?

I do not really know PHP at all, but I'll take a stab at it. You are creating an array and assigning values from the recordset field 'reason'. That is ambiguous since there are more than one columns in the select with that name. So if it is working correctly, it might just be luck that the correct field was used. Maybe it uses the first it finds ion the RS with that name? It is probably better to fully qualify the field with the table name like $reasons[] = $record['call_reason.reason'];

Otherwise, rename your database columns so they are not the same between tables.

Again, this is complete guesswork on my part, as I have yet to learn even the basics of PHP.

Report · Jul 13, 2009

Use the mysql_num_rows() function from SELECT query. This will show the total record returned.