How to save file path in database and show path in detail page

Question

I am busy with a small project where people can fill in a form which will save the values into a database. Now everything is working fine exept for file upload path. In my database I got three tables person, address and cv (with relations) see picture:

Now I want the path to be www.test.nl/directory/filename so I can make a button with the link so when you press the button it will download the file. How ever on my detail page as you can see it is showing the cv_id and not the path. My file upload is in a seperate file as is my person and address upload see below:

address and person upload:

<?php
$servername = "localhost";
$username = "root";
$password = "usbw";
$dbname = "persons";

// CREATE A CONNECTION WITH THE DATABASE
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

// ADDRESS APPEND - PREPARE SQL STATEMENT AND BIND PARAMS
// ADRES TOEVOEGEN - BEREID SQL STATEMENT EN 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
// STATEMENT UITVOEREN
$result = $stmt->execute();    
if ($result === FALSE) {
die("Error: " . $stmt->error);
}

// CAPTURE LAST INSERTED address_id
// PAK DE LAATST INGEVOERDE address_id
$last_id = $conn->insert_id;

// PERSON APPEND - PREPARE SQL STATEMENT AND BIND PARAMS
// PERSOON TOEVOEGEN - BEREID SQL STATEMENT EN 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
// STATEMENT UITVOEREN
$result = $stmt->execute();    
if ($result === TRUE) {
$URL="http://localhost:8080/Website/bedankt.php";  
header ("Location: $URL");  
} else {
    echo "Error: " . $stmt->error;
}

// CLOSE CONNECTION AND STATEMENT
// SLUIT CONNECTIE EN STATEMENT
$stmt->close();
$conn->close();
?>

file upload.php

 <?php
 $servername = "localhost";
 $username = "root";
 $password = "usbw";
 $dbname = "persons";

// CREATE A CONNECTION WITH THE DATABASE
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->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 = mysqli_query($conn, $sql);
}
$cv = $conn->insert_id;
?>

My detail page:

<?php
$servername = "localhost";
$username = "root";
$password = "usbw";
$dbname = "persons";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT person_firstname, person_lastname, 
           person_email, person_phonenumber, person_cv,  
           address_street,address_housenumber, 
           address_city,address_state,address_zipcode 
    FROM person 
       inner join address on address.address_id = person.person_address";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
echo "<table border=1>
<tr>
<th>Voornaam</th>
<th>Achternaam</th>
<th>Straat</th>
<th>Huisnummer</th>
<th>Postcode</th>
<th>Stad</th>
<th>Provincie</th>
<th>Email</th>
<th>Mobiel</th>
<th>CV</th>
</tr>";


while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row["person_firstname"] . "</td>";
    echo "<td>" . $row["person_lastname"] . "</td>";
    echo "<td>" . $row["address_street"] . "</td>";
    echo "<td>" . $row["address_housenumber"] . "</td>";
    echo "<td>" . $row["address_zipcode"] . "</td>";
    echo "<td>" . $row["address_city"] . "</td>";
    echo "<td>" . $row["address_state"] . "</td>";
    echo "<td>" . $row["person_email"] . "</td>";
    echo "<td>" . $row["person_phonenumber"] . "</td>";
    echo "<td>" . $row["person_cv"] . "</td>";
    echo "</tr>";
}
} else {
echo "Er is niks in het database gevonden";
}
$conn->close();
?>

I hope you guys can help me with this I am new to PHP so I am still learning this all. Note that everything is working well... exept the upload www.test.nl/directory/filename and saving this into my table cv_path... As showing the path (maybe as a button) in the detail page. If there is a way using almost the same statement as address to upload a file to cv than that would be great. But because this is working now (for so far) I keep my file upload and person and address upload seperated. And if it's possible to add a download button into my detail page under CV which will download the file that would be perfect.

Answer 1

You have to change the SELECT query to fetch the cv_path instead of cv_id.

Then you can use php to define it to be something like $cv_file = "www.test.nl/" . $row['cv_path'];

As a result echo $cv_file; will give you www.test.nl/files/test.pdf

Now you just create a link. echo "<a href='http://" . $cv_file . "'>cv file</a>";

Same thing would be execute the same SELECT query but add it in the link:

echo "<a href='http://www.test.nl/" . $row['cv_path'] . "'>cv file</a>";

Both of them will create a link taking you to http://www.test.nl/files/test.pdf

These are really the basics, you shouldn't be asking such simple questions as they have already been answered on stack many times.

EDIT. As for the button if you want to use pure HTML you can do as BalusC says here: How to create an HTML button that acts like a link?

Which in your case would be something like this:

echo "<form action='http://" . $cv_file . "'>";
echo "<input type='submit' value='Go to cv'>";
echo "</form>";

Answer 2

with this you can store file name in your database

$filetmp = $_FILES["cv"]["tmp_name"];

And get your image or file like

<img scr="<?=base_url?>/uploads/<?=your file name in DB?>">

That's it