在php数组中搜索元素

Question

I've got

• a users table named "members"
• a rooms table named "rooms"
• a table that associates the user id to the ids of the rooms "membersRooms"

I should write a loop that prints a dropdown for each user with all the rooms, but that adds the attribute "selected" to rooms associated with the user

What's wrong with this loop?

$members = mysql_query("SELECT * FROM members ");
$rooms = mysql_query("SELECT * FROM rooms");

while($member = mysql_fetch_array($members)){ 
    echo("<select>");
    $roomsOfUser = mysql_query("SELECT roomID FROM membersRooms WHERE userID=".$member["id"]);
    $cuArray = mysql_fetch_array($roomsOfUser); 

    while($room = mysql_fetch_array($rooms)){
        if(in_array($room["id"],$cuArray,true))
            echo("<option selected='selected'>".$room["roomName"]."</option>");
        else
            echo("<option>".$class["roomName"]."</option>");
    }
    echo("</select>");
}

Answer 1

To make this a little easier on you, you could try utilizing left and right joins on your database. This would significantly reduce your server load and still allow you to do the same functionality.

I believe, if I'm reading your database structure right, that you'ld want something along the lines of:

 SELECT members.id as memberID, rooms.id as roomID, rooms.roomName, membersRooms.roomID as memberRoom
 FROM members
 LEFT JOIN membersRooms
 ON members.id = membersRooms.userID
 RIGHT JOIN rooms
 ON membersRooms.roomID = rooms.id

Then in PHP you should be able to just keep track of when your memberID changes, and when it does, start a new select. If I didn't totally bungle that SQL (which I might have) then the resulting rows should look something like:

memberID    |   roomID  |  roomName  |  memberRoom
    1             1          foo             1
    1             2          bar             1
    2             1          foo             1
    2             2          bar             1

So on your loop iteration you would use roomID and roomName to build your select, and if RoomID matched memberRoom then you would select that row.