I have a database with a many-to-many relationship with three tables:
+-----------+ +----------+ +----------+
|doorUser | |doorAccess| | doors |
+-----------+ +----------+ +----------+
| userID | | userID | | doorNum |
| lname | | doorNum | | doorName |
| fname | +----------+ +----------+
| username |
| accessNum |
+-----------+
the doorAccess table is the intermediate table that connects the doorUser and the doors tables. The thing I want to do is to insert into both the doorUser table as well as the doorAccess table, so that each user can have access to any amount of the doors. I have managed to get my PHP to where I can insert into the doorUser table, but the doorAccess table is proving to be a lot more difficult. The below is the snippet from the submits and posts.
if (isset($_POST["submit"])) {
if( (isset($_POST["fname"]) && $_POST["fname"] !== "") && (isset($_POST["lname"]) && $_POST["lname"] !== "") &&(isset($_POST["username"]) && $_POST["username"] !== "") &&(isset($_POST["accessNum"]) && $_POST["accessNum"] !== "") ) {
$query = "INSERT INTO doorUser ";
$query .= "(fname,lname,username,accessNum) ";
$query .= "values('".$_POST["fname"]."', '".$_POST["lname"]."', '".$_POST["username"]."', '".$_POST["accessNum"]."')";
$query3 = "SELECT doorNum, doorName ";
$query3 .= "FROM doors ";
$result3 = $mysqli->query($query3);
while ($row3 = $result3->fetch_assoc()) {
$doorNum = $row3["doorNum"];
$doorName = $row3["doorName"];
if( (isset($row3["doorName"]) && $row3["doorName"] !== "")){
$query3 = "INSERT INTO doorAccess ";
$query3 .= "(userID, doorNum) ";
$query3 .= "values('".$_POST[LAST_INSERT_ID()]."', '".$_POST["doorNum"]."')"
}
}
And the below is the snippet from the forms.
echo "<p><form action = 'addUser.php?id={$ID}' method='post'>";
echo "<p><input type = 'text' name = 'fname' placeholder = 'First Name' /></p>";
echo "<p><input type = 'text' name = 'lname' placeholder = 'Last Name' /></p>";
echo "<p><input type = 'text' name = 'username' placeholder = 'username' /></p>";
echo "<p><input type = 'text' name = 'accessNum' placeholder = 'password' /></p>";
$query2 = "SELECT doorNum ";
$query2 .= "FROM doors ";
$result2 = $mysqli->query($query2);
if ($result2 && $result2->num_rows > 0) {
while ($row2 = $result2->fetch_assoc()) {
$doorNum = $row2["doorNum"];
echo "<p><input type = 'checkbox' name = 'doorName' value = '".$row2["doorNum"]."' /> Door $doorNum</p>";
}
}
echo "<p><input type = 'submit' name = 'submit' value = 'Add Person' />";
echo "</form>";
The door table is able to be added to or deleted from in another portion, so I need to be able to dynamically generate however many checkboxes for the doors, but then I also need a way to link which users has access to which doors. The checkboxes generate as they're supposed to, but I am having an issue with inserting the userID and the doors into the intermediate table.
I checked and I am able to insert into the doorUser table with this code, but the issues is somewhere in the doorAccess insert.
Any help would be greatly appreciated!
Aucun commentaire:
Enregistrer un commentaire