[UPHPU] adding a count to an MySQL query

[Randy Moller]

Wade; Try something like this using a join table, and avoid including 
fields in your select that would produce a many -> 1 condition (such as 
your user nickname field). btw, "join" in mysql defaults to "inner join" 
in case you're wondering why i didn't specify.

SELECT t.num_users, e.even_id, e.even_title, e.even_status, e.even_type, 
UNIX_TIMESTAMP(e.even_start) AS `start`,
UNIX_TIMESTAMP(e.even_end) AS `end`, UNIX_TIMESTAMP(e.even_modified) AS 
`modified`
FROM dev_cscca.events e
JOIN dev_cscca_cms.users u ON e.even_modifiedby=u.user_id
JOIN (
SELECT COUNT(*) AS `num_users` FROM
dev_cscca_cms.users u2 WHERE u2.even_id = e.even_id
) AS t
WHERE e.even_status IN(1,8)
ORDER BY e.even_title ASC

Hope that helps.

Randy

Wade Preston Shearer wrote:
> I am stuck on an MySQL query and hoping that someone can help me out.  
> I have a table that contains events. I have another many-to-many table  
> that lists each event that a user is registered for. I want to  
> retrieve all of the events with a count of how many users are  
> registered for each.
>
> This query…
>
> SELECT even.even_id, even.even_title, even.even_status, even.even_type,
> 	UNIX_TIMESTAMP(even.even_start) as start,  
> UNIX_TIMESTAMP(even.even_end) as end,
> 	UNIX_TIMESTAMP(even.even_modified) as modified, user.user_nickname
> FROM dev_cscca.events even
> 	INNER JOIN dev_cscca_cms.users user
> 		ON even.even_modifiedby=user.user_id
> WHERE even.even_status in(1,8)
> ORDER BY even.even_title asc
>
>
> …returns all of the events. I am not trying to enhance the query so  
> that it includes the count of how many users are registered (for each  
> event).
>
> This…
>
> SELECT even.even_id, even.even_title, even.even_status, even.even_type,
> 	UNIX_TIMESTAMP(even.even_start) as start,  
> UNIX_TIMESTAMP(even.even_end) as end,
> 	UNIX_TIMESTAMP(even.even_modified) as modified, user.user_nickname, (
> 		SELECT COUNT(meev.meev_id)
> 		FROM dev_cscca.members_events meev
> 		WHERE meev.meev_date_scheduled is not NULL
> 		) as count
> FROM dev_cscca.events even
> 	INNER JOIN dev_cscca_cms.users user
> 		ON even.even_modifiedby=user.user_id
> WHERE even.even_status in(1,8)
> ORDER BY even.even_title asc
>
>
> …returns a count of how many users are registered, but it counts how  
> many users are registered total, not how many for each of event. I  
> have tried various joins, but have not been able to find the proper  
> syntax for narrowing this down.
>
> _______________________________________________
>
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.430 / Virus Database: 270.14.119/2585 - Release Date: 12/24/09 08:11:00
>
>