Link upload files to a MySQL database and then link files

General discussions related to php

Moderators: macek, egami, gesf

chris122380
New php-forum User
New php-forum User
Posts: 30
Joined: Mon Jun 25, 2012 12:41 pm

Link upload files to a MySQL database and then link files

Postby chris122380 » Thu Aug 02, 2012 10:07 am

I would like to upload files and assign them to a database field and then query that database with links to those uploaded files. This is as much as I have programmed and now lost. Any help getting this to work would be great. (Thanks to all those who have helped me so far and to YouTube for helping me find sample videos.)

Index.html : is used for the upload form.

Code: Select all

<!-- index.html
-- Upload form that uses upload.php to upload files into a  documents folder
-- future version will allow for uploading to a MySQL Database
-- Chris Anthony MA 8/2/2012
--
-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
    <title>Uploading files</title>
</head>
<body>
    <h1>Uploading files</h1>
    <hr>    <!-- Makes a Horizontal line accross the page -->
   
    <!-- Upload Form behavior of how form works decided by upload.php -->
    <form action='upload.php' method='post' enctype='multipart/form-data'>
       
        File: <input type='file' name='upload'> <!-- Uploads file -->
       
       <!--Mysql spec list not curently loading correctly ???? -->
       <select name='spec'>
              <option value=' . $row['spec'] . '>' . $row['spec'] . '</option>
        </select>
        <input type='submit' name='submit' value='Upload File Now'>     <!-- Submit button -->
       
    </form>
   
</body>
</html>


Upload.php: Handles the upload request made by the form in the index.html file.

Code: Select all

<?php
//upload.php
// Takes file from index.html form and adds the upload date to the file
// and places the file into the documents folder.
// The documents folder must already exist.
// Chris Anthony MA 8/2/2012
//
//

// Connect to MySQL Database
include('../conect.php');

// Pull upload form index.html
if($_POST['submit']){
   
    // Assign uploaded file information to Variables.
    // Upload listed in this array is the same name as the input name in the form.
    $name = $_FILES['upload']['name'];
    $temp = $_FILES['upload']['tmp_name'];
    $type = $_FILES['upload']['type'];
    $size = $_FILES['upload']['size'];
   
 // Validate variables for error checking   echo "$name<br />$temp<br />$type<br />$size";
   
    // File types allowed to be uploaded (.pdf, doc, .docx, .xls, .xtsx, .jpg, and .jpeg)
    if(($type == 'application/pdf') || ($type == 'application/msword') || ($type == 'application/vnd.openxmlformats-officedocument.wordprocessingml.document') || ($type == 'application/vnd.ms-excel') || ($type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') || ($type == 'image/jpg') || ($type == 'image/jpeg')){
   
   
    // Limit file size to 300mb $size is the max file size alloud.
    if($size <= 314572800){
       
        // echo "The file: $name size is $size";
       
        // Todays date mm_dd_yyyy_
        $todaysDate = date("m_j_Y_");
       
        //add the date to the begining of the file name.
        $newfile = $todaysDate.$name;
               
                // echo $newfile;
       
        // Move file from memory to documents folder located in the same location as this file.               
        move_uploaded_file($temp,"documents/$newfile");
     
       // echo "<img src='$newfile'>";
      // Places the upload as a link into a variable
       $link = "<a href='documents/$newfile'>$newfile</a>";
       
       // Make Link or way to test $link variable
       echo $link;
       
        // Place write to MySQL. Write $newfile to a database field.
        // query database to pull spec from Chris Anthony
            $sql = "SELECT `contacts`.`first_name` AS `Chris`, `contacts`.`last_name` AS `Anthony`, `spec`.`spec` FROM `canthony`.`projectcontact` AS `projectcontact`, `canthony`.`contacts` AS `contacts`, `canthony`.`project_spec` AS `project_spec`, `canthony`.`spec` AS `spec`, `canthony`.`project` AS `project` WHERE `projectcontact`.`Contacts_ID` = `contacts`.`ID` AND `project_spec`.`spec` = `spec`.`spec` AND `project_spec`.`Project_ID` = `project`.`Project_ID` AND `projectcontact`.`Project_ID` = `project`.`Project_ID`";
$result = mysql_query($sql);
       
       
       
   
    }else{
       
        // File is bigger the 300mb warning
        echo "The file: $name is to big....<br />The size is $size and need to be less than 300mb";
       
    }
       
    }else{
       
        // File type not allowed warning
        echo "This type $type is not allowed";
    }
       
}else{
   
    // forces back to index.html when trying to open upload.php from browser URL
    header("Location: index.html");
   
}

?>


Spec_list.php: queries the Database and places the output with links to the file. This is my sample page not yet edited to work with a field that has the name of the file.

Code: Select all

<?php
session_start(); //start session and list variables as session variables
$_SESSION['pdf'];

// List Projects spec numbers with links to there spec numbers
include('conect.php');

//Query to pull data from Chris Anthony Business (Should be changed to userID variable to work with login and password system. May need new query or may need new relationships.
$query = mysql_query("SELECT `contacts`.`first_name`, `contacts`.`last_name`, `contacts`.`company`, `spec`.`spec`, `spec`.`discription` FROM `canthony`.`project_spec` AS `project_spec`, `canthony`.`project` AS `project`, `canthony`.`spec` AS `spec`, `canthony`.`contacts` AS `contacts` WHERE `project_spec`.`Project_ID` = `project`.`Project_ID` AND `project_spec`.`spec` = `spec`.`spec` AND `contacts`.`first_name` = 'Chris' AND `contacts`.`last_name` = 'Anthony'"); //query the database

//fetch the results and convert results into an array
WHILE ($rows = mysql_fetch_array($query)): //Fetch data from $query as long as there is data to fetch

$rows = preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $rows);

      // assign fields from the query to Variables using session variables
      $_SESSION['first_name'] = $rows['first_name'];
      $_SESSION['Last_name'] = $rows['last_name'];
      $_SESSION['company'] = $rows['company'];
      $_SESSION['spec'] = $rows['spec'];
      $_SESSION['discription'] = $rows['discription'];
      
      
   
   //  assign fields from the query to Variables using non session variables
    //   $first_name = $rows['first_name'];
   //   $last_name = $rows['last_name'];
   //   $company = $rows['company'];
   //   $spec = $rows['spec'];
   //   $discription = $rows['discription'];

   
   
   //   echo "<a href="spec/$spec.pdf">$spec&nbsp;$discription</a>
   //   <br />";
      
   //   echo "<a href='\spec/$spec.pdf\'>$spec&nbsp;$discription</a><br />"
   
// no sesion    $spec = str_replace (" ", "", $spec);

$_SESSION['spec'] = str_replace (" ", "", $_SESSION['spec']);

// echo '<a href="spec/',$_SESSION['spec'],'.pdf">', $_SESSION['spec'],'&nbsp;',$_SESSION['discription'],'</a><br/>';

// failed to target $_SESSION['pdf'] example echo '<a href="spec/',$_SESSION['spec'],'.pdf" target= '$_SESSION['pdf']'>', $_SESSION['spec'],'&nbsp;',$_SESSION['discription'],'</a><br/>';

// "$spec&nbsp;$discription": $pdf="$spec"; break;

//echo $_SESSION['spec'];
   
   

?>




<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Spec List for Project</title>
</head>

<body>

<a href="spec/<?php echo $_SESSION['spec']; ?>.pdf" target="pdf"> <?php echo $_SESSION['spec'],'&nbsp;',$_SESSION['discription'], '<br />'; ?>   </a>
<?php endwhile; ?>

<a href="blank.php" target="pdf">Clear right side</a>

</body>

</html>

chris122380
New php-forum User
New php-forum User
Posts: 30
Joined: Mon Jun 25, 2012 12:41 pm

Re: Link upload files to a MySQL database and then link file

Postby chris122380 » Tue Aug 07, 2012 12:18 pm

Updated the code. I now visually have the drop down menu and the upload on the same page. I have the index.php file that has the drop down php and the form. I also have a upload.php wich I would like to have manage the form uploading the file to documents folder and the file name uploaded to the operation_manuals table field manual_name and the spec next to it in the table being the spec chosen from the drop down. Updates can also be found at: http://betterphp.co.uk/board/viewtopic.php?f=4&t=1824

index.php :

Code: Select all

<?php
// index.php
// create a droptdown menu and upload form
// connect upload file name to the dropdown menu field.
// Chris Anthony MA 8/6/2012
//
// Connect to MySQL Database
include('../../conect.php');

//dropdown box of spec see
// http://www.learnphp.co/creating-an-html-select-box-from-a-mysql-results-array-using-php/
// for example code.
// query the database for the spec.
$sql = "SELECT * FROM `canthony`.`spec` AS `spec`";
   
        $query = mysql_query($sql);
     while ( $results[] = mysql_fetch_object ( $query ) );
     array_pop ( $results );
// print_r_html($results);

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
    <title>Uploading files</title>
</head>
<body>
        <h1>Uploading files</h1>
    <hr>    <!-- Makes a Horizontal line accross the page -->
<!-- Form for selecting spec and file to upload to the operation_manuals table
spec to spec and upload to manual_name field.
-->
 <form action='upload.php' method='post' enctype='multipart/form-data'>
       
        File: <input type='file' name='upload'> <!-- Uploads file -->
     
        <!-- Drop down menu -->       
        <select name="spec_drop">
         <?php foreach ( $results as $option ) : ?>
              <option value="<?php echo $option->spec; ?>"><?php echo $option->spec ; ?></option>
         <?php endforeach; ?>
         </select>
         <input type='submit' name='submit' value='Upload File Now'>     <!-- Submit button -->
 </form>

</body>
</html>


upload.php hasn't changed from the first page yet.

chris122380
New php-forum User
New php-forum User
Posts: 30
Joined: Mon Jun 25, 2012 12:41 pm

Re: Link upload files to a MySQL database and then link file

Postby chris122380 » Fri Aug 10, 2012 1:46 pm

Problem solved with some help from Temor at the BetterPHP forum. Completed code that works found below.

index.php

Code: Select all

<?php
// index.php
// create a droptdown menu and upload form
// connect upload file name to the dropdown menu field.
// Chris Anthony MA 8/6/2012
//
// Connect to MySQL Database
include('../../conect.php');
 
//dropdown box of spec see
// http://www.learnphp.co/creating-an-html-select-box-from-a-mysql-results-array-using-php/
// for example code.
// query the database for the spec.
 $sql = "SELECT * FROM `canthony`.`spec` AS `spec`";
   
        $query = mysql_query($sql);
     while ( $results[] = mysql_fetch_object ( $query ) );
     array_pop ( $results );
// print_r_html($results);
 
?>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
    <title>Uploading files</title>
</head>
<body>
        <h1>Uploading files</h1>
    <hr>    <!-- Makes a Horizontal line accross the page -->
<!-- Form for selecting spec and file to upload to the operation_manuals table
spec to spec and upload to manual_name field.
-->
 <form action='upload.php' method='post' enctype='multipart/form-data'>
       
        File: <input type='file' name='upload'> <!-- Uploads file -->
     
        <!-- Drop down menu -->       
        <select name="spec_drop">
         <?php foreach ( $results as $option ) : ?>
              <option value="<?php echo $option->spec; ?>"><?php echo $option->spec ; ?></option>
         <?php endforeach; ?>
         </select>
         <input type='submit' name='submit' value='Upload File Now'>     <!-- Submit button -->
 </form>
 
</body>
</html>


upload.php

Code: Select all

<?php
//upload.php
// Takes file from index.html form and adds the upload date to the file
// and places the file into the documents folder.
// The documents folder must already exist.
// Chris Anthony MA 8/2/2012
//
//
// Connect to MySQL Database
// include('../conect.php');

// Pull upload form index.html
if($_POST['submit']){
   
    // Assign uploaded file information to Variables.
    // Upload listed in this array is the same name as the input name in the form.
    $name = $_FILES['upload']['name'];
    $temp = $_FILES['upload']['tmp_name'];
    $type = $_FILES['upload']['type'];
    $size = $_FILES['upload']['size'];
   
 // Validate variables for error checking   echo "$name<br />$temp<br />$type<br />$size";
   
    // File types allowed to be uploaded (.pdf, doc, .docx, .xls, .xtsx, .jpg, and .jpeg)
    if(($type == 'application/pdf') || ($type == 'application/msword') || ($type == 'application/vnd.openxmlformats-officedocument.wordprocessingml.document') || ($type == 'application/vnd.ms-excel') || ($type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') || ($type == 'image/jpg') || ($type == 'image/jpeg')){
   
   
    // Limit file size to 300mb $size is the max file size alloud.
    if($size <= 314572800){
       
        // echo "The file: $name size is $size";
       
        // Todays date mm_dd_yyyy_
        $todaysDate = date("m_j_Y_");
       
        //add the date to the begining of the file name.
        $newfile = $todaysDate.$name;
               
                // echo $newfile;
       
        // Move file from memory to documents folder located in the same location as this file.               
        move_uploaded_file($temp,"documents/$newfile");
     
       // echo "<img src='$newfile'>";
      // Places the upload as a link into a variable
       $link = "<a href='documents/$newfile'>$newfile</a>";
       
       // Make Link or way to test $link variable
       echo $link;
       
        // Place write to MySQL. Write $newfile to a database field.
        // query database to pull spec from Chris Anthony
          /*  $sql = "SELECT `contacts`.`first_name` AS `Chris`, `contacts`.`last_name` AS `Anthony`, `spec`.`spec` FROM `canthony`.`projectcontact` AS `projectcontact`, `canthony`.`contacts` AS `contacts`, `canthony`.`project_spec` AS `project_spec`, `canthony`.`spec` AS `spec`, `canthony`.`project` AS `project` WHERE `projectcontact`.`Contacts_ID` = `contacts`.`ID` AND `project_spec`.`spec` = `spec`.`spec` AND `project_spec`.`Project_ID` = `project`.`Project_ID` AND `projectcontact`.`Project_ID` = `project`.`Project_ID`";
$result = mysql_query($sql); */
       
       /* $sql = "INSERT INTO `manual_name` (`$newfile`) VALUES ('{$_FILES['upload']['name']}' ";
mysql_query($sql); */
       
       include('../../conect.php');
       
       $option = $_POST['spec_drop'];
       
       mysql_query("INSERT INTO operation_manuals (manual_ID, manual_status, spec, manual_name) VALUES('','1','$option','$newfile')");
       // echo mysql_error();
   
    }else{
       
        // File is bigger the 300mb warning
        echo "The file: $name is to big....<br />The size is $size and need to be less than 300mb";
       
    }
       
    }else{
       
        // File type not allowed warning
        echo "This type $type is not allowed";
    }
       
}else{
   
    // forces back to index.html when trying to open upload.php from browser URL
    header("Location: index.php");
   
}

?>


Return to “PHP General”

Who is online

Users browsing this forum: No registered users and 1 guest

cron