I have a form with multiple fields to upload data and fetch the member’s data to the front end. In the front end, some sections have multiple points so I have created separate tables for them. To fetch data from different tables we need to create a foreign key and for this, I have made the member name a foreign key. In this, I have a problem if the backend user uploads data then he should not upload the same member name, for this, I need to make a foreign key a numeric number, but a numeric number foreign key is also a primary key so that it becomes auto_increment. But in tables like Qualification, Career, Achievement, and Issue, the member’s data will get separated because of auto_increment. So is there any way to make the foreign key numeric so that the foreign key will not get separated from the tables like Qualification, Career, Achievement, and Issue?
Following are my form-
function addQua() {
var container = document.getElementById('qua-container');
var newQua = document.createElement('div');
newQua.className = 'qua';
newQua.innerHTML = `
<input type="text" name="qua[]" required placeholder="Qualification">
<button type="button" class="btn-remove" onclick="removeQua(this)">Remove</button>
`;
container.appendChild(newQua);
}
function removeQua(button) {
var qua = button.parentElement;
qua.remove();
}
function addCar() {
var container = document.getElementById('car-container');
var newCar = document.createElement('div');
newCar.className = 'car';
newCar.innerHTML = `
<input type="text" name="car[]" required placeholder="Career">
<input type="text" name="date[]" required placeholder="Year">
<button type="button" class="btn-remove" onclick="removeCar(this)">Remove</button>
`;
container.appendChild(newCar);
}
function removeCar(button) {
var car = button.parentElement;
car.remove();
}
function addAchi() {
var container = document.getElementById('achi-container');
var newAchi = document.createElement('div');
newAchi.className = 'achi';
newAchi.innerHTML = `
<input type="text" name="achi[]" required placeholder="Achievement">
<button type="button" class="btn-remove" onclick="removeAchi(this)">Remove</button>
`;
container.appendChild(newAchi);
}
function removeAchi(button) {
var achi = button.parentElement;
achi.remove();
}
function addIss() {
var container = document.getElementById('iss-container');
var newIss = document.createElement('div');
newIss.className = 'iss';
newIss.innerHTML = `
<input type="text" name="iss[]" required placeholder="Issues">
<button type="button" class="btn-remove" onclick="removeIss(this)">Remove</button>
`;
container.appendChild(newIss);
}
function removeIss(button) {
var iss = button.parentElement;
iss.remove();
}<div class="member_form_main_container">
<div class="member_form_sub_container">
<form action="handle_member_form" method="post" id="form" enctype="multipart/form-data">
<div class="box">
<span>image</span>
<div class="input">
<input type="file" name="image" id="image">
</div>
</div>
<div class="box">
<span>full name</span>
<div class="input">
<input type="text" name="name" id="name">
</div>
</div>
<div class="box">
<span>place of birth</span>
<div class="input">
<input type="text" name="pob" id="pob">
</div>
</div>
<div class="box">
<span>date of birth</span>
<div class="input">
<input type="date" name="dob" id="dob">
</div>
</div>
<div class="box">
<span>father's name</span>
<div class="input">
<input type="text" name="father" id="father">
</div>
</div>
<div class="box">
<span>mother's name</span>
<div class="input">
<input type="text" name="mother" id="mother">
</div>
</div>
<div class="box qua">
<span>qualification</span>
<div class="input qua_input">
<div id="qua-container" class="formQua">
<div class="qua">
<input type="text" name="qua[]" required placeholder="Qualification">
</div>
</div>
<button type="button" class="btn-qua" onclick="addQua()">Add Qualification</button>
</div>
</div>
<div class="box car">
<span>career</span>
<div class="input car_input">
<div id="car-container" class="formCar">
<div class="car">
<input type="text" name="car[]" required placeholder="Career">
<input type="text" name="date[]" required placeholder="Year">
</div>
</div>
<button type="button" class="btn-car" onclick="addCar()">Add Career</button>
</div>
</div>
<div class="box achi">
<span>achievements</span>
<div class="input achi_input">
<div id="achi-container" class="formAchi">
<div class="achi">
<input type="text" name="achi[]" required placeholder="Achievements">
</div>
</div>
<button type="button" class="btn-achi" onclick="addAchi()">Add Achievements</button>
</div>
</div>
<div class="box iss">
<span>issues</span>
<div class="input iss_input">
<div id="iss-container" class="formIss">
<div class="iss">
<input type="text" name="iss[]" required placeholder="Issues">
</div>
</div>
<button type="button" class="btn-iss" onclick="addIss()">Add Issues</button>
</div>
</div>
<div class="button">
<button type="submit">submit</button>
</div>
</form>
</div>
</div>The following scripts are for handling the form-
<?php
include "../partials/dbconnect.php"
?>
<?php
if ($_SERVER['REQUEST_METHOD']=='POST') {
$upload_name = $_POST['name'];
$upload_pob = $_POST['pob'];
$upload_dob = $_POST['dob'];
$upload_father = $_POST['father'];
$upload_mother = $_POST['mother'];
date_default_timezone_set('Asia/Kolkata');
$date = date('Y-m-d H:i:s');
// File upload handling
if (isset($_FILES['image']) && $_FILES['image']['error'] === UPLOAD_ERR_OK) {
$fileTmpPath = $_FILES['image']['tmp_name'];
$fileName = $_FILES['image']['name'];
$fileNameCmps = explode(".", $fileName);
$fileExtension = strtolower(end($fileNameCmps));
// Allowed file extensions
$allowedfileExtensions = array('jpg', 'jpeg', 'png', 'avif', 'webp');
if (in_array($fileExtension, $allowedfileExtensions)) {
// Directory in which the file will be saved
$uploadFileDir = '../member_images/';
$dest_path = $uploadFileDir.$fileName;
if (move_uploaded_file($fileTmpPath, $dest_path)) {
$sql_member_profile = "INSERT INTO `member_profile` (`member_image`, `member_name`, `birth_place`, `birthday`, `father`, `mother`) VALUES ('$fileName', '$upload_name', '$upload_pob', '$upload_dob', '$upload_father', '$upload_mother')";
$result_member_profile = mysqli_query($conn, $sql_member_profile);
if ($result_member_profile) {
echo 'Image upload successfully';
}
// start
// Qualification start
// Get the qualification data
$quas = $_POST['qua'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlQua = "INSERT INTO member_qualifications (member_name, member_qualification, date_upload) VALUES (?, ?, ?)";
$stmt1 = $conn->prepare($sqlQua);
// Insert each qualification and date into the table
for ($i = 0; $i < count($quas); $i++) {
$stmt1->bind_param("sss", $upload_name, $quas[$i], $date);
$stmt1->execute();
}
// Qualification end
// Career start
// Get the career data
$cars = $_POST['car'];
$dates = $_POST['date'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlCar = "INSERT INTO member_careers (member_name, member_career_year, member_career_context, date_upload) VALUES (?, ?, ?, ?)";
$stmt2 = $conn->prepare($sqlCar);
// Insert each qualification and date into the table
for ($i = 0; $i < count($cars); $i++) {
$stmt2->bind_param("ssss", $upload_name, $dates[$i], $cars[$i], $date);
$stmt2->execute();
}
// Career end
// Achievements start
// Get the achievement data
$achis = $_POST['achi'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlAchi = "INSERT INTO member_achievements (member_name, member_achievement, date_upload) VALUES (?, ?, ?)";
$stmt3 = $conn->prepare($sqlAchi);
// Insert each achievement and date into the table
for ($i = 0; $i < count($achis); $i++) {
$stmt3->bind_param("sss", $upload_name, $achis[$i], $date);
$stmt3->execute();
}
// Achievements end
// Issues start
// Get the issue data
$isss = $_POST['iss'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlIss = "INSERT INTO member_issues (member_name, member_issue, date_upload) VALUES (?, ?, ?)";
$stmt4 = $conn->prepare($sqlIss);
// Insert each issue and date into the table
for ($i = 0; $i < count($isss); $i++) {
$stmt4->bind_param("sss", $upload_name, $isss[$i], $date);
$stmt4->execute();
}
// Issues end
// Close the statement and connection
$stmt1->close();
$stmt2->close();
$stmt3->close();
$stmt4->close();
echo "File is successfully uploaded.";
}else {
echo "There was some error moving the file to upload directory.";
}
}else {
echo "Upload failed. Allowed file types: " . implode(',', $allowedfileExtensions);
}
}else {
if (isset($_FILES['image']) && $_FILES['image']['error'] == UPLOAD_ERR_NO_FILE) {
$sql_member_profile = "INSERT INTO `member_profile` (`member_image`, `member_name`, `birth_place`, `birthday`, `father`, `mother`) VALUES ('user.png', '$upload_name', '$upload_pob', '$upload_dob', '$upload_father', '$upload_mother')";
$result_member_profile = mysqli_query($conn, $sql_member_profile);
if ($result_member_profile) {
echo 'Image upload successfully';
}
// start
// Qualification start
// Get the qualification data
$quas = $_POST['qua'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlQua = "INSERT INTO member_qualifications (member_name, member_qualification, date_upload) VALUES (?, ?, ?)";
$stmt1 = $conn->prepare($sqlQua);
// Insert each qualification and date into the table
for ($i = 0; $i < count($quas); $i++) {
$stmt1->bind_param("sss", $upload_name, $quas[$i], $date);
$stmt1->execute();
}
// Qualification end
// Career start
// Get the career data
$cars = $_POST['car'];
$dates = $_POST['date'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlCar = "INSERT INTO member_careers (member_name, member_career_year, member_career_context, date_upload) VALUES (?, ?, ?, ?)";
$stmt2 = $conn->prepare($sqlCar);
// Insert each qualification and date into the table
for ($i = 0; $i < count($cars); $i++) {
$stmt2->bind_param("ssss", $upload_name, $dates[$i], $cars[$i], $date);
$stmt2->execute();
}
// Career end
// Achievements start
// Get the achievement data
$achis = $_POST['achi'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlAchi = "INSERT INTO member_achievements (member_name, member_achievement, date_upload) VALUES (?, ?, ?)";
$stmt3 = $conn->prepare($sqlAchi);
// Insert each achievement and date into the table
for ($i = 0; $i < count($achis); $i++) {
$stmt3->bind_param("sss", $upload_name, $achis[$i], $date);
$stmt3->execute();
}
// Achievements end
// Issues start
// Get the issue data
$isss = $_POST['iss'];
// Prepare SQL statement to insert data into table "member_telephone"
$sqlIss = "INSERT INTO member_issues (member_name, member_issue, date_upload) VALUES (?, ?, ?)";
$stmt4 = $conn->prepare($sqlIss);
// Insert each issue and date into the table
for ($i = 0; $i < count($isss); $i++) {
$stmt4->bind_param("sss", $upload_name, $isss[$i], $date);
$stmt4->execute();
}
// Issues end
// Close the statement and connection
$stmt1->close();
$stmt2->close();
$stmt3->close();
$stmt4->close();
echo "File is successfully uploaded.";
}else {
echo "There was an error with the file upload. Error code: " . $_FILES['image']['error'];
}
}
}
?>