[RESOLVED] count error when using join

showman13

Good morning All,

I am having an issue with getting a count of records from the adv_table based on the cat_id, when trying to do JOIN so I can get a count of adv_id's from 2 different levels based on cat_id and cat_parent...

to simplify:
adv_table contains records structured as follows:
adv_id / mem_id / cat_id / etc. (most important columns)

cat_table contains records structured as follows:
cat_id / cat_name / cat_parent / etc. (most important columns)

What I need is strictly a count of total records for a single cat_id PLUS
the total records of all cat_id's that have a cat_parent that matches the single cat_id

if cat_id is 1, then I need the count to include all adv_table recs with cat_id=1
PLUS
All adv_table recs with cat_id's where the cat_parent for that cat_id equals 1

I hope that makes sense, and that I'm not confusing the issue.

Anyway, my query consistently comes back with a 1 in every case, both when I know there are multiple records that would be in the intended count, and when there are 0 recs to count.

Here are the related queries that I have. First is a straight count and second is intended to count the ads in the sub categories related to the base category.

This should list the base categories including a count of all ads related to this base category, whether under the base_cat or one of the sub_cats.

$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
$basecat = mysql_query($sql) or die($sql);
$cat_count=0;

while($cat_list=mysql_fetch_array($basecat)){
++$cat_count;

$sql_ct = mysql_query("SELECT count(adv_id) FROM adv_table where cat_id='".$cat_list[0]."'");
$basecount = mysql_num_rows($sql_ct);

$sql_ct = mysql_query("SELECT count(a.adv_id) FROM a.adv_table, b.cat_table WHERE a.cat_id=b.cat_id AND b.cat_parent='".$cat_list[0]."'");
$subcount = mysql_num_rows($sql_ct);

$totcount = $basecount + $subcount;

  print "<strong>".$cat_list[1]." (".$totcount.")</strong><br />";
}

I'm guessing that my join is messed up, but being a bit limited in knowledge on the use of joins, I can't see what the issue is.

Any suggestions?

showman13

Added Info...

When I try to run the last query directly in PHPmyAdmin, using a cat_id that has multiple sub cats, and they all have ads listed under them :

this is the error result I get

SQL query: Documentation

SELECT count( a.adv_id )
FROM a.adv_table, b.cat_table
WHERE a.cat_id = b.cat_id
AND b.cat_parent =18

MySQL said: Documentation
#1142 - SELECT command denied to user 'username'@'localhost' for table 'adv_table'

Could this be what is causing the erroneous results? Or is it just because I'm doing it through phpmyadmin?

bradgrafelman

showman13 wrote:
Could this be what is causing the erroneous results? Or is it just because I'm doing it through phpmyadmin?

Well that depends, is phpMyAdmin using the same credentials that your PHP script is using? If so, your problem should be obvious. If not, then you should be using the same credentials as your PHP script so that you can accurately diagnose the problem.

showman13

While I know that assuming is a bad thing to do, I do assume that the credentials are the same. I've never had an issue before with this. PHPmyAdmin is provided by my hosting company on my dedicated server, and I test just about every query that gives me any issues. Generally I can find the issue immediately by doing this.

But, although you said it should be obvious, it isn't the least bit obvious to me.

Thanks for the debugging link.

bradgrafelman

There's no assuming needed... PMA clearly displays which username it's using, and your PHP script must specify what username and password to use when connecting, so it's a simple matter of comparing the two..

As for the problem, the error message is quite clear.. That user doesn't have the necessary permissions to execute a SELECT query on that table.

showman13

showman13;10971304 wrote:
Added Info...

When I try to run the last query directly in PHPmyAdmin, using a cat_id that has multiple sub cats, and they all have ads listed under them :

this is the error result I get

SQL query: Documentation

SELECT count( a.adv_id )
FROM a.adv_table, b.cat_table
WHERE a.cat_id = b.cat_id
AND b.cat_parent =18

MySQL said: Documentation
#1142 - SELECT command denied to user 'username'@'localhost' for table 'adv_table'

Could this be what is causing the erroneous results? Or is it just because I'm doing it through phpmyadmin?

Just FYI...

The error was in fact in the syntax of the query.

this one actually works:

SELECT count( a.adv_id )
FROM adv_table a, cat_table c
WHERE a.cat_id = c.cat_id
AND c.cat_parent =18

Strange thing is that even my server support staff didn't pick up on it when I talked with them last night.

I guess using the correct Syntax would be a good first step to having a query work.

Thanks for your direction.

Douglas

bradgrafelman

Both are semantically correct, they just mean two different things (e.g. one query attempts to access tables from databases "a" and "b" while the other accesses both tables from the presently selected DB and aliases them as "a" and "b" respectively).