Report totals for non-numerical data

[lightfighter]

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

 

etc.

 

 

Is there a way to do this?

 

[ignace]

SELECT name, work, count(*) as work_times FROM table GROUP BY work

[lightfighter]

Thanks for your reply.  I'm sorry, but I don't fully understand your answer.  Can you explain it a little bit more please?  Thank you very much.

[ignace]

Thanks for your reply.  I'm sorry, but I don't fully understand your answer.  Can you explain it a little bit more please?  Thank you very much.

 

It's an sql query to perform on your db to get the results.

[Psycho]

The process will be more complex than a single query because not every customer will have used every service! The process will require two queries (one to get the unique work types in addition to the one ignace supplied) and then additional logic to be used when displaying the results to handle customer/work combinatinos that don't exist.

 

Besides the query is wrong.

 

I'll post some code in a few minutes

[.josh]

this query:

 

select count(work) as total, work, name from tablename group by work order by name, work

 

should produce something like this:

 

[pre]

total  work        name

2      breaks      John Public

4      oil change  John Public

2      tune up    John Public

1      breaks      John Private

6      oil change  John Private

4      shocks      John Private

8      tune up    John Private

[/pre]

 

I would start out by assigning the info to a multi-dim array where first level is customer's name, 2nd level is work, like so:

 

while ($info = mysql_fetch_assoc($result)) {
  $customers[$info['name']][$info['work']] = $info['total'];
}  

 

As Mj mentioned, since not all customers will have all services, you can't do a simple nested foreach.  But what you can do is a foreach for customers and then name each service explicitly.  Or you can redo the while loop to auto fill in the gaps and then do a nested foreach. Throw in an array_sum() for the grand total.

[Psycho]

With all due respect Crayon, that query won't work. By doing a GROUP BY only on "work", you will group records from different customers. The GROUP BY needs to be "GROUP BY name, work"

[ignace]

With all due respect Crayon, that query won't work. By doing a GROUP BY only on "work", you will group records from different customers. The GROUP BY needs to be "GROUP BY name, work"

 

I know I made the mistake first, but I believe for the query to work you even need to list all select fields in the group by clause, thus:

select count(work) as total, work, name from tablename group by total, work, name

 

Edit: Nope, checked it.

 

select count(work) as total, work, name from tablename group by work order by name, work

 

And is the 'work' necessary for the order by? As group by already put it all together so work won't show up double for name thus leaving the work order by unneccessary?

[lightfighter]

Thanks everyone.  I really do appreciate the information and help.  Would anyone be kind enough to let me know of a tutorial or book I can get to teach myself how to do this? 

 

I've found a lot of stuff, but they all leave off on the beginning levels of PHP with everything that I already know how to do.  Is there a good resource that I can learn about this more advanced scripting?

 

Thanks again, I really appreciate everyone's help.

[.josh]

With all due respect Crayon, that query won't work. By doing a GROUP BY only on "work", you will group records from different customers. The GROUP BY needs to be "GROUP BY name, work"

 

Yeah my bad, I typoed that before when I was testing and fixed it but forgot to update my c/p of it.

 

And is the 'work' necessary for the order by? As group by already put it all together so work won't show up double for name thus leaving the work order by unneccessary?

 

putting work in the order by will order the work names, so they appear in the same order.

 

without the work in the order by, the results might appear like this:

 

[pre]

total  work        name

4      oil change  John Public

2      breaks      John Public

2      tune up    John Public

8      tune up    John Private

1      breaks      John Private

4      shocks      John Private

6      oil change  John Private

[/pre]

 

instead of this:

 

[pre]

total  work        name

2      breaks      John Public

4      oil change  John Public

2      tune up    John Public

1      breaks      John Private

6      oil change  John Private

4      shocks      John Private

8      tune up    John Private

[/pre]

[Psycho]

I know this can be done with a single query such that a "null" record will be created for each customer/work item that doesn't currently exist. This would greatly ease the process of displayng the records without a lot of complicated logic. I'm having some mixed results in joining the table to a sub query of just the distinct work types, but I'm missing something.

[.josh]

OP: is there some set list of "work" categories somewhere?  Are those 4 the only ones? 

[MatthewJ]

Small point.. but cars don't have "breaks"... they have "brakes"

[Psycho]

OK, this works. It includes a query to get the totals for all name/work combinations - even those that don't exist, which makes displaying the results very simple. I have to believe there is a simpler way to do the query, but it works.

 

<?php

$db = mysql_connect('localhost', 'root', '');
mysql_select_db('test');

//Query the unique works to generate the headers
$query = "SELECT DISTINCT work FROM `table` ORDER BY work";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
    $works[] = $record['work'];
}

//Start the report table including headers
$report = "<table border=\"1\">\n";
$report .= "  <tr><th>Name</th><th>" . implode('</th><th>', $works) . "</th></tr>\n";

//Query the records
$query = "SELECT t2.name, t2.work, COUNT(t1.work) as total
          FROM (
              SELECT tt1.name, tt2.work
              FROM (SELECT DISTINCT name FROM `table`) tt1,
                   (SELECT DISTINCT work FROM `table`) tt2) t2
          LEFT JOIN `table` t1
          ON t1.name = t2.name AND t1.work = t2.work
          GROUP BY t2.work, t2.name
          ORDER BY t2.name, t2.work";
$result = mysql_query($query) or die(mysql_error());

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

while($record = mysql_fetch_assoc($result))
{
    if($currentName!=$record['name'])
    {
        if ($currentName!=false)
        {
            $report .= "</tr>\n";
        }
        $currentName=$record['name'];
        $report .= "  <tr>\n";
        $report .= "    <td>{$currentName}</td>\n";
    }

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

?>

<html>
<body>
<?php echo $report; ?>
</body>
</html>

 

Here was my test data

name  | work
===================
bob   | oil change 
bob   | brakes 
jane  | brakes 
jim   | oil change 
robert| alignment 
bob   | oil change 
jane  | brakes 

 

And here was the result

Name  | alignment | brakes | oil change
=======================================
bob   |     0     |   1    |   2 
jane  |     0     |   2    |   0 
jim   |     0     |   0    |   1 
robert|     1     |   0    |   0 

[lightfighter]

Thanks mjdamato,

 

That worked great.

 

Just one more question for you if you don't mind?  If the work is set as a foreign key, and in that table only has the work ID, where and how do you insert the additional query to get the information from the other table to display the work name and not the work id number?

 

Also, can I add queries to the beginning of the document to pull up the information based off of a search for name and date range?

 

Thanks again.

[Daniel0]

You should start with normalizing the database though...

 

http://en.wikipedia.org/wiki/Database_normalization

[.josh]

I will def agree that if it's an option, restructure your database.  I kind of assumed that if that was an option, OP would have mentioned it.  Right, OP? 

[Psycho]

Just one more question for you if you don't mind?  If the work is set as a foreign key, and in that table only has the work ID, where and how do you insert the additional query to get the information from the other table to display the work name and not the work id number?

 

Had I known that the "work" was a fireign key in your table it would have made all of this MUCH easier and  would have saved me a lot of time. Plus, now I'm guessing that the customer is a foreign key in the table as well. I'm really not up for taking another wack at this without being provided ALL the details.

[lightfighter]

Sorry about that.  Because I don't know how to do this, I didn't know the information required to make it easier. 

 

Yes, there is a foreign key, which I was hoping to use that to display the header for the table columns.  The rest is ok and it all displays just fine.  Without using the foreign key, I can just have a legend at the bottom to do the same thing.  So rewriting the code is not completely necessary.  What you have given will work just fine.

 

It is not a primary request to make the report defined as generated from a search form, but I am trying to learn here.  The answer you gave will help me do what I have been requested to do.  I thank you for your help, but I also want to learn, not just have code given to me.    That is why I asked the follow up questions about having it generated from a search by name and date range.

 

Do you know of a tutorial or reference book that I can learn how to do this?  Again, thank you for your help.

[Psycho]

If you want to learn more just google for tutorials on using JOINS in queries. Unfortunately tying to explain how to do it in a forum would be difficult because it would just take too much information to try and explain. Plus, I am by no means an expert in database queries, but for situations that are not strait-forward, I can typically get what I need after a little trial and error.

[lightfighter]

Well, I have done a lot of research and 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 it was 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";

 

 

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'];

}

 

 

How does it know that $reasons needs to display call_reason.reason and not call_reason.reason_id or call_log.reason (when call_log.reason is the foreign key equal to call_reason.reason_id)?

 

Thank you very much.

 

[Psycho]

Did you take a look at any tutorials regardng JOINs? That is kind of the point of a tutorial - to eplain how something works. I could give you a very "gneral" statement of what it is doing, but to really give you enough to understand I'd basically have to write a tutorial.

 

Generally speaking, when you JOIN two tables you create a result set that includes every combination of records from table 1 to records in table 2. So, for the first record in table 1 you would get multiple results where that record is joined to each record from table 2. This is almost never what you want. You only want the records from table 1 to be joined to the records that it is related to in table 2. So, your JOIN will include some instruction for doing this such as stating that a value in table 1 must equal a value in table to (e.g. the id must be the same as the foreign key).

 

Anyway, I could go on explaining, but honestly I don't have the time and there are plenty of good turorials out there that will do a better job than I could in a forum post.

 

http://www.tizag.com/mysqlTutorial/mysqljoins.php

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

http://www.webdesign.org/web/web-programming/php/mysql-join-tutorial.14876.html

http://www.tutorialized.com/tutorial/MySQL-Table-Joins/482

http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html