Need help with MySQL count() query.

[johnkelly]

Hi, I want to count two columns in the same table and provide a different condition for each column count. The query starts as ...

SELECT count(tid), count(pid) FROM table WHERE

... now here is where I am stuck.

If I want to restrict the count of column tid by adding tid = pid to the end of the above query the count of tid is correct.

If I want to restrict the count of column pid by adding pid != tid to the end of the above query the count of pid is correct.

BUT I want to add both conditions to the SAME query. Is there anyway this can be done?

[van__]

$stmt = "SELECT count(tid) as tid , count(pid) as pid FROM table WHERE ...";

[van__]

Oh, ya... now that I bothered to actually read your post, instead of just looking at bad syntax...

No.. not really.
Kind of saying be both true and false at the same time, and logical programmatic constructs don't like being irrational.

What you'd do here is code for it.

<?
$stmt = "SELECT tid,pid FROM table";
... do your query...
for($i=0;$count($mysql_num_rows($result);$i++) {
$myData[$i] = mysql_fetch_array($result,'MYSQL_ASSOC');
}

$tid = array();
$pid = array();

foreach ($myData as $key=>$val) {
foreach($myData[$key] as $nKey=>$nVal) {
if ($nKey=='tid') {
array_push($tid,$nVal);
} else {
array_push($pid,$nVal);
}
}
}

//now do the math...
for ($i=0;$i<count($tid);$i++) {
if ($pid[$i] != $tid[$i]) {
$no_equal++;
} else {
$is_equal++;
}
}

echo $no_equal . "<BR>";
echo $is_equal . "<BR>";
echo "From a total of: " . count($tid) . " rows.";
?>

[van__]

Actually, I must be smoking crack...
that was a tad wasteful...
Here's a cleaner way...

<?
$stmt = "SELECT tid,pid FROM table";
... do your query...
for($i=0;$count($mysql_num_rows($result);$i++) {
$myData[$i] = mysql_fetch_array($result,'MYSQL_ASSOC');
}

$tid = array();
$pid = array();

foreach ($myData as $key=>$val) {
if ($myData[$key]['pid'] != $myData[$key]['tid']) {
$no_equal++;
} else {
$is_equal++;

}
}

echo "amount non-equal: " . $no_equal . "<BR>";
echo "amount equal: " .$is_equal . "<BR>";
echo "From a total of: " . count($tid) . " rows.";
?>

...ahhhhhhhhh much better

[fitzbean]

Just do 2 queries.