CODEWITHSUNDEEP
phpsqlcount
user1632867
user1632867

Reputation: 11

Count attendees MYSQL/PHP

Level
I'm a beginner

I have two tables:

events
Details of all the events with a unique eventid per event.

users
Lists the details of each attendee with columns eventname1 and eventname2 containing the eventid of the events they are signed up for. (note: a specific eventid could be in either eventname() column e.g. event id 66 could appear in eventname1 for one user and eventname 2 for another)

What I would Like to Do
I would like to echo a table with the details of all my events AND include a column (# Attendees) per event which lists the number of users registered for that event.

What I have so far

   SELECT COUNT( * ) 
    FROM  `users` 
    WHERE  `eventname1` = ( 
    SELECT  `id` 
    FROM  `events` 
    WHERE id =e.g.23 )

I have been tring to use this in conjunction with a join but I'm not getting anywhere. Any suggestions would be greatly appreciated.

Thanks.

Upvotes: 0

Views: 225

Answers (2)

Rafee
Rafee

Reputation: 4078

Create 3 tables and store them seperatly and you can count it easily

events
event_id, event_name, event_desc, etc_column

users
user_id, username, etc_colun

attendess_table
attendd_id, user_id, event_id, 

select e.event_name, u.username
from attendess_table at
INNER JOIN users u ON u.user_id = at.user_id
INNER JOIN events e ON e.event_id = at.event_id
where u.user_id = 2

for counting

select count(user_id) from attendess_table where user_id = 2

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this

SELECT u.id,u.name,e.name, count(*)   
FROM   users u
left   outer join events e
on     u.id=e.id
group by u.id,u.name,e.name

Upvotes: 0

Related Questions