LEFT JOIN MySQL和PHP

Question

I want to get some statitics out for my adminpanel. I have two tables named users and cms_prosjekt. I want to count how many projects that have the same attribute as users.

Each user have a motto that is connected to a project. For example: Motto is spirit and the project code is spirit. It won't return anything. I have two users with the same motto as code in a project.

 <?php 

   $result = mysql_query("SELECT
      users.id,

      COUNT(users.motto) AS count
    FROM
      users
      LEFT JOIN cms_prosjekt ON
        users.motto=cms_prosjekt.code
    GROUP BY
      users.motto");
 $num_rows = mysql_num_rows($result);

  echo "$num_rows";

  ?>

Answer 1

The query should be count(*) and group by user.motto

 "SELECT
    user.motto
  , COUNT( * ) AS count
 FROM  users
 LEFT JOIN cms_prosjekt ON  users.motto=cms_prosjekt.code
 GROUP BY  users.motto"