Vegard Berg Vegard Berg - 2 months ago 7
MySQL Question

LEFT JOIN MySQL and PHP

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

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"
Comments