Query returns only one result, more entries exists

[Vebut]

Hi,

I'm having some trouble with a query that is supposed to return all 'projects' connected to a specific 'customer', but only one is returned.

		SELECT
		projects.name AS name,
		projects.id AS id,
		SUM(reports.amount * reports.price) AS total,
		COUNT(DISTINCT reports.user) AS users
	FROM projects
		LEFT JOIN reports ON reports.project = projects.id
	WHERE projects.customer = :id
		ORDER BY projects.name ASC

 

There are several projects on each customer but this query only returns one of them. Also, the returning result does not seem to be affected by the ORDER BY clause as a project called "Port" is returned even though I have other projects that is supposed to be indexed before it (e.g "General").

 

What am I doing wrong?

// Thanks, Daniel

[sader]

maybe this is causes the problem?

WHERE projects.customer = :id

[Vebut]

Well, ':id' is a defined variable that can be used with the PDO library using PHP:

<?php
	$projects = $this->pdo->prepare("
	SELECT
		projects.name AS name,
		projects.id AS id,
		SUM(reports.amount * reports.price) AS total,
		COUNT(reports.user) AS users
	FROM projects
		LEFT JOIN reports ON reports.project = projects.id
	WHERE projects.customer = :id
		GROUP BY reports.user
		ORDER BY projects.name ASC
	");
	$projects->bindValue(':id', $id, PDO::PARAM_STR);
	$projects->execute();
?>

 

The query returns a project and the projects are allways connected to the customer I'm browsing, so that should not be the problem.

 

Edit: $id contains the customer id.

[PFMaBiSmAd]

When you execute the query directly against your database, using your favorite database management tool, does it produce the expected results?

 

I would guess that your presentation logic that is retrieving the results from the result set is probably at fault.

 

 

[Vebut]

No I've tried using phpmyadmin but online one result is returned. Can it be the LEFT JOIN clause that is causing this? The table 'reports' does not neccessary contain any records related to the projects.

[Vebut]

I solved it by grouping by project id:

GROUP BY projects.id