How do I upload a file within a database with 1 or more relations

Question

I created a form where people can upload their personal information and resume. Now my database has three tables called person, address and cv. address and cv both has a relation with person. When I fill in the form and look in my database the person > address is correct however it doesnt save the resume(path) in the database at all. Also the cv_id is shown in person as a NULL. Can anyone help me fix this problem?

My upload.php (file)

connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

if(isset($_POST['submit']))
{
  $filetmp = $_FILES["cv"]["tmp_name"];
  $filename = $_FILES["cv"]["name"];
  $filetype = $_FILES["cv"]["type"];
  $filepath = "files/".$filename;

  move_uploaded_file($filetmp,$filepath);

  $sql = "INSERT INTO cv (cv_name,cv_path,cv_type) VALUES ('$filename','$filepath','$filetype')";
  $result = mysql_query($sql);
}
?>

Here is my function.php which only uploads person and address information. My form has two actions to function.php and upload.php

connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

// ADDRESS APPEND - PREPARE SQL STATEMENT AND BIND PARAMS
$stmt = $conn->prepare("INSERT INTO address (address_street, address_housenumber, 
                                         address_zipcode, address_city, address_state)
                    VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("sssss", $straat, $huisnummer, $postcode, $stad, $provincie);

$straat = htmlspecialchars($_POST['straat']);
$huisnummer = htmlspecialchars($_POST['huisnummer']);
$postcode = htmlspecialchars($_POST['postcode']);
$stad = htmlspecialchars($_POST['stad']);
$provincie = htmlspecialchars($_POST['provincie']);

// EXECUTE STATEMENT
$result = $stmt->execute();    
if ($result === FALSE) {
die("Error: " . $stmt->error);
}

// CAPTURE LAST INSERTED address_id
$last_id = $conn->insert_id;

// PERSON APPEND - PREPARE SQL STATEMENT AND BIND PARAMS
$stmt = $conn->prepare("INSERT INTO person (person_firstname, person_lastname, 
                                        person_email, person_phonenumber,
                                        person_cv, person_address)
                     VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param("sssssi", $firstname, $lastname, $email, $telephone, $cv, $last_id);

$firstname = htmlspecialchars($_POST['firstname']);
$lastname = htmlspecialchars($_POST['lastname']);
$email = htmlspecialchars($_POST['email']);
$telephone = htmlspecialchars($_POST['telephone']);


// EXECUTE STATEMENT
$result = $stmt->execute();    
if ($result === TRUE) {
$URL="http://localhost:8080/Website/bedankt.php";  
header ("Location: $URL");  
} else {
echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

My form:

action.php:

Database:

Maybe there is a way my file upload can be in my function.php.

user4029967 · Accepted Answer

Your error is in upload.php file on line 24.

Change this:

$result = mysql_query($sql);

to this:

$result = mysqli_query($conn, $sql);

Keep in mind mysql extension is deprecated and it was removed in recent php ver. 7. You shouldn't be using it in your code.

EDIT. Your current db logic is flawed but who am I to judge you? If you want to do it as you have it now you have to do the following changes.

In function.php modify the INSERT INTO and remove $cv from it.

$stmt = $conn->prepare("INSERT INTO person (person_firstname, person_lastname, person_email, person_phonenumber, person_address) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("ssssi", $firstname, $lastname, $email, $telephone, $last_id);

btw in your original code it's sssssi, this is also an error as your id is probably an integer just like the address id.

In upload.php before you close the tag add following line:



$last_id = $conn->insert_id;


Now update the table "person" putting that id into person_cv field.

EDIT2.
Code formatting fails as always...

EDIT3.
I thought you will move it around a little bit and try it yourself. Here is another solution, even simpler.

Look at your files. In action.php change

include('function.php');
include('upload.php');


to

include('upload.php');  
include('functie.php');


Now upload.php gets processed first which means that at the end of the upload.php file you can add $cv = $conn->insert_id; (right before php closing tag).

Now the variable $cv which you use in function.php exists therefore you can use it and your original insert query will work without inserting NULL values into your db:

$stmt = $conn->prepare("INSERT INTO person (person_firstname, person_lastname, person_email, person_phonenumber, person_cv, person_address) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param("sssssi", $firstname, $lastname, $email, $telephone, $cv, $last_id);

How do I upload a file within a database with 1 or more relations

Answers (1)

Related Questions