CODEWITHSUNDEEP
phpmysqldatabasedrop-down-menu
jojojojo
jojojojo

Reputation: 81

Dropdown menu with values from DB

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

Upvotes: 0

Views: 140

Answers (1)

Abdul Moiz
Abdul Moiz

Reputation: 1327

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

  1. 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).

Upvotes: 1

Related Questions