[SOLVED] It works, but still error message

[brentnerb]

Hi.  I have this thing where someone can choose up to 3 sets of criteria, query the database and then it compares the results of the 3 sets month by month.  It seems to be working fine - I get the expected results - but I also get "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource" for the line with the while statement.  Also I added error_reporting(E_ALL) at the top and started getting "Notice: Undefined offset: 3" too.  But still the results are returned.  I dont get it.  Any ideas?  Here is the current code:

 

        for ($i=0; $i <= $totalQueries; $i++) { // WHATEVER PROCESSING IS GOING TO BE DONE, IT NEEDS TO BE DONE ONCE PER QUERY (1 - 3 TOTAL QUERIES)   

                //var_dump($result[$i]);

                while($row = mysql_fetch_assoc($result[$i])){ // **** this works but is generating an error, try it another way ????

                        //var_dump( $row);

                        $yea = $_POST['start_year'];

                        $mon = $_POST['start_month'];

 

                        for ($m = 0; $m <= $number_of_months; $m++){

                                if (($row['year'] == $yea) && ((int)$row['month'] == $mon)) {

                                        $data[$i][$m]++;

                                }

                                $mon++; // INCREMENT actual MONTH number, (position within total num of months is auto incremented by the for stmnt

                                if($mon >= 13) { // IF MONTH IS MORE THAN 12, SET MONTH BACK TO ONE AND UP THE YEAR BY ONE

                                        $mon = 1;

                                        $yea++;

                                }

                        }

                }

        }

 

OH and also if I change mysql_fetch_assoc($result[$i]) to mysql_fetch_assoc($result[0]) it works fine, just outputs the results of the same query 3 times.  It doesn't generate the error though.

 

Thanks,

 

Brent

[PFMaBiSmAd]

This line -

 

($i=0; $i <= $totalQueries; $i++)

 

should be -

 

($i=0; $i < $totalQueries; $i++)

 

But you have a bigger problem in that you are executing similar queries inside of a loop instead of using a single query to retrieve all your data at one time and just looping through the results. Posting your code that is performing the queries would help.

[brentnerb]

Thanks! changing the <= to < got rid of the error message!

 

Yeah, I thought doing separate queries was probably a dumb way of going about it, but I wasn't sure how else to have 3 distinct sets of results.  I'm graphing them out in a month-by-month comparison chart so I thought I would need to do a separate query for each set of results being compared. 

 

Anyway, its working now so I am happy.  The whole code, if you're at all interested, goes like this (there are probably a ton of dumb mistakes in it, oh well...):

 

<?php

 

error_reporting(E_ALL);

 

// FAKE POST DATA:

$_POST['start_year'] = 2008;

$_POST['start_month'] = 7;

$_POST['end_year'] = 2009;

$_POST['end_month'] = 5;

$_POST['label[0]'] = "Classroom Support Calls";

$_POST['label[1]'] = "Classroom Check Reports";

$_POST['label[2]'] = "Other Calls";

 

$count_years = $_POST['end_year'] - $_POST['start_year'];

$years_months = $count_years * 12;

$count_months = $_POST['end_month'] - $_POST['start_month'];

$number_of_months=($years_months + $count_months);

 

include("includes/db.inc.php");

 

if ($_GET['action'] == 'ajaxGraph') { // ******* NEED TO CHANGE GET TO POST TO MAKE IT ACTUALLY WORK  !!!!! ***** !!!! ***** !!!!!

$additionalQueries = 2;

$totalQueries = 3;

// $totalMonths = 9; // will be determined based on the dates submitted

// $startMonth = 7; // 8/2008 was the first month of classroom checks. so we might as well go back to 7/2008

// $startYear = 2008;

// $label = "Customer Support Calls";

 

 

$db = CreateConnection();

$result = array();

$sql_1 = "SELECT id, DATE_FORMAT(datecreated,'%Y') as year, DATE_FORMAT(datecreated, '%m') as month FROM `report_building` WHERE phone_code = '10000' ORDER BY datecreated ASC"; // classroom support calls

$result[0] = mysql_query($sql_1,$db) or print "Error with result 1 ".mysql_error();

 

$sql_2 = "SELECT id, DATE_FORMAT(datecreated, '%Y') as year, DATE_FORMAT(datecreated, '%m') as month FROM `report_building` WHERE phone_code = '0' ORDER BY datecreated ASC"; // classroom check reports

$result[1] = mysql_query($sql_2,$db) or print "Error with result 2 ".mysql_error();

 

$sql_3 = "SELECT id, DATE_FORMAT(datecreated, '%Y') as year, DATE_FORMAT(datecreated, '%m') as month FROM `report_building` WHERE phone_code != '0' && phone_code != '10000' ORDER BY datecreated ASC"; // calls - not classroom support

$result[2] = mysql_query($sql_3,$db) or print "Error with result 3 ".mysql_error();

 

$label = array();

$label[0] = "Classroom Support Calls";

$label[1] = "Classroom Checks";

$label[2] = "Other Calls";

 

CloseConnection($db);

 

$data = array();

for ($i=0; $i <= $additionalQueries; $i++) {

 

$data[$i] = array(); // makes up to 3 arrays, one for each possible query

for ($p=0; $p <= $number_of_months; $p++) { // EACH ARRAY NEEDS AS MANY ELEMENTS AS MONTHS WE NEED DATA FOR

$data[$i][$p] = 0; // EACH ELEMENT STARTS OUT EMPTY

}

}

 

for ($i=0; $i < $totalQueries; $i++) { // WHATEVER PROCESSING IS GOIG TO BE DONE, IT NEEDS TO BE DONE ONCE PER QUERY (1 - 3 TOTAL QUERIES)

//var_dump($result[$i]);

while($row = mysql_fetch_assoc($result[$i])){ // **** this works but is generating an error, try it another way ????

// var_dump( $row);

$yea = $_POST['start_year'];

$mon = $_POST['start_month'];

 

for ($m = 0; $m <= $number_of_months; $m++){

if (($row['year'] == $yea) && ((int)$row['month'] == $mon)) {

$data[$i][$m]++;

}

$mon++; // INCREMENT actual MONTH number, (position within total num of months is auto incremented by the for stmnt

if($mon >= 13) { // IF MONTH IS MORE THAN 12, SET MONTH BACK TO ONE AND UP THE YEAR BY ONE

$mon = 1;

$yea++;

}

}

}

}

 

 

makeGraph($additionalQueries,$number_of_months,$data,$_POST['start_month'],$_POST['start_year'],$label);

}

 

 

 

 

/* ONCE THE DATA IS ALL RETRIEVED THIS FUNCTION WILL GRAPH IT OUT */

 

function makeGraph($additionalQueries,$totalMonths,$data,$startMonth,$startYear,$label){

/*

VARIABLES THE FUNCTION NEEDS:

$additionalQueries: you can do a search for just one query. You have to do at least one or else there is no data to dispaly.  If you do more than one then thats where this variable comes in.

$totalMonths: the number of months you selected in your quey.  Has to be the same for all queries involved, so we only have one totalMonths value for all.

$data[]; IS AN ARRAY of between 1 and 3 ARRAYS. Each of those arrays contains only the monthly values of whatever search you did.

$startMonth: the number 1-12 of the first month contained in data[].

$startYear: the year YYYY of the first month contatined in the data[].

$label[]: IS AN ARRAY of between 1 and 3 values coresponding to the $data[] array. Each value is a string designating a label for that query which will be displayed in the key.

*/

 

$graphBody = "";

 

$grandTot = 0; // initialize grandTot

foreach ($data as $key => $value){

foreach($value as $row => $data){

if ($data > $grandTot) {

$grandTot = $data;

}

}

}

 

$theNumber = (95/$grandTot); // this is the number that determines what percentage of the div each bar's height is *** ** ** **

 

$colors = array(); // these values are static i.e. will not be passed to the function i.e. will always be the same i.e. ice-T

$colors[0]="rgb(200,20,20)";

$colors[1]="rgb(20,20,200)";

$colors[2]="rgb(20,200,20)";

 

$totalWidth = (($totalMonths +1) * ( 25 * ($additionalQueries +1) +50 )) - 30; // this is a little convoluted but it works

if ($totalWidth < 400) {

$totalWidth = 600; // it looks funny if its too small.....

}

 

$graphBody .= "<div style='width:".$totalWidth."px; height:400px; position:relative; top:160px; margin:auto; border:4px solid #ccc;'>";

 

for($i=0; $i<=$additionalQueries; $i++) { //****** $i = a single query (one of the possible three total)

$left = $i * 30; // each query within the same month moves over 30px for the next bar

global $data; // HAVE TO GLOBALIZE THIS TO MAKE IT WORK!!!!!!!!

foreach($data[$i] as $key => $value) {

$graphBody .= "<div style='width:20px; height:".($value*$theNumber)."%; background-color:".$colors[$i]."; position:absolute; bottom:5; left:".($left+5)."; padding-left:5px;'><div style='position:absolute; top:-16px;'>".$value.

"</div><div style='position:absolute; bottom:-40px;'>";

if ($i==0) { // only print the month/date for the first bar of each month (if there are mutliple bars/queries)

$graphBody .= $startMonth."-".substr($startYear,2);

$startMonth++; // increment the month

if ($startMonth >= 13) { // if necessary increment the year and reset month to 1

$startMonth = 1;

$startYear++;

}

}

$graphBody .= "</div></div>";

$left = $left + (25 * ($additionalQueries + 1)) + 50; // each new month, the first bar moves over this much for the next section

}

}

$graphBody .= "</div>";

 

$graphBody .= "<div id='key' style='border:4px solid #ccc; position:absolute; top: 20px; right: 20px;'>";

 

for ($i=0; $i<=$additionalQueries; $i++) {

$graphBody .= "<div id='key_".$i."' style='background-color:".$colors[$i]."; padding:8px;'>".$label[$i]."</div>";

}

$graphBody .= "</div>";

 

print $graphBody;

}

?>