0

So i'm trying to fetch all of the user_id's from the users-events table where the event_id is equal to the passed in variable (let's say it's 2 for now.)

In the database there are currently 2 ID's registered to that event_id which are 1 and 2.

This code however only returns the first of these values. I feel like i need to incorporate the first query into the while loop but i dont know how to go about it.

Any help would be greatly appriciated!

function showregisteredplayers($id){

$eventq = mysql_fetch_assoc(mysql_query("SELECT user_id FROM `users-events` WHERE event_id = '".$id."'"));
$rosterq = mysql_query("SELECT username FROM `users` WHERE `user_id` = '".$eventq['user_id']."'");

  while($player = mysql_fetch_assoc($rosterq)){
    echo("
        <tr>
            <td>".$player['username']."</td>
        </tr>
    "); 
  }
}
Reece Mathieson
  • 51
  • 1
  • 7

2 Answers2

1

Use a sub query then kill your first one.

SELECT username FROM `users` WHERE `user_id` IN
(
  SELECT user_id FROM `users-events` WHERE event_id = 5
)

Rest is fine, you already are looping over the second result set so this should do. Unless you have a large number of records, there should not be any considerable performance degradation with the use of IN otherwise you can optimize the query.

5 is obviously just an example, use $id there correctly.

Community
  • 1
  • 1
Hanky Panky
  • 46,730
  • 8
  • 72
  • 95
1

Why not use a JOIN?

SELECT username 
FROM `users` AS u 
INNER JOIN `users-events` AS ue ON u.user_id = ue.user_id
WHERE event_id = ?

Several advices:

  1. Don't use mysql_ functions because are deprecated
  2. Use prepared queries, then you only need to loop through execute method, take a look to Example 3 in this link (example from php.net using mysqli)
Sal00m
  • 2,938
  • 3
  • 22
  • 33