search for element in a php array

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>");
}

Throwing that select in the while loop makes DBAs punch kittens. — Mike Purcell
– Mike Purcell, Commented Dec 15, 2011 at 1:00
Are you sure that the data type of both are the same, and do you really need to ensure that? Does it work without strict type checking enabled for the in_array call? Try removing that third "true" parameter from the in_array call and see if it works, then. — Rylab
– Rylab, Commented Dec 15, 2011 at 1:15
no it doesn't work... and yes they are all int @DigitalPrecision I need a drop down for each user.. how should I do? — Gianluca
– Gianluca, Commented Dec 15, 2011 at 1:20
@Janky: What do you want to see in the list? If the code worked, you would only see room names in the dropdown that are linked to a user. — Mike Purcell
– Mike Purcell, Commented Dec 15, 2011 at 1:25
I want to see a drop down for each user with ALL the rooms as options, but the rooms which are associated to a user (the ones that are in the third table) must be selected — Gianluca
– Gianluca, Commented Dec 15, 2011 at 1:36

DaOgre · Accepted Answer · 2011-12-15 01:34:54Z

2

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.

answered Dec 15, 2011 at 1:34

DaOgre

2,10016 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Purcell Over a year ago

+1 for the join. If the schema allows it, get the data you need straight from the source at once, vs issuing a query for every iteration of the loop.

Ogun Meriçligil · Accepted Answer · 2011-12-15 01:38:56Z

0

$rooms query while is dead while runs once time in while

put this $rooms = mysql_query("SELECT * FROM rooms"); query line in first while

answered Dec 15, 2011 at 1:38

Ogun Meriçligil

663 bronze badges

1 Comment

Sam Dufel Over a year ago

While that would fix his problem, it would also make his script substantially less efficient

Sam Dufel · Accepted Answer · 2011-12-15 01:54:26Z

0

OK, so you need information from 3 tables - members, rooms, and membersRooms. The rows from members and membersRooms line up 1:1, so we can get both of those with 1 query.

This method will minimize the number of queries needed - if you ever see yourself querying the database in a loop, ask yourself if there's a better way.

$member_query = mysql_query("SELECT * FROM members LEFT JOIN membersRooms ON (members.id = membersRooms.userID)");

$room_query = mysql_query("SELECT * FROM rooms");
$rooms = array();
while ($room = mysql_fetch_assoc($room_query))
  $rooms[] = $room;

while ($member = mysql_fetch_assoc($member_query)) {
  echo '<select>';
  foreach($rooms as $room) {
    echo "<option value='{$room['roomID']}' ";
    if ($member['roomID'] == $room['id'])
      echo 'selected="selected"';
    echo ">{$room['roomName']}</option>";
  }
  echo '</select>';
}

It's worth noting that if members:rooms is a 1:many relation, you don't need to use a third table to join them - just add a roomId to members, and you're fine.

answered Dec 15, 2011 at 1:54

Sam Dufel

17.6k3 gold badges51 silver badges52 bronze badges

2 Comments

Gianluca Over a year ago

you wrote "The rows from members and membersRooms line up 1:1", maybe I have not explained well... one member can be associated to more than one rooms!

Gianluca Over a year ago

this prints one <select> for each record of $member_query... it's not correct cause it should prints one <select> for each member

Collectives™ on Stack Overflow

search for element in a php array

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related