Solved

Insert in php multiple attachment to mysql database in same time but they can be also empty

Posted on 2014-02-22
8
1,747 Views
Last Modified: 2014-03-01
Hello, I have form where user can insert title & news to mysql database. But there must be option to insert also couple word or pdf attachments.

But i don't figure how I can make that kind of form and it checker that those pdf attachment can also be empty and then only title and news go to mysql database.

Here is form and now there is only one attachment browser.

<form action="lisaa_tiedosto_uutinen_action.php" method="post" enctype="multipart/form-data">

      Title: <input name="title" size="40" maxlength="255">
      <br>
      Text1: <textarea name="text1"  rows="7" cols="30"></textarea>
      <br>
      <br>
      <input type="file" name="uploaded_file">
      <br>
      <br>
            <input type="submit" value="Lisää uutinen">
</form>

Open in new window


And here is action page that insert data to database:

<body>

    <?php
	error_reporting(E_ALL ^ E_NOTICE);
	include("config.php");
    // Check if a file has been uploaded
    if(isset($_FILES['uploaded_file'])) {
        // Make sure the file was sent without errors
        if($_FILES['uploaded_file']['error'] == 0) {
            // Connect to the database
            $dbLink = new mysqli('localhost', 'root', 'password', 'db');
            if(mysqli_connect_errno()) {
                die("MySQL connection failed: ". mysqli_connect_error());
            }
     
            // Gather all required data
            $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
            $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
            $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
            $size = intval($_FILES['uploaded_file']['size']);

      // Set global variables to easier names
      $title = $_POST['title'];
      $text1 = $_POST['text1'];
      $text2 = $_POST['text2'];
	 
            // Create the SQL query
            $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`, `name`, `mime`, `size`, `data`, `created`
                )
                VALUES (
                    '$title',NOW(),'$text1','{$name}', '{$mime}', {$size}, '{$data}', NOW()
                )";
     
            // Execute the query
            $result = $dbLink->query($query);
     
            // Check if it was successfull
            if($result) {
                echo 'Success! Your file was successfully added!';
            }
            else {
                echo 'Error! Failed to insert the file'
                   . "<pre>{$dbLink->error}</pre>";
            }
        }
        else {
            echo 'An error accured while the file was being uploaded. '
               . 'Error code: '. intval($_FILES['uploaded_file']['error']);
        }
     
        // Close the mysql connection
        $dbLink->close();
    }
    else {
        echo 'Error! A file was not sent!';
    }
     
    // Echo a link back to the main page
    echo '<p>Click <a href="lisaa_tiedosto.php">here</a> to go back</p>';
    ?>

</body>

Open in new window

0
Comment
Question by:Enska77
  • 5
  • 3
8 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39879150
I can show you how to upload a file.  The overall design of something like this would put the uploaded file (if any) into the server file system and would write the smaller bits of data, including the URL of the uploaded file, into the data base.  If you're new to PHP programming, some good learning resources are available in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Here is my teaching example showing the principles of file uploads.

<?php // RAY_upload_one_file.php
error_reporting(E_ALL);


// MANUAL REFERENCE PAGES YOU MUST UNDERSTAND TO UPLOAD FILES
// http://php.net/manual/en/features.file-upload.php
// http://php.net/manual/en/features.file-upload.common-pitfalls.php
// http://php.net/manual/en/function.move-uploaded-file.php
// http://php.net/manual/en/reserved.variables.files.php

// MANUAL PAGES THAT ARE IMPORTANT IF YOU ARE DEALING WITH LARGE FILES
// http://php.net/manual/en/ini.core.php#ini.upload-max-filesize
// http://php.net/manual/en/ini.core.php#ini.post-max-size
// http://php.net/manual/en/info.configuration.php#ini.max-input-time


// PHP 5.1+  SEE http://php.net/manual/en/function.date-default-timezone-set.php
date_default_timezone_set('America/Chicago');

// ESTABLISH THE BIGGEST FILE SIZE WE CAN ACCEPT - ABOUT 8 MB
$max_file_size = '8000000';

// ESTABLISH THE KINDS OF FILE EXTENSIONS WE CAN ACCEPT (USE UPPERCASE ONLY)
$file_exts = array
( 'XLS'
, 'XLSX'
, 'PDF'
)
;
$f_exts = implode(', ', $file_exts);

// ESTABLISH THE NAME OF THE DESTINATION FOLDER
$my_dir = getcwd();

// OR USE THIS TO PUT UPLOADS IN A SEPARATE FOLDER
$my_dir = 'RAY_junk';
if (!is_dir($my_dir))
{
    mkdir($my_dir);
}

// LIST OF THE ERRORS THAT MAY BE REPORTED IN $_FILES[]["error"] (THERE IS NO #5)
$errors = array
( 0 => "Success!"
, 1 => "The uploaded file exceeds the upload_max_filesize directive in php.ini"
, 2 => "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form"
, 3 => "The uploaded file was only partially uploaded"
, 4 => "No file was uploaded"
, 5 => "UNDEFINED ERROR"
, 6 => "Missing a temporary folder"
, 7 => "Cannot write file to disk"
)
;


// IF THERE IS INFORMATION POSTED
if (!empty($_POST))
{
    // IF THERE ARE ERRORS
    $error_code    = $_FILES["userfile"]["error"];
    if ($error_code)
    {
        trigger_error($errors[$error_code], E_USER_ERROR);
    }

    // GET THE FILE SIZE
    $fsize = number_format($_FILES["userfile"]["size"]);

    // SYNTHESIZE THE NEW FILE NAME FOR TEMPORARY STORAGE
    $fname = basename($_FILES['userfile']['name']);

    // FAIL IF THIS IS NOT AN ALLOWABLE EXTENSION
    $f_ext = explode('.', $fname);
    $f_ext = end($f_ext);
    $f_ext = strtoupper(trim($f_ext));
    if (!in_array($f_ext, $file_exts)) trigger_error("$f_ext NOT ALLOWED.  CHOOSE FROM $f_exts", E_USER_ERROR);

    // THE SERVER PATH TO THE FILE
    $my_path
    = getcwd()
    . DIRECTORY_SEPARATOR
    . $my_dir
    . DIRECTORY_SEPARATOR
    . $fname
    ;

    // THE URL PATH TO THE FILE
    $my_url
    = $my_dir
    . DIRECTORY_SEPARATOR
    . $fname
    ;

    // MESSAGES ABOUT THE UPLOAD STATUS, IF ANY
    $msg = NULL;

    // IF THE FILE IS NEW (DOES NOT EXIST)
    if (!file_exists($my_path))
    {
        // IF THE MOVE FUNCTION WORKED CORRECTLY
        if (move_uploaded_file($_FILES['userfile']['tmp_name'], $my_path))
        {
            $upload_success = 1;
        }
        // IF THE MOVE FUNCTION FAILED IT PROBABLY THREW A MESSAGE
        else
        {
            $upload_success = -1;
            trigger_error("MOVE TO $my_path FAILED", E_USER_ERROR);
        }
    }

    // IF THE FILE ALREADY EXISTS
    else
    {
        $msg .= "<br/><b><i>$my_url</i></b> already exists" . PHP_EOL;

        // SHOULD WE OVERWRITE THE FILE? IF NOT
        if (empty($_POST["overwrite"]))
        {
            $upload_success = 0;
        }

        // IF WE SHOULD OVERWRITE THE FILE, TRY TO MAKE A BACKUP
        else
        {
            $now    = date('Y-m-d-His');
            $my_bak = $my_path . '.' . $now . '.bak';
            if (!copy($my_path, $my_bak))
            {
                $msg .= "<br/><strong>Attempted Backup Failed!</strong>" . PHP_EOL;
            }
            if (move_uploaded_file($_FILES['userfile']['tmp_name'], $my_path))
            {
                $upload_success = 2;
            }
            else
            {
                $upload_success = -1;
                trigger_error("MOVE TO $my_path FAILED", E_USER_ERROR);
            }
        }
    }

    // PREPARE A REPORT OF THE SCRIPT'S SUCCESS OR FAILURE
    if ($upload_success == 2) { $msg .= "<br/>A backup was made and the file was overwritten" . PHP_EOL; }
    if ($upload_success == 1) { $msg .= "<br/><strong>$my_url</strong> has been saved" . PHP_EOL; }
    if ($upload_success == 0) { $msg .= "<br/><strong>It was NOT overwritten.</strong>" . PHP_EOL; }
    if ($upload_success < 0)  { $msg .= "<br/><strong>ERROR: $my_url NOT SAVED - SEE WARNING FROM move_uploaded_file()</strong>" . PHP_EOL; }

    // ADD FILE SIZE AND PERMISSION INFORMATION
    if ($upload_success > 0)
    {
        $msg .= "<br/>$fsize bytes uploaded" . PHP_EOL;
        if (!chmod ($my_path, 0755))
        {
            $msg .= "<br/>chmod(0755) FAILED: fileperms() = ";
            $msg .= substr(sprintf('%o', fileperms($my_path)), -4);
        }
    }

    // SHOW THE SUCCESS OR FAILURE
    echo $msg;

    // SHOW A LINK TO THE FILE
    echo '<br/>'
    . '<a href="'
    . $my_url
    . '" target="_blank">'
    . "See: $my_url"
    . '</a>'
    ;
}


// CREATE THE FORM FOR INPUT (USING HEREDOC SYNTAX)
$form = <<<ENDFORM
<p>Upload one file
<form enctype="multipart/form-data" method="post">
<!-- MAX_FILE_SIZE MUST PRECEDE THE FILE INPUT FIELD -->
<input type="hidden" name="MAX_FILE_SIZE" value="$max_file_size" />
Find a file to Upload ($f_exts): <input name="userfile" type="file" />
<br/>Check this box
<input autocomplete="off" type="checkbox" name="overwrite" /> to <strong>overwrite</strong> existing files
</br><input type="submit" value="Upload" />
</form>
</p>
ENDFORM;

echo $form;

Open in new window

HTH, ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39879316
Here is an outline showing how you might want to reconfigure the logic in your action= script.  Obviously I cannot test this, but hopefully it will give you some good ideas.

<body>

<?php
// NEVER SUPPRES NOTICE MESSAGES - INSTEAD CORRECT THE UNDERLYING CONDITIONS THAT CAUSE THE NOTICE
error_reporting(E_ALL);

// NOT SURE WHAT THIS DOES - IT SHOULD PROBABLY INCLUDE THE DB CONNECTION?
include("config.php");

$dbLink = new mysqli('localhost', 'root', 'password', 'db');
if(!$dbLink) 
{
    trigger_error("MySQL connection failed: ". mysqli_connect_error(), E_USER_ERROR);
}

// ESCAPE THE EXTERNAL DATA THAT WILL BE NEEDED
$title = $dbLink->real_escape_string($_POST['title']);
$text1 = $dbLink->real_escape_string($_POST['text1']);
$text2 = $dbLink->real_escape_string($_POST['text2']);

// IF THERE WAS NO FILE UPLOADED
if ($_FILES['uploaded_file']['error'] == 4) 
{
    /* RUN THE QUERY WITHOUT THE FILE DATA 
     */
}
// IF THERE WAS A CLEAN UPLOAD
elseif ($_FILES['uploaded_file']['error'] == 0)
{
    /* USE Move_UpLoaded_File() 
     * ESCAPE NAME, TYPE, ETC
     * RUN THE QUERY WITH THE FILE DATA
     */
}
else
{
    /* RUN THE QUERY WITHOUT THE FILE DATA 
     * USE $_FILES['uploaded_file']['error'] TO IDENTIFY THE ERROR
     */
}

?>

</body>

Open in new window

0
 

Author Comment

by:Enska77
ID: 39880460
Thank you very much Ray, I tested your skript, but there is something that I cant figure out. Nothing goes into mysql database. I don't get any error message, text and title goes nowhere. And when there is attachment that disappear also. Here is action page code.

<?php
// NEVER SUPPRES NOTICE MESSAGES - INSTEAD CORRECT THE UNDERLYING CONDITIONS THAT CAUSE THE NOTICE
error_reporting(E_ALL);

$dbLink = new mysqli('localhost', 'root', 'password', 'db');
if(!$dbLink)
{
    trigger_error("MySQL connection failed: ". mysqli_connect_error(), E_USER_ERROR);
}


// ESCAPE THE EXTERNAL DATA THAT WILL BE NEEDED
$title = $dbLink->real_escape_string($_POST['title']);
$text1 = $dbLink->real_escape_string($_POST['text1']);


// IF THERE WAS NO FILE UPLOADED
if ($_FILES['uploaded_file']['error'] == 4)
{
    /* RUN THE QUERY WITHOUT THE FILE DATA
     */
      
                   $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`
                )
                VALUES (
                    '$title',NOW(),'$text1'
                )";
}
// IF THERE WAS A CLEAN UPLOAD
elseif ($_FILES['uploaded_file']['error'] == 0)
{

// Gather all required data
            $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
            $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
            $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
            $size = intval($_FILES['uploaded_file']['size']);
    /* USE Move_UpLoaded_File()
     * ESCAPE NAME, TYPE, ETC
     * RUN THE QUERY WITH THE FILE DATA
     */
      
                   $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`, `name`, `mime`, `size`, `data`, `created`
                )
                VALUES (
                    '$title',NOW(),'$text1','{$name}', '{$mime}', {$size}, '{$data}', NOW()
                )";
      
}
else
{
    /* RUN THE QUERY WITHOUT THE FILE DATA
     * USE $_FILES['uploaded_file']['error'] TO IDENTIFY THE ERROR
     */
                   $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`
                )
                VALUES (
                    '$title',NOW(),'$text1'
                )";
}

?>
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39880588
Going forward, please use the Code feature (in the toolbar of the Post a Comment section) to post the code.  It makes the code much easier to read and it gives us line numbers that facilitate discussion. See below for the code from the post immediately above.

It appears that your script creates the query string, but does not actually run the query.  Please see this article with examples showing how to run the query and test for success.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

There may be some other things wrong and since I can't test I'll be glad to help after you get the queries working.  Just post back if you see anything that looks awry.  One thing that leaps out at me is the use of file_get_contents() to read what you think is an uploaded file.  Don't do that.  There are security issues with uploaded files, and as a result there are standard ways of dealing with uploaded files.  Please go back to this code snippet and check the code sample and the man page references carefully!  You do not want to put the uploaded file into the data base.  You want to store the file in the server file system and put a URL pointer into the data base.  There are a lot of reasons for this, but it takes a background in computer science to understand it, so for now just accept it as an article of faith.  Data base systems do not work well with large (wide) columns of data.

In case you do not have it already, you may find that phpMyAdmin is a really useful tool when you start working with data bases.  Free and open source.

<?php
// NEVER SUPPRES NOTICE MESSAGES - INSTEAD CORRECT THE UNDERLYING CONDITIONS THAT CAUSE THE NOTICE
error_reporting(E_ALL);

$dbLink = new mysqli('localhost', 'root', 'password', 'db');
if(!$dbLink)
{
    trigger_error("MySQL connection failed: ". mysqli_connect_error(), E_USER_ERROR);
}


// ESCAPE THE EXTERNAL DATA THAT WILL BE NEEDED
$title = $dbLink->real_escape_string($_POST['title']);
$text1 = $dbLink->real_escape_string($_POST['text1']);


// IF THERE WAS NO FILE UPLOADED
if ($_FILES['uploaded_file']['error'] == 4)
{
    /* RUN THE QUERY WITHOUT THE FILE DATA
     */
      
                   $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`
                )
                VALUES (
                    '$title',NOW(),'$text1'
                )";
}
// IF THERE WAS A CLEAN UPLOAD
elseif ($_FILES['uploaded_file']['error'] == 0)
{

// Gather all required data
            $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
            $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
            $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
            $size = intval($_FILES['uploaded_file']['size']);
    /* USE Move_UpLoaded_File()
     * ESCAPE NAME, TYPE, ETC
     * RUN THE QUERY WITH THE FILE DATA
     */
      
                   $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`, `name`, `mime`, `size`, `data`, `created`
                )
                VALUES (
                    '$title',NOW(),'$text1','{$name}', '{$mime}', {$size}, '{$data}', NOW()
                )";
      
}
else
{
    /* RUN THE QUERY WITHOUT THE FILE DATA
     * USE $_FILES['uploaded_file']['error'] TO IDENTIFY THE ERROR
     */
                   $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`
                )
                VALUES (
                    '$title',NOW(),'$text1'
                )";
}

?>

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Enska77
ID: 39880834
Thank you so much of your patience and guidance. I have forgot next line  $result = $dbLink->query($query);  and now everything goes to database, but there is that problem that text1 data goes double to database? When I write "test" in textfield it shows in site like testtest? There is somekind of douple insert?

     <?php
// NEVER SUPPRES NOTICE MESSAGES - INSTEAD CORRECT THE UNDERLYING CONDITIONS THAT CAUSE THE NOTICE
error_reporting(E_ALL);

$dbLink = new mysqli('localhost', 'root', 'password', 'db');
if(!$dbLink) 
{
    trigger_error("MySQL connection failed: ". mysqli_connect_error(), E_USER_ERROR);
}


// ESCAPE THE EXTERNAL DATA THAT WILL BE NEEDED
$title = $dbLink->real_escape_string($_POST['title']);
$text1 = $dbLink->real_escape_string($_POST['text1']);


// IF THERE WAS NO FILE UPLOADED
if ($_FILES['uploaded_file']['error'] == 4) 
{
    /* RUN THE QUERY WITHOUT THE FILE DATA 
     */
	 
	             $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`
                )
                VALUES (
                    '$title',NOW(),'$text1'
                )";
				// Execute the query
            $result = $dbLink->query($query);
			if($result) {
                echo 'Success! Your file was successfully added!';
            }
}
// IF THERE WAS A CLEAN UPLOAD
elseif ($_FILES['uploaded_file']['error'] == 0)
{

// Gather all required data
            $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
            $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
            $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
            $size = intval($_FILES['uploaded_file']['size']);
    /* USE Move_UpLoaded_File() 
     * ESCAPE NAME, TYPE, ETC
     * RUN THE QUERY WITH THE FILE DATA
     */
	 
	             $query = "
                INSERT INTO `uutiset_tiedostot` (
                   `title`, `dtime`, `text1`, `name`, `mime`, `size`, `data`, `created`
                )
                VALUES (
                    '$title',NOW(),'$text1','{$name}', '{$mime}', {$size}, '{$data}', NOW()
                )";
				// Execute the query
            $result = $dbLink->query($query);
			if($result) {
                echo 'Success! Your file was successfully added!';
            }
	 
}

?>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39880924
I cannot readily see anything that would cause the fields to be concatenated to themselves in the way you describe.  There must be more to the script than this, and since we cannot see that part, there is no way to figure out what might be wrong.  You might start by checking the input to this script.  You can use var_dump($_POST) and var_dump($_FILES) to see the external input.
http://php.net/manual/en/function.var-dump.php

I'm going to recommend that you take some time to study the basics about PHP and MySQL.  File uploads are a somewhat more advanced topic and sometimes it's wise to walk before you try to run.  To that end there are some good learning resources in this article.  The article will also help you avoid the many bad examples that litter the internet (such as the example that led you to believe it's a good idea to put an image file into a data base).
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
 

Author Closing Comment

by:Enska77
ID: 39896844
Thank you Ray, i need investigate that store the file in the server file system and put a URL pointer into the data base. Is there somewhere example?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39897202
There are examples of PHP code all over the internet.  Unfortunately these do not come with expiration dates or endorsement for best practices, and many of them are terrible -- just plain dangerous, insecure or wrong.  For that reason, let this article guide your learning adventures in PHP.  I've been careful to choose the best-of-breed resources (books, web sites, etc.) and list them in the article.  If it's not called out in the article, don't even look at it!  Once you've been through the learning resources in the article, you will be well qualified to seek out and evaluate PHP examples, and to write your own PHP code examples.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to dynamically set the form action using jQuery.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now