List Mysql Result Properly For Google Graph

[jeger003]

I am trying to create a google graph but I can't get php and mysql to list results properly for google graph.

 

this is my query -- i understand its multiple loops but I can't figure out any other way.

 

 

 

 

$query = mysql_query("SELECT DISTINCT(RNAME) AS tobs, DATE_FORMAT(`FE`,'%H') AS hours, COUNT(oKey) AS KeyCount
FROM table1
WHERE DA IS NULL
GROUP BY RNAME
ORDER BY RNAME, hours DESC
LIMIT 3") or die(mysql_error());

while($get = mysql_fetch_array($query))
{
$TOB = $get['tobs'];

echo "----";
$query2 = mysql_query("SELECT 24hour FROM grh ORDER BY 24hour") or die(mysql_error());
while($hour = mysql_fetch_array($query2))
{
$hour1 = $hour['24hour'];
echo "data.addRow([new Date(2012, 10 ,1,$hour1,00),";

$query3 = mysql_query("SELECT DATE_FORMAT(`FE`,'%H') AS hours2, COUNT(oKey) AS KeyCount2, RNAME AS FB
FROM table1
WHERE RNAME = '$TOB'
AND DA IS NULL
AND DATE_FORMAT(`FE`,'%H') = $hour1
GROUP BY RNAME
ORDER BY hours2 DESC") or die(mysql_error());

if(mysql_num_rows($query3) == 0)
{
echo "0,]);<br>";
}
else
{
while($final = mysql_fetch_array($query3))
{

echo $final['KeyCount2']."]);<br>";//echo $hour1." - ".$final['KeyCount2']." - ".$TOB."<br>";
}
}

}
}

 

 

 

the output

 

 

 

data.addColumn('datetime', 'Date');
var data = new google.visualization.DataTable();
data.addColumn('number', 'COLUMN1');
data.addColumn('number', 'COLUMN2');
data.addColumn('number', 'COLUMN3');
LOOP 1 -- data.addRow([new Date(2012, 10 ,1,00,00),0,]);
data.addRow([new Date(2012, 10 ,1,01,00),0,]);
data.addRow([new Date(2012, 10 ,1,02,00),0,]);
data.addRow([new Date(2012, 10 ,1,03,00),0,]);
data.addRow([new Date(2012, 10 ,1,04,00),0,]);
data.addRow([new Date(2012, 10 ,1,05,00),0,]);
data.addRow([new Date(2012, 10 ,1,06,00),0,]);
LOOP 2 --- data.addRow([new Date(2012, 10 ,1,00,00),0,]);
data.addRow([new Date(2012, 10 ,1,01,00),0,]);
data.addRow([new Date(2012, 10 ,1,02,00),0,]);
data.addRow([new Date(2012, 10 ,1,03,00),0,]);
data.addRow([new Date(2012, 10 ,1,04,00),0,]);
data.addRow([new Date(2012, 10 ,1,05,00),0,]);
data.addRow([new Date(2012, 10 ,1,06,00),0,]);

LOOP 3 --- data.addRow([new Date(2012, 10 ,1,00,00),76]);
data.addRow([new Date(2012, 10 ,1,01,00),0,]);
data.addRow([new Date(2012, 10 ,1,02,00),0,]);
data.addRow([new Date(2012, 10 ,1,03,00),0,]);
data.addRow([new Date(2012, 10 ,1,04,00),0,]);
data.addRow([new Date(2012, 10 ,1,05,00),0,]);
data.addRow([new Date(2012, 10 ,1,06,00),0,]);

I need the out put to look like this

 

if for the date/time (2012, 10 ,1,01,00) all three columns had

 

COLUMN1- 100

COLUMN2- 200

COLUMN3- 300

 

I want output

 

data.addRow([new Date(2012, 10 ,1,01,00),100,200,300]);

[PFMaBiSmAd]

See the following thread on detecting a change in a value, closing out a previous section, and starting a new section as you iterate over the rows in your result set - http://forums.phpfreaks.com/topic/271047-loop-throught-sql-results-and-dont-print-repeated-data/#entry1394613

 

You would detect a change in the date.

[jeger003]

spent the last 4 hours trying to implement your code to have it display the way I want but with no success. How do i list columns first and then have the hour show up only once and the matching COUNTS. I'm pulling my hair out on this one. I'd appreciate any help.

 

COLUMN 1

COLUMN 2

COLUMN 3

 

 

data.addRow([new Date(2012, 10 ,1,HOUR,00),COLUMN 1 COUNT, COLUMN 2 COUNT, COLUMN 3 COUNT]);

[jeger003]

$query = mysql_query("
SELECT DATE_FORMAT(`FirstExtracted`,'%H') AS hours2, COUNT(ObjectKey) AS KeyCount2, 
RobotName AS FinalBot
FROM gday
WHERE DateActioned IS NULL
GROUP BY RobotName, hours2
ORDER BY hours2 ASC LIMIT 10") or die(mysql_error());
$last_heading = null; // remember the last heading, initialize to a value that will never appear in the data



while($row = mysql_fetch_array($query))
{
   $hour = $row['hours2'];
   $KeyCount = $row['KeyCount2'];

   $data[] = array('id_name'=>$hour,'phone number'=>$KeyCount,'email'=>')]');

   foreach($data as $row){ // loop over the sample data, in place of the traditional while(){} loop on the line above
           // detect a change in the heading
           if($last_heading != $hour){
                   // heading change, either a new one or the first one
                   if($last_heading != null){
                           // not the first section, close out the previous section here ...
                           echo $last_email . '*';
                   }
                   // start a new section here...
                   echo "data.addRow([new Date(2012, 10 ,1,".$hour. ',00),**';

                   // remember values from the current row
                   $last_heading = $hour; // remember the heading
                   $last_email = $row['email']; // remember the email since it will have changed to the next value at the time you need the last value
           }
           // output the data under each heading here...
           echo $KeyCount . ',';
   }
   // close out the last section here, if any...
   if($last_heading != null){
           echo $last_email . '';
   }  

}

 

Output

 

[color=#000000][font='Times New Roman'][size=1]data.addRow([new Date(2012, 10 ,1,00,00),21,)]5,5,)]165,165,165,)]879,879,879,879,)]450,450,450,450,450,)]31,31,31,31,31,31,)]166,166,166,166,166,166,166,)]101,101,101,101,101,101,101,101,)]26,26,26,26,26,26,26,26,26,)]65,65,65,65,65,65,65,65,65,65,)][/size][/font][/color]

[jeger003]

can anyone help me