PHP & MySQL problem

aktelmiele · 03-12-2008 02:51PM #1

Hi all,

Trying to get this query working and its driving me mad

What it is trying to do is to retrieve the number of times a fault appeared today.

When I run the code it keeps saying there

There are hard drive corrupt etc.

Its supposed to say

There are 3 hard drive corrupt

The total wont appear.

When I echo the query that is being generated, I can run the query directly in MySQL and it returns the correct data. It just wont appear in my webpage :mad:

It will display, using XAMPP and PHP MyAdmin

[HTML]Faulttypecount(calllogs.callid)RAM 2Hard drive corrupt 3 [/HTML]

Sorry, trying to get it to appear in a table but not too successful :P

Faulttype is text and callid is an int in the database.

Anyone help me

Thanks

<?php

$con = mysql_connect("localhost","root","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("test", $con);

$query2 = 'Select faulttype , count(calllogs.callid) from `agents` LEFT JOIN calllogs ON agents.agentid=calllogs.agentid RIGHT JOIN faults ON calllogs.faultid=faults.faultid WHERE DATE_FORMAT( TIMESTAMP, \'%Y-%m-%d\' ) = CURDATE( ) AND agents.agentid = 1 group by faulttype';
echo $query2;c
$result2 = mysql_query($query2) or die("oops");

while($newArray2 = mysql_fetch_array($result2)){

echo "There are" ." ". $newArray2['COUNT(calllogs.callid)'] . " " . $newArray2['faulttype'];
	echo "<br />";
	
}
?>

Webmonkey · 03-12-2008 02:57PM

Try this

[php]<?php

$con = mysql_connect("localhost","root","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $con);

$query2 = 'Select faulttype , count(calllogs.callid) as callCount from `agents` LEFT JOIN calllogs ON agents.agentid=calllogs.agentid RIGHT JOIN faults ON calllogs.faultid=faults.faultid WHERE DATE_FORMAT( TIMESTAMP, \'%Y-%m-%d\' ) = CURDATE( ) AND agents.agentid = 1 group by faulttype';
echo $query2;c
$result2 = mysql_query($query2) or die("oops");

while($newArray2 = mysql_fetch_array($result2)){

echo "There are" ." ". $newArray2 . " " . $newArray2;
echo "<br />";

}
?>[/php]

Count(x) is a function, not a column. In the SQL output it could be something like EXPxxx but you have to dedicate a name to it.

aktelmiele · 03-12-2008 03:33PM

Nice one, Thanks

PHP & MySQL problem

Comments