using COUNT with inner join

[jeff5656]

I am having a hard time getting the right syntax with count using 2 tables.

 

Here's what I have

$query = "SELECT COUNT(*) as num FROM people, constructs INNER JOIN people ON (people.id = constructs .pt_id) WHERE constructs .signstat = 'y' ";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

 

 

but I get: warning mysql_fetch_array(); supplied argument is not a valid mysql result resource

[Maq]

You have spaces:

constructs .pt_id

&

constructs .signstat

[jeff5656]

Sorry. No spaces in the actual code.  That happened when I was editing it here in phpfreaks.

[fenway]

Echo mysql_error().

[DavidAM]

You seem to have joined the people table twice. Try this:

 

$query = "SELECT COUNT(*) as num 
FROM constructs INNER JOIN people ON (people.id = constructs.pt_id) 
WHERE constructs.signstat = 'y' ";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

 

Although I don't recommend combining the fetch and query in a single call. The error message indicates that mysql_query() failed and with it embedded in the call to mysql_fetch_array() you can't catch it and take action. Also, you are trying to reference the field name in the $total_pages array, but you fetched it as a numeric array. I would rewrite the whole thing like this:

 

$query = "SELECT COUNT(*) as num 
FROM constructs INNER JOIN people ON (people.id = constructs.pt_id) 
WHERE constructs.signstat = 'y' ";
$res = mysql_query($query);
if ($res === false) {
  // In development output some stuff to help fix the problem
  // But don't leave this in production
  die(mysql_error() . PHP_EOL . 'SQL: ' . $query);
}
$total_pages = mysql_fetch_assoc($res);  // Use fetch_assoc() so we get the column names as array keys
$total_pages = $total_pages['num'];  // num is a string, it needs to be in quotes

 

EDIT: Apparently mysql_fetch_array() returns the data with both numeric indexes and column name indexes. I still recommend using mysql_fetch_assoc() specifically; but, to each his own.

[jeff5656]

Thank you for that very helpful reply.  I also appreciate all the comments you put in there to help me understand.  It works perfectly :-)