Member Avatar for 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>";

                                }

}
  • 2 Contributors
  • 4 Replies
  • 189 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by drewpark88

Dani AI

Generated

Short summary: the root cause was treating query resources as raw IDs. correctly pointed out that the first mysqlquery returns a resource which you must fetch from, and that fixed the immediate bug for . A cleaner, more robust solution is a single JOIN that pulls user rows for today's event, and to use a modern DB API (PDO or mysqli) with prepared statements instead of the old mysql* calls.

Here is a compact PDO example (replace DSN/credentials and adjust column names as needed):

$pdo = new PDO('mysql:host=localhost;dbname=yourdb','user','pass',[
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$today = date('Y-m-d');
$sql = "
  SELECT u.Name
  FROM userinfo AS u
  JOIN event_participants AS ep ON ep.userId = u.id
  JOIN events AS e ON e.id = ep.eventId
  WHERE e.Event_Date = :today
";
$stmt = $pdo->prepare($sql);
$stmt->execute([':today' => $today]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo '<h2>' . htmlspecialchars($row['Name'], ENT_QUOTES, 'UTF-8') . '</h2>';
}

Troubleshooting and best practices: use LEFT JOIN if you need participants even when user rows are missing; guard against multiple events on the same date by filtering on a specific event id if necessary; index event_participants(eventId) and events(EventDate) for speed; validate that the event exists before querying participants; log or inspect exceptions if the result is empty. Finally, avoid mysql* (removed in modern PHP). Prepared statements prevent injection and make the code safer and clearer.

Recommended Answers

All 4 Replies

Member Avatar for 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.

Edited by burgercho because: Typo
Member Avatar for 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.

Member Avatar for 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?

Edited by burgercho because: n/a
Member Avatar for 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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.