How to output and edit columns of a many-to-many table through <select\>.
I have three tables : filial_work , department_work, contact_work .
In them, I managed to output only the already assigned id_filial(id_department) , but I need everything to be output and they can be changed.
filial_work :
id_filial | filial
department_work :
id_department | department | department_email
contact_work:
id_contact | id_filial | id_department | full_name | email_user | work_number | tel_number
script for outputting a string by ip from the contact_work table
<?php
include "../src/php/connect_db_tel-sprav-contact.php";
$id_contact = $_GET['id'];
$result = mysqli_query($induction, "SELECT * FROM `contact_work` WHERE `id_contact` = '$id_contact'");
$result = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html lang="ru">
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="/src/css/Create_contact_style.css">
<title>Editing a contact</title></head>
<body>
<header><img src="/src/img/01-logo.webp" alt="Centrospas-Yugoria logo">
<h1 class="logo">Editing a contact</h1></header>
<nav></nav>
<main></main>
<div class="visual_create_new_user">
<form action="/src/php/script_change_contact.php " method="post"> //script for outputting a string by ip from the
contact_work table<input type="hidden" name="id_contact" value="<?= $result['id_contact'] ?>">
<p>
<button onclick="location.href='/src/TEST_Admin_Contact.php'">Back to contacts</button>
</p>
<p>Change Branch: <select>
<option><?= $result['id_filial'] ?></option>
</select></p>
<p>Change Department: <select>
<option><?= $result['id_department'] ?></option>
</select></p>
<p>Change the Position: <input type="text" name="_post" value="<?= $result['post'] ?>"></p>
<p>Change your FULL NAME: <input required type="text" name="full_name" value="<?= $result['full_name'] ?>"></p>
<p>Change Email: <input type="text" name="email_user" value="<?= $result['email_user'] ?>"></p>
<p>Change the work phone number: <input type="text" name="work_number" value="<?= $result['work_number'] ?>"></p>
<p>Change personal phone number: <input type="text" name="tel_number" value="<?= $result['tel_number'] ?>"></p>
<p>
<button type="submit">Change</button>
</p>
</form>
</div>
</body>
</html>
In order to populate a dropdown box with values from a linked table, you need to select them from this table.
Hence your code could be like this
// get your record
$id_contact = $_GET['id'] ?? 0;
$sql = "SELECT * FROM `contact_work` WHERE `id_contact` = ?";
$contact = $db->execute_query($sql, [$id_contact])->fetch_assoc();
// get values from a linked table
$sql = "SELECT id, department FROM `department_work`";
$departments = $db->query($sql)->fetch_all(MYSQLI_ASSOC);
And then you can create your dropdown like this.
<p>Change Department:
<select name = "id_department">
<?php foreach ($departments as $dep): ?>
<?php $selected = ($dep['id'] === $contact['id_department']) ? "selected" : "" ?>
<option value="<?= htmlspecialchars($dep['id']) ?" <?= $selected ?>>
<?= htmlspecialchars($dep['department']) ?>
</option>
<?php endforeach ?>
</select>
</p>
Note the changes I made