Dropdown menu with values from DB

Question

I want to create a dropdown menu called "rooms" which shows me the available rooms stored in the database.

The index.php shows a table with some devices and the name of the room in which the device is stored. (SQL query: SELECT deviceID, deviceName, roomName FROM device LEFT JOIN room ON device.roomID = room.roomID) with roomID being a primary key in the room-table and device.roomID a foreign key in the device-table.

The user should be able to change the device's room, I did that with a link next to each device which redirects me to a form. In this form I want the dropdown menu to show the current room as default value, and all available rooms as options.

$sql1 = "SELECT roomname FROM device LEFT JOIN room ON device.roomID = room.roomID WHERE deviceID='$id'";
$sql7 = "Select distinct roomname, roomID from room";
require_once ('config.php');
$db_link = mysqli_connect (MYSQL_HOST, 
                           MYSQL_USER, 
                           MYSQL_PW, 
                           MYSQL_DATABASE);
        mysqli_set_charset($db_link, 'utf8');
     $db_erg = mysqli_query( $db_link, $sql1 );
     if ( ! $db_erg )
{
  die('Error: ' . mysqli_error($db_link));
}
$db_erg2 = mysqli_query( $db_link, $sql7);
echo 'Room:';
echo '</br>';
echo '</br>';
echo '<select name="room>';

while($row = mysqli_fetch_array($db_erg2, MYSQL_ASSOC)) 
{
  echo '<option value="' . $row['roomID'] .'"selected="selected">' . $row['roomname'] . '</option>';
}

while($row = mysqli_fetch_array($db_erg, MYSQL_ASSOC)) 
{
  echo '<option value="' . $row['roomID'] . '">' . $row['roomname'] . '</option>';
  

}

I added a button which creates a SQL query to update the device table with the new roomID. BUT the roomID is only passed into the query WHEN I change the room in the dropdown menu. If not, the sql-query does this: "UPDATE device blaabala SET device.roomID = ""; And a blank roomID leads to "Cannot add or update a child row: a foreign key constraint fails"

And I want it also to be passed when I DONT change the room (I have to mention here that the room will later contain boxes and when I change the boxes I dont want to have to change the room everytime as well.)

Hope anyone understands my problem since English ain't my mothertongue. Thanks in advance

Can you briefly ask what going wrong with you? is it only the arrayname showing in the dropdown? or anything else? — Abdul Moiz
– Abdul Moiz, Commented Apr 1, 2015 at 10:29
In the dropdown menu there are the available rooms from the database (distinct roomname from room). When I change the room in the menu, it is successfully updated, but when I don't, no value is sent to $_POST['room'] and I want the roomID to be sent there. — jojojojo
– jojojojo, Commented Apr 1, 2015 at 10:40

Abdul Moiz · Accepted Answer · 2015-04-01 10:46:28Z

1

<option value="<?=$row['roomID']?>"> <?=$row['roomname']?></option>

Query Error

Its because you have referential integrity enforced and you are violating it. You are trying to add or update a record using invalid foreign key(key that does not exist).

answered Apr 1, 2015 at 10:46

Abdul Moiz

1,3272 gold badges17 silver badges41 bronze badges

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

1 Comment

jojojojo Over a year ago

Ok thx, but why does it work when I change the value in the dropdown menu and doesn't when I don't change it?

Collectives™ on Stack Overflow

Dropdown menu with values from DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related