Display Event Participants MySQL

[drewpark88]

Hey Guys, I have a quick question for you. I display a new event every day that allows users to attend that event. The event id and user id are added to an event participants table. I have 3 tables in my DB: "userinfo" which holds stuff like id, Name, etc for the users. Then I have "events" which holds stuff like id, Title, etc for events. The last table is "event_participants" which holds three fields participantId, eventId, userId.

What I would like to do is display all users who are attending that daily event. I made a function which yes, kinda sucks and doesn't work yet..but might help you to see what I am getting at. I don't understand, do I add all the userId where eventId to an array? I am new to this and a bit confused.

Any help will be greatly appreciated! : )

public function EventParticipants(){


   $condb = new DBCON();
   $condb->startcon(); 


   $todays_date = date("Y-m-d");

$event_id = mysql_query("SELECT id FROM events WHERE Event_Date = '$todays_date'");

$participants = mysql_query("SELECT * FROM event_participants WHERE eventId = '$event_id'");
	
$user_id = mysql_query("SELECT 'userId' FROM event_participants WHERE eventId = '$participants'");
	
	
     $query = "SELECT * FROM userinfo WHERE id = '$user_id'";
     $result = mysql_query($query);


				while ($row = mysql_fetch_assoc($result)){ 

				echo "<h2>".$row['Name']."</h2>";

                                }

}

[burgercho]

The query for attaining the participants returns a result, not just the id, a couple of fixes that would help:

$participants = mysql_query("SELECT userId FROM event_participants WHERE eventId = '$event_id'");
while ($id = mysql_fetch_row($participants)){
    $query = "SELECT * FROM userinfo WHERE id = '".$id[0]."'";
    $result = mysql_query($query);
    while ($row = mysql_fetch_assoc($result)) { 
    echo "<h2>".$row['Name']."</h2>";
    }
}

Although, if you're willing to tackle it, a MySQL JOIN statement would probably be more appropriate.

[drewpark88]

Thanks burgercho, still not working. I might try the join, appreciate your help!

The query for attaining the participants returns a result, not just the id, a couple of fixes that would help:

$participants = mysql_query("SELECT userId FROM event_participants WHERE eventId = '$event_id'");
while ($id = mysql_fetch_row($participants)){
    $query = "SELECT * FROM userinfo WHERE id = '".$id[0]."'";
    $result = mysql_query($query);
    while ($row = mysql_fetch_assoc($result)) { 
    echo "<h2>".$row['Name']."</h2>";
    }
}

Although, if you're willing to tackle it, a MySQL JOIN statement would probably be more appropriate.

[burgercho]

You need to get the result set from the first query also

$res = mysql_fetch_row(mysql_query("SELECT id FROM events WHERE Event_Date = '$todays_date'"));
$event_id = $res ? $res[0] : NULL;

Also, what are you getting as output?

[drewpark88]

You are awesome!!! That fixed my problem! Thank you sooo much :)

You need to get the result set from the first query also

$res = mysql_fetch_row(mysql_query("SELECT id FROM events WHERE Event_Date = '$todays_date'"));
$event_id = $res ? $res[0] : NULL;

Also, what are you getting as output?