I'm creating a html form to insert informations and upload image files name into a two Mysql table at the same time. The php code that i'm using to register user and upload images to images folder works well, but i didnt get the correctly way to insert, at the same time, an ID from another table.
I found some questions about it but i didn't find a correctly way to solve this:
- How to insert multiple values into a table in php
- insert multiple rows via a php array into mysql
- Inserting multiple rows in a table using PHP
- How to insert multiple table rows into database using php
Below are the two tables that demonstrate what i'm doing:
users_tbl
userID | username
------------------
01 | john
02 | Marie
03 | Joseph
xx | xxxxx
------------------
And below are the table that call images_tbl that receive image name:
images_tbl
id | imageName
-------------------------
01 | my_pic1.jpg
02 | my_pic2.jpg
03 | my_pic3.jpg
04 | my_pic4.jpg
05 | img-1.jpg
06 | img-2.jpg
-------------------------
What i would like to do is insert one column into images_tbl that call userID (foreing key) of users_tbl, just to receive the recently created last userID number at the same time that i register and insert the uploaded image name into images_tbl table:
images_tbl
id | userID |imageName
-------------------------
01 | 01 |my_pic1.jpg
02 | 01 |my_pic2.jpg
03 | 01 |my_pic3.jpg
04 | 02 |img02.jpg
05 | 02 |img03.jpg
xx | xx |xxxxxxx.jpg
-------------------------
Based on the table above, the php code (insert.php) that i'm using to register an user into (user_tbl) table and, at the same time, insert images name into images_tbl table, is showed below:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare("INSERT INTO users_tbl (username) VALUES (?)");
$stmt->bind_param("s", $username);
// set parameters and execute
$firstname = $_POST['username'];
$stmt->execute();
echo "New records created successfully";
$stmt->close();
$conn->close();
?>
<?php
if(!empty($_FILES)){
// Include the database configuration file
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// File path configuration
$targetDir = "uploads/";
$fileName = $_FILES['file']['name'];
$targetFilePath = $targetDir.$fileName;
// Here is the part of this code that i'm not be able to fix this:
$lastUserID = "SELECT userID FROM users_tbl ORDER BY userID DESC LIMIT 1";
// Upload file to server
if(move_uploaded_file($_FILES['file']['tmp_name'], $targetFilePath)){
// Insert file information in the database - $fileName and $lastUserID
$insert = $db->query("INSERT INTO images_tbl (imageName, userID) VALUES ('".$fileName."','".$lastUserID."')");
}
}
?>
Finally, the html form code that i'm using to do this:
<form action="insert.php" method="post" enctype="multipart/form-data" >
<input type="text" name="username" id="username" />
<br>
<div class="dropzone"></div>
<input type="submit" name="submit" id="startUpload" value="ADD">
</form>
As the form above, i'm using Dropzone.js to upload image files and, as i said, the php code that i'm using, upload images files into to uploads folder and insert data into users_tbl but doesn't insert userID into userID column of images_tbl table.
How can i adaptate my php code insert.php to insert userID correctly into images_tbl table? Any tips?