Topic: Need help with mysql select statements

[masteroleary]

How do I add the mysql count function to a select statement that selects data from multiple tables.
In this example below I can select data from different columns and tables. I then have a select statement that counts the id in multiple tables. But when I put the two together I get the error shown in result 3.

Also, I use 'AS' to create an alias name for each piece of data. Although I do this for the 'COUNT' items in $querycount, results two give me 4 when there are only 2 rows, adding both count statements together. What is the correct way to retrieve the number of rows for each table individually without the COUNT statements adding together?

Code: [Select]

$queryid = 'Select site.id AS siteid, shoot.id AS shootid From site, shoot';
$querycount = 'Select COUNT(site.id) AS sitecount, Count(shoot.id) AS shootcount From site, shoot';
$querymixed = 'Select COUNT(site.id) AS sitecount, Count(shoot.id) AS shootcount, site.id AS siteid, shoot.id AS shootid From site, shoot';
$resultid = mysql_query($queryid);
$resultcount = mysql_query($querycount);
$resultmixed = mysql_query($querymixed);

while($rowid = mysql_fetch_array($resultid, MYSQL_ASSOC)){
print_r($rowid);
}
echo '<br /><br />';
while($rowcount = mysql_fetch_array($resultcount, MYSQL_ASSOC)){
print_r($rowcount);
}
echo '<br /><br />';
while($rowmixed = mysql_fetch_array($resultmixed)){
print_r($rowmixed);
}
Result 1:
Array ( [siteid] => 1 [shootid] => 1 ) Array ( [siteid] => 2 [shootid] => 1 ) Array ( [siteid] => 1 [shootid] => 2 ) Array ( [siteid] => 2 [shootid] => 2 )

Result 2:
Array ( [sitecount] => 4 [shootcount] => 4 )

Result 3:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...

[taith]

whats the exact error?

Code: [Select]

$resultmixed = mysql_query($querymixed) or die(mysql_error());


[masteroleary]

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[masteroleary]

Dont ask me why this makes it work. I dont know sql yet and I really didnt understand the definitions for the keywords added (' GROUP BY ' & ' WITH ROLLUP' )

Code: [Select]

$querymixed = 'Select COUNT(site.id) AS sitecount, Count(shoot.id) AS shootcount, site.id AS siteid, shoot.id AS shootid From site, shoot GROUP BY site.id, shoot.id WITH ROLLUP';