returning a negative number in count function shouldn't be

[SpecialFX]

I'm running a count and it should be giving me a postive number but its negative.
Here is the code.

<?php
require ('dbconnect.php');
$query = "SELECT date, type, COUNT(count)  FROM countit GROUP BY date";
$result = mysql_query($query) or die(mysql_error());

$tquery = "SELECT date, type, COUNT(count)  FROM countit WHERE type='user' Group by type";
$tresult = mysql_query($tquery) or die(mysql_error());
$trow = mysql_fetch_array($tresult);


$row_count = 0;
$darkcolor = "#EEEEEE";
$lightcolor = "#FFFFFF";

function nextColor() {
    global $row_count, $darkcolor, $lightcolor;
    $return = (($row_count % 2) ? $darkcolor : $lightcolor);
    $row_count++;
    return $return;
}
echo'<table>';
echo '<tr align="left" valign="top" style="background: ' . nextColor() . '"><td><font face="Tahoma" size="2"><a href=index.php>Home</a></td></tr>';

$datetotal = $row['COUNT(count)'];
$usertotal = $trow['COUNT(count)'];
$amountbandtwo=($datetotal-$usertotal);
$squery = "SELECT * FROM countit WHERE one=1";
$sresult = mysql_query($squery) or die(mysql_error());
$samount = mysql_num_rows($sresult);

$userpercent = ($usertotal/$samount)*100;
$userpercent = round($userpercent, 1);
$bandpercent = ($amountbandtwo/$samount)*100;
$bandpercent = round($bandpercent, 1);



echo '<tr align="left" valign="top" style="background: ' . nextColor() . '"><td><font face="Tahoma" size="2">' . $row['date'] . '</font></td><td><font face="Tahoma" size="2">Total: ' . $row['COUNT(count)'] . '</font></td><td><font face="Tahoma" size="2">Band Visits: '.$amountbandtwo.'</font></td><td><font face="Tahoma" size="2">User Visits: ' . $trow['COUNT(count)'] . '</font></td></tr>';
}
echo'<tr align="left" valign="top" style="background: ' . nextColor() . '"><td colspan=4><font face="Tahoma" size="2">Total visits: '.$samount.' Percent Band: '.$bandpercent.'% Percent User '.$userpercent.'%</td></tr>';
echo '</table>';
?>

[bpat1434]

$datetotal = $row['COUNT(count)'];
$usertotal = $trow['COUNT(count)'];

Invalid code.... your queries should be set up like:

SELECT date, type, COUNT(count) AS cnt

and change your PHP code to be:

$datetotal = $row['cnt'];
$usertotal = $trow['cnt'];

[SpecialFX]

now the date total is blank the bandamount is negative and the user visits are also blank and the band percent is negative.

[Weedpacket]

Just curious:

SELECT date, type, COUNT(count)  FROM countit GROUP BY date

SELECT date, type, COUNT(count)  FROM countit WHERE type='user' Group by type

Why isn't the dbms complaining that you've got nonaggregated fields that aren't grouped ('type' in the first one, and 'date' in the second)?

The only way I can see negative numbers getting into your results is if $datetotal < $usertotal - that being the only subtraction going on. Since a priori I don't see any reason why the first query won't return multiple rows (once for each date), and the only reason why the second will only return one row is because the field in the group by clause is being used in the where clause (making the group by irrelevant, incidentally) ... I don't see anything to specify which date you're trying to get a count of, and if the query does return multiple rows, you only look at the first - without apparently caring what date it is for.

[SpecialFX]

It returns multiple rows, and I don't follow what you're saying.

[Weedpacket]

That's not surprising; I don't follow what your code is doing 🙂

[SpecialFX]

I PM you the link to show you what it looks like.

[SpecialFX]

Weedpacket, did you get my example?

[Weedpacket]

SpecialFX wrote:

Did you get my example?

Yes, but:

1. A random user (out of eighty thousand) sends me a random link and I'm supposed to know what it relates to and what to do with it

2. If it's broken, seeing what it does do is useless as an explanation for what it should do

3. I don't do this stuff by PM for the obvious reason that it's not public and is therefore worthless as part of a community project

SpecialFX wrote:

It returns multiple rows,

But you're only using one of them, with no control over which one.