[Resolved] turning two queries into one

[Tad]

Okay, I've been wandering around a bunch of join tutorials trying to get this, but still haven't had much luck. Just what exactly do you need to do to join two queries together to make one? Do both of the tables need to have the same data in them somewhere, or a field that is the same...? I'm just getting lost. Here's what I've got so far. It's two queries that print out the total number of items in my tables. I think that they could easily be turned into one query though. Any pointers?

<?php

$q="SELECT COUNT(animalid) as totalanimals FROM Fauna WHERE pending != 'yes'";
$result= mysql_query($q, $connection) or die 
("Could not execute query : $q." . mysql_error());
while ($row=mysql_fetch_array($result))
{
$totalanimals=$row["totalanimals"];
echo "<center>[ Fauna: <b>$totalanimals</b> | ";
}

$q="SELECT COUNT(plantid) as totalplants FROM Flora WHERE pending != 'yes'";
$result= mysql_query($q, $connection) or die 
("Could not execute query : $q." . mysql_error());
while ($row=mysql_fetch_array($result))
{
$totalplants=$row["totalplants"];
echo "Flora: <b>$totalplants</b> ]<br>";
}

$sum = ($totalanimals + $totalplants);
echo "[ Grand Total: <b>$sum</b> ]</center>";
?>

[TheBB]

Try this. I think it should work.

SELECT COUNT(Fauna.animalid) as totalanimals, COUNT(Flora.plantid) as totalplants FROM Fauna, Flora WHERE pending != 'yes'

Edit: Or this. Not sure.

SELECT COUNT(Fauna.animalid) as totalanimals, COUNT(Flora.plantid) as totalplants FROM Fauna, Flora WHERE Fauna.pending != 'yes' AND Flora.pending != 'yes'

I suppose you got the idea. Just experiment.

[Tad]

Okay, I understand why the first query wouldn't work. It doesn't recognize the pending bit. 🙂

The second one "works", but my totals don't show up correctly anymore.

$q=" SELECT COUNT(Fauna.animalid) as totalanimals, COUNT(Flora.plantid) as totalplants FROM Fauna, Flora WHERE Fauna.pending != 'yes' and Flora.pending != 'yes'";

$result= mysql_query($q, $connection) or die 
("Could not execute query : $q." . mysql_error());

while ($row=mysql_fetch_array($result))
{
$totalanimals=$row["totalanimals"];
$totalplants=$row["totalplants"];

echo "<center>[ Fauna: <b>$totalanimals</b> | Flora: <b>$totalplants</b> ]<br>";
}

$sum = ($totalanimals + $totalplants);
echo "[ Grand Total: <b>$sum</b> ]</center>";

And it prints out: [ Fauna: 4 | Flora: 4 ]
[ Grand Total: 8 ]

When it should be: [ Fauna: 4 | Flora: 1 ]
[ Grand Total: 5 ]

So somehow it's saying that both $totalanimals and $totalplants is equal to the value of $totalanimals. Thanks for the help though. I just need to figure out how to define each individual value now.

[tsinka]

Hi,

try this query:

SELECT COUNT(animalid) AS cnt FROM Fauna WHERE pending != 'yes'
UNION ALL
SELECT COUNT(plantid) AS cnt FROM Flora WHERE pending != 'yes'

This should return two rows containing the row count of each table.

Using a join here doesn't work since there is no relationship between these tables so you would get the count of the table with the most rows for both tables.

Additonally, I'd suggest not to use yes/no but e.g. 1/0 (with the appropriate column type) for the pending column (but that it as matter of design)

Thomas

[Tad]

Thanks tsinka. 🙂 I got it working. I'll be sure to change those pending columns too.