CREATE TABLE postcodes (
postcode VARCHAR(6) NOT NULL,
adres VARCHAR(30) NOT NULL,
woonplaats VARCHAR(28) NOT NULL,
PRIMARY KEY(postcode)
);
CREATE TABLE leden (
lidnummer int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
naam VARCHAR(20)NOT NULL,
voornaam VARCHAR(20) NOT NULL,
huisnummer VARCHAR(15) NOT NULL,
postcode VARCHAR(6) NOT NULL,
PRIMARY KEY (lidnummer),
FOREIGN KEY (postcode) REFERENCES postcodes(postcode)
);
CREATE TABLE emails (
email VARCHAR(40) NOT NULL,
lidnummer int(10) UNSIGNED NOT NULL,
PRIMARY KEY (email),
FOREIGN KEY(lidnummer) REFERENCES leden(lidnummer)
);
CREATE TABLE telefoonnummers (
telefoonnummer VARCHAR(13) NOT NULL,
lidnummer int(10) UNSIGNED NOT NULL,
PRIMARY KEY (telefoonnummer),
FOREIGN KEY(lidnummer) REFERENCES leden(lidnummer)
);
INSERT INTO postcodes VALUES ('1234AB', 'SQ-Laan', 'PHPetten');
INSERT INTO leden (naam, voornaam, huisnummer, postcode) VALUES ('Bakker', 'Dave', '14b', '1234AB');
INSERT INTO emails VALUES ('Dave@mail.com', LAST_INSERT_ID());
INSERT INTO telefoonnummers VALUES ('0645781245', LAST_INSERT_ID());
INSERT INTO telefoonnummers VALUES ('0654351657', LAST_INSERT_ID());
INSERT INTO postcodes VALUES ('9764CD', 'Spechtlaan', 'Amsterdam');
INSERT INTO leden (naam, voornaam, huisnummer, postcode) VALUES ('Beek', 'John', '26', '9764CD');
INSERT INTO emails VALUES ('John@mail.com', LAST_INSERT_ID());
INSERT INTO emails VALUES ('Johnwerk@mail.com', LAST_INSERT_ID());
INSERT INTO telefoonnummers VALUES ('0405784216', LAST_INSERT_ID());
INSERT INTO postcodes VALUES ('6542GT', 'Esther de Boer van Rijkpark', 'Heerhugowaard');
INSERT INTO leden (naam, voornaam, huisnummer, postcode) VALUES ('Kocken', 'Sjoerd', '1-hoog', '6542GT');
INSERT INTO emails VALUES ('Sjoerd@mail.com', LAST_INSERT_ID());
INSERT INTO emails VALUES ('Sjoerdprive@mail.com', LAST_INSERT_ID());
INSERT INTO telefoonnummers VALUES ('+311085467985', LAST_INSERT_ID());
INSERT INTO telefoonnummers VALUES ('+31645785426', LAST_INSERT_ID());
INSERT INTO telefoonnummers VALUES ('+317258546546', LAST_INSERT_ID());
<?php
if (isset($_POST['update_member'])) {
$mysqli->autocommit(false);
$stmt = $mysqli->prepare("UPDATE leden SET naam=?, voornaam=?, huisnummer=?, postcode=? WHERE lidnummer=?");
$stmt->bind_param('ssssi', $achternaam, $voornaam, $huisnummer, $postcode, $lidnummer);
$stmt->execute();
$affected += $stmt->affected_rows;
deleteEmails($mysqli, $lidnummer);
deleteTelnrs($mysqli, $lidnummer);
insertEmails($mysqli, $emails, $lidnummer);
insertTelnrs($mysqli, $telnrs, $lidnummer);
$mysqli->commit();
// header("Location: index.php");
$stmt->close();
}
function insertEmails($conn, $input, $lidnummer)
{
if ($input == "") { return; }
$stmt = $conn->prepare('INSERT INTO emails VALUES (?, ?)');
$contacts_arr = explode('\r\n', $input);
foreach($contacts_arr as $contact) {
if ($contact == "") {
continue;
}
$stmt->bind_param('si', $contact, $lidnummer);
$stmt->execute();
}
$stmt->close();
}
function insertTelnrs($conn, $input, $lidnummer)
{
if ($input == "") { return; }
$stmt = $conn->prepare('INSERT INTO telefoonnummers VALUES (?, ?)');
$contacts_arr = explode('\r\n', $input);
foreach($contacts_arr as $contact) {
if ($contact == "") {
continue;
}
$stmt->bind_param('si', $contact, $lidnummer);
$stmt->execute();
}
$stmt->close();
}
function deleteEmails($conn, $lidnummer)
{
$del_email_stmt = $conn->prepare("DELETE FROM emails WHERE lidnummer=?");
$del_email_stmt->bind_param('i', $lidnummer);
$del_email_stmt->execute();
$del_email_stmt->close();
}
function deleteTelnrs($conn, $lidnummer)
{
$del_tel_stmt = $conn->prepare("DELETE FROM telefoonnummers WHERE lidnummer=?");
$del_tel_stmt->bind_param('i', $lidnummer);
$del_tel_stmt->execute();
$del_tel_stmt->close();
}
if ($_GET['lidnummer']) {
$lidnummer = $_GET['lidnummer'];
$stmt = $conn->prepare("SELECT * FROM leden WHERE lidnummer=?");
$stmt->bind_param('i', $lidnummer);
$stmt->execute();
$result = $stmt->get_result()->fetch_array(MYSQLI_ASSOC);
$stmt->close();
$postcode = htmlspecialchars($result['postcode']);
$stmt_postcode = $conn->prepare("SELECT * FROM postcodes WHERE postcode=?");
$stmt_postcode->bind_param('s', $postcode);
$stmt_postcode->execute();
$postcode_result = $stmt_postcode->get_result()->fetch_array(MYSQLI_ASSOC);
$stmt_postcode->close();
$stmt_email = $conn->prepare("SELECT * FROM emails WHERE lidnummer=?");
$stmt_email->bind_param('i', $lidnummer);
$stmt_email->execute();
$email_result = $stmt_email->get_result();
$num_emails = $email_result->num_rows;
$stmt_email->close();
$stmt_tel = $conn->prepare("SELECT * FROM telefoonnummers WHERE lidnummer=?");
$stmt_tel->bind_param('i', $lidnummer);
$stmt_tel->execute();
$tel_result = $stmt_tel->get_result();
$num_tels = $tel_result->num_rows;
$stmt_tel->close();
?>
<div class="leden-form">
<h3>Update lid</h3>
<form action="<?php $_SERVER["PHP_SELF"]; ?>" method="POST">
<label for="lidnummer">
Lidnummer:
<input type="text" name="lidnummer" value="<?php echo htmlspecialchars($result['lidnummer']); ?>" readonly>
</label>
<label for="naam">
Voornaam:
<input type="text" name="voornaam" value="<?php echo htmlspecialchars($result['voornaam']); ?>" required>
</label>
<label for="achternaam">
Achternaam:
<input type="text" name="achternaam" value="<?php echo htmlspecialchars($result['naam']); ?>"required>
</label>
<label for="huisnummer">
Huisnummer:
<input type="text" name="huisnummer" value="<?php echo htmlspecialchars($result['huisnummer']); ?>"required>
</label>
<label for="postcode">
Postcode:
<select name="postcode" required>
<option selected disabled value="<?php echo $postcode_result['postcode'] ?>">
<?php echo $postcode_result['postcode']; ?> - <?php echo $postcode_result['adres']; ?> - <?php echo $postcode_result['woonplaats']; ?>
</option>
<?php
selectPostcodeOptions($conn);
?>
</select>
</label>
<label for="emailadres">
E-mailadres(sen):
<textarea name="emailadres" cols="45" rows="4"><?php
for ($i=0; $i < $num_emails; $i++) {
$result = $email_result->fetch_assoc();
echo $result['email'] . "\r\n";
}
?></textarea>
</label>
<label for="telnr">
Telefoonummer(s):
<textarea name="telnr" cols="45" rows="4"><?php
for ($i=0; $i < $num_tels; $i++) {
$result = $tel_result->fetch_assoc();
echo $result['telefoonnummer'] . "\r\n";
}
?></textarea>
</label>
<button type="submit" name='update_member'>Update lid</button>
</form><br>
<a href="index.php">Cancel update</a>
</div>
<?php } ?>