Reporting

[phatgreenbuds]

Ok so another hurdle.  Reporting on a database has thrown me for a loop.  Lets say I want to count the number of specific entries and display the count in report. for example:

5 red apples
6 green apples

where there are 5 unique rows that include "red" and "apples" and 6 unigue rows that include "green" and "apples".

I think i know the query part but the output is whats confusing me.

[code]
$result = mysql_query("SELECT count(*) FROM mydb GROUP BY color AND catagory");

$num_rows = mysql_num_rows($result);

//Now how do you display it?

[/code]

[taith]

the mysql_num_rows() shows you how many rows it returned...

[code]
$result = mysql_query("SELECT count(color) FROM mydb GROUP BY color AND catagory");
$row = mysql_fetch_array($result);
print_r($row); #depends on your database which field they'll go into  :-)
[/code]

[phatgreenbuds]

so where does the mysql_num_rows() fit in?

[craygo]

So do you want to just group items and output them with a count?? you are not just looking for green and red apples, correct??

Ray

[phatgreenbuds]

I want to catagorize the unique entries where the uniqueness is keyed by two specific fields; color and catagory.  Then output that to the screen with the total count of the rows matching.

"$count $color $catagory" is the expected output.

[craygo]

Here is something. Change table and field names to fit your table
[code]<?php
$table = "items";
// Query database
$sql = "SELECT count(ITEM) AS total, CATAGORY, ITEM, QTY_REQ, STILL_NEEDS FROM ".$table." ";
$sql .= "GROUP BY CATAGORY, ITEM";
  $res = mysql_query($sql);
  $num_rows = mysql_num_rows($res);
if($num_rows > 1){
    while ($rows = mysql_fetch_assoc($res)){
// Print Database Details
echo $rows['total']." -- ".$rows['ITEM']." -- ".$rows['CATAGORY']."<br />";
}
} else {
// Print No data message
print '<strong>NO ITEMS!!</strong>';
}
?>[/code]

I have a table I use for testing the ITEM is the color for your table

Ray

[phatgreenbuds]

well now that works...I wish i understood it...

I will have to do some reading to understand what you have done here.

[craygo]

One problem you had with your query is that you used AND in your GROUP BY clause. The GROUP BY is seperated with comma's. Only the WHERE clause uses AND.

Ray

[phatgreenbuds]

ok so I am playing around with this a little more and its starting to make sense.  I am curious tho about the period in this line before the = sign. What does it do?

$sql .= "GROUP BY CATAGORY, ITEM";

[taith]

.= just adds the string onto the end of a preexisting string

[code]
<?
$str='hello';
$str.='world';
echo $str; #hello world
?>
[/code]

[phatgreenbuds]

ah so its just like a kind of append type thing...

Well your code cleared up some all of my confusion so thanks for the help.  This forum has yet to let me down.

[HuggieBear]

Check out the first paragraph on this page: [url=http://uk.php.net/manual/en/language.operators.string.php]String Operators[/url]

Regards
Huggie