PHP script

[vicmad]

I'm running SNMP Zenoss connected to mysql database.I'm using a PHP script to select the device that fails the most based on the severity level 5 and 4 for a particular year and month. The script works when I select severity = 5 but does not work when i have a mixture of 5 and 4.
Severity 5 is highest and 1 is the lowest. Table name is history.
-Column name called "devices" has all the device names.
-Column name called "events" has all the events that are generated.
-Column name called "severity" has severity levels from 5-1.


BELOW IS THE PHP SCRIPT THAT WORKS WHEN SEVERITY = 5

$yearNum= $_GET["year_num"];
$monthNum= $_GET["month_num"];


$filename = "exported_tickets.txt";


$Handle = fopen($filename, 'w');


mysql_connect("--.--.--.--", "username", "password") or die(mysql_error());
mysql_select_db("events") or die(mysql_error());



// Collects data from "history" table: filtering based on the year and month.



$result = mysql_query ("SELECT device,
count(*) AS number_of_events
from history
where severity = 5
AND MONTH(stateChange)= ".$monthNum." AND YEAR(stateChange) = ".$yearNum."
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10") or die(mysql_error());

fwrite($Handle, "device".", "."number_of_events"." \n");


// store the record of the table into $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{


fwrite($Handle, $row["device"].", ".$row["number_of_events"]."\n");



}
mysql_free_result($result);

fclose($Handle);


BELOW IS THE PHP SCRIPT THAT DOES NOT WORK WHEN I HAVE A MIXTURE OF SEVERITY = 5 AND 4



$yearNum= $_GET["year_num"];
$monthNum= $_GET["month_num"];


$filename = "exported_tickets.txt";


$Handle = fopen($filename, 'w');


mysql_connect("--.--.--.--", "username", "password") or die(mysql_error());
mysql_select_db("events") or die(mysql_error());



// Collects data from "history" table: filtering based on the year and month.



$result = mysql_query ("SELECT device,
count(*) AS number_of_events
from history
where severity = 5
AND MONTH(stateChange)= ".$monthNum." AND YEAR(stateChange) = ".$yearNum."
GROUP BY Device
UNION ALL
SELECT device,
count(*) AS number_of_events
from history
where severity = 4
AND MONTH(stateChange)= ".$monthNum." AND YEAR(stateChange) = ".$yearNum."
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10") or die(mysql_error());

fwrite($Handle, "device".", "."number_of_events"." \n");


// store the record of the table into $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{


fwrite($Handle, $row["device"].", ".$row["number_of_events"]."\n");



}
mysql_free_result($result);

fclose($Handle);




PLEASE HELP

 

[jpadie]

why are you using a union query?

why not just

select device, count(*) as 'Number of Incidents'
from tablename
group by device

if you want more help I suggest using a more descriptive title, providing information on what is not working, what error messages you are getting etc etc.

and remember ALWAYS to post code within the [ignore]

[/ignore] tags when using this forum.

 

[jpadie]

actually i misunderstood.

SELECT
	device,
	SUM(
		CASE
			WHEN severity = 5
			THEN 1
			ELSE 0
		END
	) as 'Number of Severity 5 Incidents',
	SUM(
		CASE
			WHEN severity = 4
			THEN 1
			ELSE 0
		END
	) as 'Number of Severity 4 Incidents'

FROM	history
GROUP BY device

 

[vicmad]

Thanks Jpadie.
The first script works when SEVERITY = 5 and the result is available to a output text file.
The second script does not work when I have a mixture of severity 5 and 4. The result is not available to a output text file. There are no error messages. Just at the left side bottom of the browser says "Done".

The other issue I have is the first script works in Firefox and not in Internet Explorer.

Please Help

[COLOR=red]FIRST SCRIPT[/color]

$yearNum= $_GET["year_num"];
$monthNum= $_GET["month_num"];


$filename = "exported_tickets.txt";


$Handle = fopen($filename, 'w');


mysql_connect("--.--.--.--", "username", "password") or die(mysql_error());
mysql_select_db("events") or die(mysql_error());



// Collects data from "history" table: filtering based on the year and month.



$result = mysql_query ("SELECT device,
count(*) AS number_of_events
from history
where severity = 5
AND MONTH(stateChange)= ".$monthNum." AND YEAR(stateChange) = ".$yearNum." 
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10") or die(mysql_error());

fwrite($Handle, "device".", "."number_of_events"." \n");


// store the record of the table into $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

    
    fwrite($Handle, $row["device"].", ".$row["number_of_events"]."\n");



}
mysql_free_result($result);

fclose($Handle);


[COLOR=red]SECOND SCRIPT[/color]
$yearNum= $_GET["year_num"];
$monthNum= $_GET["month_num"];


$filename = "exported_tickets.txt";


$Handle = fopen($filename, 'w');


mysql_connect("--.--.--.--", "username", "password") or die(mysql_error());
mysql_select_db("events") or die(mysql_error());



// Collects data from "history" table: filtering based on the year and month.



$result = mysql_query ("SELECT device,
count(*) AS number_of_events
from history
where severity = 5
AND MONTH(stateChange)= ".$monthNum." AND YEAR(stateChange) = ".$yearNum." 
GROUP BY Device
UNION ALL
SELECT device,
count(*) AS number_of_events
from history
where severity = 4
AND MONTH(stateChange)= ".$monthNum." AND YEAR(stateChange) = ".$yearNum."
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10") or die(mysql_error());

fwrite($Handle, "device".", "."number_of_events"." \n");


// store the record of the table into $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

    
    fwrite($Handle, $row["device"].", ".$row["number_of_events"]."\n");



}
mysql_free_result($result);

fclose($Handle);

 

[jpadie]

you have the answer in my second post. add whatever time based where conditions as you wish.

firefox vs internet explorer cannot be related to anything server based like php