Page 1 of 2

MySQL Output Help

Posted: Thu Aug 02, 2007 10:25 am
by icesolid
I am trying to get the following query to output into a certain format.

The code I am using is below:

Code: Select all

<?php
include("connect.php");

$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered, '%M %Y') as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");

echo "January 2006 = 0<br>";
echo "February 2006 = 0<br>";

$i = "3";

while($row = mysql_fetch_assoc($result)) {
    echo $row["yearmonth"] . " = " . $row["ordercount"] . "<br>";
	
    if($i == "12") {
        $i = "0";

        $year = explode(" ", $row["yearmonth"]);

        $result1 = mysql_query("SELECT count(*) as ordercount FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)=" . $year["1"] . " GROUP BY LEFT(DATE(date_ordered), 4)");
        $row1 = mysql_fetch_assoc($result1);

        echo "<br><b>Total For " . $year["1"] . " = " . $row1["ordercount"] . "</b>";
        echo "<hr>";
    }

    $i++;
}
?>
I get this result:

January 2006 = 0
February 2006 = 0
March 2006 = 9
April 2006 = 12
May 2006 = 58
June 2006 = 166
July 2006 = 155
August 2006 = 243
September 2006 = 191
October 2006 = 208
November 2006 = 199
December 2006 = 358

Total For 2006 = 1599
--------------------------------------------------------------------------------
January 2007 = 367
February 2007 = 364
March 2007 = 423
April 2007 = 474
May 2007 = 438
June 2007 = 378
July 2007 = 380
August 2007 = 21

I would like to make this print out totals for each year. I have to manually print the first two months of 2006 because there was nothing ordered in those months, however I still want it to show that none were ordered.

I would like the final output to look like this:

January 2006 = 0
February 2006 = 0
March 2006 = 9
April 2006 = 12
May 2006 = 58
June 2006 = 166
July 2006 = 155
August 2006 = 243
September 2006 = 191
October 2006 = 208
November 2006 = 199
December 2006 = 358

Total For 2006 = 1599
--------------------------------------------------------------------------------
January 2007 = 367
February 2007 = 364
March 2007 = 423
April 2007 = 474
May 2007 = 438
June 2007 = 378
July 2007 = 380
August 2007 = 21

Total For 2007 = 2845
--------------------------------------------------------------------------------

Posted: Thu Aug 02, 2007 12:33 pm
by pickle
== is a comparison operator, not an assignment operator.

Code: Select all

$i == "0";
is probably not doing what you want it to. Also, there's no need for the double quotes when assigning.

Posted: Thu Aug 02, 2007 12:34 pm
by ReverendDexter
I'm wondering if what you'll have to do is make a lookup table with all of your months, and then do a left join of that on your query results.

Posted: Thu Aug 02, 2007 12:56 pm
by icesolid
Would that involve me making a table called months and putting all of the months Jan 2006 till Dec 2006 and same for 2007?

I am trying to make this thing print out each month for each year since 2006 and print how many cases were ordered in each month.

This is the way I am doing it now, if there is an easier way help would be much appreciated.

Posted: Thu Aug 02, 2007 2:18 pm
by icesolid
I have put together some code that works. I do not think it is that efficient however it does work. I am going to have to try forcing some data to be 2008 to test and see if it will work going into 2008.

Code: Select all

<?php
include("connect.php");

$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered, '%M %Y') as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");

echo "January 2006 = 0<br>";
echo "February 2006 = 0<br>";

$i = "3";

while($row = mysql_fetch_assoc($result)) {
    echo $row["yearmonth"] . " = " . $row["ordercount"] . "<br>";
	
    if($i == "12") {
        $i = "0";

        $year = explode(" ", $row["yearmonth"]);

        $result1 = mysql_query("SELECT count(*) as ordercount FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)=" . $year["1"] . " GROUP BY LEFT(DATE(date_ordered), 4)");
        $row1 = mysql_fetch_assoc($result1);

        echo "<br><b>Total For " . $year["1"] . " = " . $row1["ordercount"] . "</b>";
        echo "<hr>";
    }

    $i++;
}

$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered, '%M %Y') as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7) DESC"); // By ordering DESC it is pulling the latest year out of the set
$row = mysql_fetch_assoc($result);

$year = explode(" ", $row["yearmonth"]);

$result1 = mysql_query("SELECT count(*) as ordercount FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)=" . $year["1"] . " GROUP BY LEFT(DATE(date_ordered), 4)");
$row1 = mysql_fetch_assoc($result1);

echo "<br><b>Total For " . $year["1"] . " = " . $row1["ordercount"] . "</b>";
echo "<hr>";
?>
I get this result:

January 2006 = 0
February 2006 = 0
March 2006 = 9
April 2006 = 12
May 2006 = 58
June 2006 = 166
July 2006 = 155
August 2006 = 243
September 2006 = 191
October 2006 = 208
November 2006 = 199
December 2006 = 358

Total For 2006 = 1599
--------------------------------------------------------------------------------
January 2007 = 367
February 2007 = 364
March 2007 = 423
April 2007 = 474
May 2007 = 438
June 2007 = 378
July 2007 = 380
August 2007 = 28

Total For 2007 = 2852

Posted: Thu Aug 02, 2007 2:27 pm
by pickle
Can we see what your database schema is? I too think this can be done much cleaner, but I'd like to see the database.

Posted: Thu Aug 02, 2007 2:31 pm
by icesolid
I would like to have the database spit out each year since 2006, with the month and year and the total cases ordered for that month and year. Even if there were zero cases ordered in that month. I still want to see that printed. The code I have above works but it doesn’t seem to be the best way to conduct this operation. I am just looking for a smoother, cleaner and more efficient way to do this.

This code also makes it so that it automatically will print a month for January 2008 and the total cases as soon as a case is ordered for 2008. It will not require me to add anything to do the code to make 2008 print out totals.

My 'cases' table structure is below:

Code: Select all

-- 
-- Table structure for table `cases`
-- 

CREATE TABLE `cases` (
  `id` int(11) NOT NULL auto_increment,
  `customer` text NOT NULL,
  `user_code` text NOT NULL,
  `control_number` text NOT NULL,
  `inspector_code` text NOT NULL,
  `reviewer_code` text NOT NULL,
  `viewed` text NOT NULL,
  `assigned` text NOT NULL,
  `printed` text NOT NULL,
  `saved` text NOT NULL,
  `inpurgatory` text NOT NULL,
  `sent_to_reviewer` text NOT NULL,
  `sent_to_customer` text NOT NULL,
  `ecd` date NOT NULL default '0000-00-00',
  `ecd_change` text NOT NULL,
  `date_ordered` date NOT NULL default '0000-00-00',
  `underwriter` text NOT NULL,
  `due_date` text NOT NULL,
  `date_assigned` date NOT NULL default '0000-00-00',
  `date_printed` date NOT NULL default '0000-00-00',
  `date_sent_to_reviewer` date NOT NULL default '0000-00-00',
  `date_completed` date NOT NULL default '0000-00-00',
  `date_marked_inpurgatory` date NOT NULL default '0000-00-00',
  `policy_number` text NOT NULL,
  `name_of_insured` text NOT NULL,
  `mailing_address` text NOT NULL,
  `mailing_city` text NOT NULL,
  `mailing_state` text NOT NULL,
  `mailing_zip` text NOT NULL,
  `location_address` text NOT NULL,
  `location_city` text NOT NULL,
  `location_state` text NOT NULL,
  `location_zip` text NOT NULL,
  `location_territory` text NOT NULL,
  `survey_code` text NOT NULL,
  `survey_description` text NOT NULL,
  `restaurant_supplement` text NOT NULL,
  `rush` text NOT NULL,
  `agency_name` text NOT NULL,
  `agency_phone` text NOT NULL,
  `contact_name` text NOT NULL,
  `contact_phone` text NOT NULL,
  `coverages_building` text NOT NULL,
  `coverages_contents` text NOT NULL,
  `coverages_liability` text NOT NULL,
  `special_instructions` longtext NOT NULL,
  `brief_description` text NOT NULL,
  `type_of_policy` text NOT NULL,
  `policy_year_built` text NOT NULL,
  `policy_construction` text NOT NULL,
  `policy_gross` text NOT NULL,
  `effective_date` text NOT NULL,
  `note` longtext NOT NULL,
  `drawing` text NOT NULL,
  `photo1` text NOT NULL,
  `caption1` text NOT NULL,
  `photo2` text NOT NULL,
  `caption2` text NOT NULL,
  `photo3` text NOT NULL,
  `caption3` text NOT NULL,
  `photo4` text NOT NULL,
  `caption4` text NOT NULL,
  `photo5` text NOT NULL,
  `caption5` text NOT NULL,
  `photo6` text NOT NULL,
  `caption6` text NOT NULL,
  `photo7` text NOT NULL,
  `caption7` text NOT NULL,
  `photo8` text NOT NULL,
  `caption8` text NOT NULL,
  `photo9` text NOT NULL,
  `caption9` text NOT NULL,
  `photo10` text NOT NULL,
  `caption10` text NOT NULL,
  `photo11` text NOT NULL,
  `caption11` text NOT NULL,
  `photo12` text NOT NULL,
  `caption12` text NOT NULL,
  `photo13` text NOT NULL,
  `caption13` text NOT NULL,
  `photo14` text NOT NULL,
  `caption14` text NOT NULL,
  `photo15` text NOT NULL,
  `caption15` text NOT NULL,
  `photo16` text NOT NULL,
  `caption16` text NOT NULL,
  `photo17` text NOT NULL,
  `caption17` text NOT NULL,
  `photo18` text NOT NULL,
  `caption18` text NOT NULL,
  `photo19` text NOT NULL,
  `caption19` text NOT NULL,
  `photo20` text NOT NULL,
  `caption20` text NOT NULL,
  `charge` text NOT NULL,
  `reviewer_initials` text NOT NULL,
  `recommendations` text NOT NULL,
  `billing_note` text NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `control_number` (`control_number`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4713 ;

Posted: Thu Aug 02, 2007 3:46 pm
by pickle
Whoa - that baby could really use some normalization. But that's another issue.


What about a query like:

Code: Select all

SELECT
  count(*) as 'count',
  DATE_FORMAT(date_ordered,'%M') as month,
  DATE_FORMAT(date_ordered, '%Y') as year
FROM
  cases
WHERE
  user_code NOT IN('001','149','150') AND
  YEAR(date_ordered) >= 2006
GROUP BY
  LEFT(DATE(date_ordered),7))
You could then display it like this (pseudo-code)

Code: Select all

$resultset = query($query);
while(looping through the result set)
{
  put each row (ie: each month) in a separate entry in a multi-dimensional array
  ie:
    array['year']['month'] = count for that row
}

foreach(year in the array)
{
  $year_total = 0;
  for(month = 1; month < 13; month++)
  {
     if(the array has an entry for this month)
     {
       output array[year][month]
       $year_total = $year_total + array[year][month]
     }
       
     otherwise (the count must be zero)
  }

  output $year_total
  reset $year_total
}
That would certainly cut down on the number of queries you have to do.

Also, you've got a lot of fields set as type 'text' which isn't necessary unless you're expecting the fields to have 65K+ characters in them. For things like ids, varchar() is perfectly adequate.

Posted: Thu Aug 02, 2007 3:55 pm
by icesolid
What if I am not sure of the length of the fields though. Some fields are allowed up to like 500 characters. Do I just set all of the fields that are just text to varchar(500) in case?

I never used varchar() because I never wanted to define how many characters were allowed in the field.

And normalization, where could I optimize that?

Posted: Thu Aug 02, 2007 4:01 pm
by pickle
For the fields that require up to 500 characters, you can use text. I was referring more to fields like `user_code`, `control_number` & stuff like that. varchar() can only be up to 255 characters FYI.

As for normalization - I was referring to the `photoX` and `captionX` fields. You could put them all in a different table & connect them via the id.

Posted: Thu Aug 02, 2007 5:38 pm
by ReverendDexter
icesolid wrote:Even if there were zero cases ordered in that month. I still want to see that printed.
That's what makes this difficult... if no cases were ordered in say, January '06, then January '06 doesn't exist in your database; and if it doesn't exist, it's very hard to make it part of your output ;)

Which means you have to do one of two things: either make it exist (as part of a lookup table which would have every month/year that you wanted to see... not the most elegant thing in the world, but it will *definitely* work), or use some functionality (that I know not of) that treats month/year combos as an infinite serial set (a la integers), and would know by definition that there should be something between March '07 and May '07 if there's nothing in your db. But I don't think such a thing exists (well, maybe in Oracle...).


And, as a tangent: varchars are awesome - only take up as much room as they need, with a user defined cap.

Posted: Thu Aug 02, 2007 7:24 pm
by nathanr
I think your trying to do too much with mysql now.. you could easily add in a with rollup to the original sql to get totals, however that would make it harder to parse in php, so for the time being stick with the query you have, the single one that returns everything from the db in a usable format:

Code: Select all

$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered,'%M') AS month, DATE_FORMAT(date_ordered, '%Y') AS year FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
now you have all the data you need to play with, so no need for any more SQL.

next up is to turn all the data into the format you require, this is where PHP comes in.

Code: Select all

<?php
#include("connect.php");

/*	Create an array of all the months you want:
	which would be from Jan 06 to NOW (where now is the current date)
	may aswell create an additional array for total while we are here  */
$YearMonthCountArray = array();
$YearCountTotalArray = array();
for($y=2006;$y<=date('Y');$y++) {
	for($m=1;$m<13;$m++) {
		if((intval($y.$m) <= intval(date('Ym')))) {
			$YearMonthCountArray[$y][date('F', mktime(0,0,0,$m))] = 0;
		}
	}
	$YearCountTotalArray[$y] = 0;
}

/*	Form the query which will get the totals from the table in the database */
$result = mysql_query("SELECT count(*) as ordercount, DATE_FORMAT(date_ordered,'%M') AS month, DATE_FORMAT(date_ordered, '%Y') AS year FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)");
/*	cycle the results */
while($row = mysql_fetch_assoc($result)) {
	/*	add in count to the related month */
    $YearMonthCountArray[$row['year']][$row['month']] = $row['ordercount'];
	/*	increment year total accordingly */
	$YearCountTotalArray[$row['year']] += $row['ordercount'];
}

print_r($YearMonthCountArray);
print_r($YearCountTotalArray);
?>

Posted: Fri Aug 03, 2007 8:59 am
by icesolid
That seems to collect the data into an array that I need. Now I just don't get how I am going to dynamically turn this:

Array ( [2006] => Array ( [January] => 0 [February] => 0 [March] => 9 [April] => 12 [May] => 58 [June] => 166 [July] => 155 [August] => 243 [September] => 191 [October] => 208 [November] => 199 [December] => 358 ) [2007] => Array ( [January] => 367 [February] => 364 [March] => 423 [April] => 474 [May] => 438 [June] => 378 [July] => 380 [August] => 34 [September] => 0 ) ) Array ( [2006] => 1599 [2007] => 2858 )

Into this:

January 2006 = 0
February 2006 = 0
March 2006 = 9
April 2006 = 12
May 2006 = 58
June 2006 = 166
July 2006 = 155
August 2006 = 243
September 2006 = 191
October 2006 = 208
November 2006 = 199
December 2006 = 358

Total For 2006 = 1599
--------------------------------------------------------------------------------
January 2007 = 367
February 2007 = 364
March 2007 = 423
April 2007 = 474
May 2007 = 438
June 2007 = 378
July 2007 = 380
August 2007 = 34

Total For 2007 = 2858

Posted: Fri Aug 03, 2007 11:39 am
by nathanr
I'm strongly concidering getting an invoice lined up for this..

Posted: Fri Aug 03, 2007 12:01 pm
by John Cartwright
foreach()