Facing the problem of uploading the member ID while uploading the data to multiple tables through a single form/click
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?
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'];
}
}
}
?>
Following are the Tables-
Solution
I have found the answer by suggesting @Adyson. With the help of the demo, I have found that I have to put mysqli_insert_id($conn) in just below the member_profile inserting the query.
Following are my final codes that work for my application-
<?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);
$mid = mysqli_insert_id($conn);
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, qualification_member_id, date_upload) VALUES (?, ?, ?, ?)";
$stmt5 = $conn->prepare($sqlQua);
// Insert each qualification and date into the table
for ($i = 0; $i < count($quas); $i++) {
$stmt5->bind_param("ssss", $upload_name, $quas[$i], $mid, $date);
$stmt5->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);
$noImage_mid = mysqli_insert_id($conn);
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'];
}
}
}
?>
- Laravel Model Factories with Tests
- Filter out rows of a 2d array if the row is not found in another 2d array
- Why does this PDO statement silently fail?
- Laravel post directly to resource index
- Create json-encoded array from a PDO query result column
- JSON encode a column of a 2d array
- JSON encode the first column of a 2d array
- need to json_encode a PHP array in a particular way
- Amazon S3 - How to fix 'The request signature we calculated does not match the signature' error?
- How to get slugs and names of all order statuses in WooCommerce?
- MySQL database design for image options relationships
- CakePHP with Stored Procedure
- In Payum How can I check "correctly" the status "pending" in the payment?
- How to check if user logged in or no Laravel
- How to validate array in Laravel?
- Read a file backwards line by line using fseek
- 12-digit regex for phone number
- How do I get an object's unqualified (short) class name?
- PHP Call to undefined function
- Using string path to delete element from array
- Update data on a page without refreshing
- Extract year with an optional plus right after from a string using PHP regex
- Unexpected = error in if statement
- Is there any code generator for Zend Framework?
- What's the net::ERR_HTTP2_PROTOCOL_ERROR about?
- Parsing data from blade form to controller using request
- how to render a view and save the content in a file in Laravel
- CakePHP: How do I access the Auth->allow action array?
- Add wordpress custom post type data to an external db
- remove empty spaces between jquery autocomplete result list