Listing of projects with document count

[rmeeuwsen]

I'm trying to build a page that contains a listing of all available projects. Every project can involve several documents. Projects and documents are stored in separate tables:

 

tbl_projects

-----------------

project_id

name description

 

tbl_documents

---------------------

document_id

project_id

name

path

 

How can I generate the listing so that it shows all projects and at every project a count of available documents? I need help on the SQL statement and php code...

 

Thanks in advance!

 

Robin Meeuwsen

[MatthewJ]

Maybe something like

 

SELECT tbl_projects.*, count(tbl_documents.document_id) FROM tbl_projects, tbl_documents WHERE tbl_projects.project_id = tbl_documents.project_id

[rmeeuwsen]

Thanks for you response! I tried it but MySQL returns the following error:

 

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

 

How to use GROUP BY so that the query works after all?

 

Thanks!

 

Robin Meeuwsen

[rmeeuwsen]

This doesn't do the trick  :-\ Here is wat I have:

 

SQL:

 

SELECT * FROM tbl_projects

 

PHP:

 

<?php do { ?>

  <p><?php echo $row_rsProjects['project_id']; ?> - <?php echo $row_rsProjects['name']; ?> - x documents related to this project</p>

<?php } while ($row_rsProjects = mysql_fetch_assoc($rsProjects)); ?>

 

This gives me a listing of all projects. How can I now add the count of documents per project?

[rmeeuwsen]

I'm halfway there I think:

 

SELECT *, COUNT( doc_project_id ) AS proj_doc

FROM tbl_projects

LEFT JOIN tbl_documents ON tbl_projects.project_id = tbl_documents.doc_proj_id

GROUP BY tbl_projects.project_id

 

This gives me the listing I need but only the 1st and the last record show the project_id. The document count is working though.

 

Any ideas?