Link upload files to a MySQL database and then link files

Ask about a PHP problem here.
Post Reply
chris122380
Posts: 9
Joined: Thu Jul 26, 2012 3:17 pm
Contact:

Link upload files to a MySQL database and then link files

Post by chris122380 »

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.) I visually have the drop down menu and the upload on the same page. I have the index.php file that has the drop down in php and the form in html. 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.

Index.php :[syntax=php]<?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 ... 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>[/syntax]

upload.php :[syntax=php]<?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");

}

?>[/syntax]
Local Server: XAMMP 1.8.0, Windows 7 64bit
Local PHP: 5.4.4
Local Apache: 2.4.2
Local MySQL: 14.14 Distrib 5.5.25a, for Win32 (x86)
PHP/HTML Editor: Komodo Edit, version 7.1.0
User avatar
Temor
Posts: 1186
Joined: Thu May 05, 2011 8:04 pm

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

Post by Temor »

Well, you would have to create an INSERT query in your upload.php. The way I usually do this is to link the file by it's ID in the table.
[syntax=php]$sql = "INSERT INTO `file` (`file_name`) VALUES ('{$_FILES['upload']['name']}' ";
mysql_query($sql);[/syntax]

and then I would fetch the item by the ID as stated previously.

[syntax=php]$sql = "SELECT `file_name` FROM `file` WHERE `id` = {$id}
$result = mysql_query($sql);[/syntax]
chris122380
Posts: 9
Joined: Thu Jul 26, 2012 3:17 pm
Contact:

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

Post by chris122380 »

My table (operation_manuals) has two fields spec (Spec is a foreign key that links to a table with the list of spec numbers and there descriptions) and manual_name. The drop down menu pulls the spec number from the spec table. The file name needs to be linked with the spec number as I may need to reference the file by name or by spec number.

I have added the fallowing code to the upload.php after my link is made (Link was for testing purposes). [syntax=php] $sql = "INSERT INTO `manual_name` (`$newfile`) VALUES ('{$_FILES['upload']['name']}' ";
mysql_query($sql);[/syntax] I then get the fallowing error:
( ! ) SCREAM: Error suppression ignored for
( ! ) Warning: include(../conect.php): failed to open stream: No such file or directory in C:\xampp\htdocs\manualsaccess\test_iFrame\File_upload\new\upload.php on line 11
and
( ! ) SCREAM: Error suppression ignored for
( ! ) Warning: include(): Failed opening '../conect.php' for inclusion (include_path='.;C:\xampp\php\PEAR') in C:\xampp\htdocs\manualsaccess\test_iFrame\File_upload\new\upload.php on line 11


Line 11 is my include('../conect.php') to connect to the database. I know the connection is working so unsure why the error.

I have also tried [syntax=php]mysql_query("INSERT INTO manual_name VALUES('','','$option','$newfile')")[/syntax] same two errors and a third error now
( ! ) SCREAM: Error suppression ignored for
( ! ) Notice: Undefined variable: option in C:\xampp\htdocs\manualsaccess\test_iFrame\File_upload\new\upload.php on line 59
line 59 is the INSERT query line. Same errors when trying this code instead. [syntax=php] mysql_query("INSERT INTO manual_name (manual_ID, manual_status, spec, manual_name) VALUES('','','$option','$newfile')");[/syntax]
Local Server: XAMMP 1.8.0, Windows 7 64bit
Local PHP: 5.4.4
Local Apache: 2.4.2
Local MySQL: 14.14 Distrib 5.5.25a, for Win32 (x86)
PHP/HTML Editor: Komodo Edit, version 7.1.0
chris122380
Posts: 9
Joined: Thu Jul 26, 2012 3:17 pm
Contact:

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

Post by chris122380 »

Temor wrote:Well, you would have to create an INSERT query in your upload.php. The way I usually do this is to link the file by it's ID in the table.


I have added [syntax=php] $option = $_POST['spec_drop'];

mysql_query("INSERT INTO operation_manuals (manual_ID, manual_status, spec, manual_name) VALUES('','','$option','$newfile')");[/syntax] I have no errors and seems to run correctly but nothing posts to the database. is there a way to error check the INSERT INTO MySQL to be sure there are no errors when trying to INSERT to the database?

I have also tried placing my INSERT before the file is moved (Line 38 in the upload.php example below) to the folder and still didn't post anything to the database).

Updated index.php[syntax=php]<?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 ... 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>

[/syntax]

Updated upload.php[syntax=php]<?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); */

$option = $_POST['spec_drop'];

mysql_query("INSERT INTO operation_manuals (manual_ID, manual_status, spec, manual_name) VALUES('','','$option','$newfile')");


}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");

}

?>[/syntax]
Local Server: XAMMP 1.8.0, Windows 7 64bit
Local PHP: 5.4.4
Local Apache: 2.4.2
Local MySQL: 14.14 Distrib 5.5.25a, for Win32 (x86)
PHP/HTML Editor: Komodo Edit, version 7.1.0
User avatar
Temor
Posts: 1186
Joined: Thu May 05, 2011 8:04 pm

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

Post by Temor »

Adding [syntax=php]echo mysql_error();[/syntax] under a failing query will provide a more detailed error message.
This
[syntax=php]$sql = "INSERT INTO `manual_name` (`$newfile`) VALUES ('{$_FILES['upload']['name']}' ";
mysql_query($sql);[/syntax]
won't work seeing as $newfile is not a column. Change that to the column name which you want to populate with $_FILES['upload']['name'].

/edit;
I will take a closer look at this tomorrow. I'm to tired to think right now. If I haven't updated this post in say 15 hours, send me a PM and remind me, will you? :)
chris122380
Posts: 9
Joined: Thu Jul 26, 2012 3:17 pm
Contact:

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

Post by chris122380 »

Thank you the [syntax=php]echo mysql_error();[/syntax] helped me a lot in looking for the solution. My upload is now working correctly with a drop down menu.

Updated and working code.
index.php[syntax=php]<?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 ... 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>[/syntax]

upload.php[syntax=php]<?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");

}

?>[/syntax]
Local Server: XAMMP 1.8.0, Windows 7 64bit
Local PHP: 5.4.4
Local Apache: 2.4.2
Local MySQL: 14.14 Distrib 5.5.25a, for Win32 (x86)
PHP/HTML Editor: Komodo Edit, version 7.1.0
Post Reply