SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Uploading files to MySQL DB

    Hi all,
    I have been trying to upload files to my server (localhost at the moment) and I have followed this tutorial below:

    http://www.php-mysql-tutorial.com/php-mysql-upload.php

    It works great, and all the info about the file gets inserted to the DB.
    Now, sorry if this sounds really ignorant, but where will these files get uploaded to?

    In the example, there are three includes used. One for open and close connection, and another config.php.

    I could find these files anywhere on the site, so changed my script like so:

    PHP Code:
       <?
       
    if(isset($_POST['upload']))
       {
               
    $fileName $_FILES['userfile']['name'];
               
    $tmpName  $_FILES['userfile']['tmp_name'];
               
    $fileSize $_FILES['userfile']['size'];
               
    $fileType $_FILES['userfile']['type'];
               
               
    $fp fopen($tmpName'r');
               
    $content fread($fp$fileSize);
               
    $content addslashes($content);
               
    fclose($fp);
               
               if(!
    get_magic_quotes_gpc())
               {
                   
    $fileName addslashes($fileName);
               }
               
       
               
    $con= @mysql_connect("localhost","user","secret");
               
    mysql_select_db("my_db,$con);
               
     
    $query = "INSERT INTO upload (namesizetypecontent ".
                        "
    VALUES ('$fileName''$fileSize''$fileType''$content')";
       
               mysql_query(
    $query) or die('Error, query failed' .mysql_error());                    
               
               
               echo "
    <br>File $fileName uploaded<br>";
               exit;
       }        
       ?>
    Will one of the include files that the author used, be used to define upload paths etc?

    Cheers,
    D

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The files are actually stored in the DB. They are stored in the `content` column of the `upload` table.
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  3. #3
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The files will be stored into your database, as they say in the tutorial
    Using PHP to upload files into MySQL database sometimes needed by some web application.
    They will be stored into the content column.

    Regards,

    MichaŽl
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  4. #4
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah thanks, silly me.
    So now, to output the image, what would I do?
    I currently have:
    PHP Code:
     $sql=mysql_query("select * from upload where id=9",$con);
         while(
    $row=mysql_fetch_array($sql))
              {
               
    $imgName=$row["name"];
               
    $img=$row["content"];                                                        
              }
     print (
    "<img src=" .$img ."/><p><b>The image's name is: " .$imgName ."</b></p>"); 
    This predictably outputs the image name fine, but gives me a load of jumbled text (obviously the data making up the image, contained in "content").
    What needs to be done to convert this into image format ?

    Sorry if these are stupid questions.

    D

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The image can't be included in the HTML itself, it has to be a link to an image file (technically this isn't true, but there is a much better way to do it).

    Here is one way to do it:

    PHP Code:
    // ... code clipped
    print ("<img src=images.php?img=" .$img ."/><p><b>The image's name is: " .$imgName ."</b></p>"); 
    You should really use quotes around your values in your tags, for example: <img src="image.png" /> instead of <img src=image.png>. I did not change the code to do this so that you can understand the change I made better.

    So now, the image is requested at images.php?img=imagename.jpg. We need to make images.php request the image and output it.

    images.php:
    PHP Code:
    <?php
    $filename 
    $_GET['img'];

    $query "SELECT `content` FROM `upload` WHERE `name`='$filename' LIMIT 0,1;";

    $result mysql_query($query);
    if(
    mysql_num_rows($result) != 1){
      
    // image not found, do error stuff
      
    return -1;
    }

    // We need to output the mime type in the header
    $filename explode($filename'.'); // This is not a very robust way to do it, for example, it will see filename.jpg.bmp as a jpg.
    // You should come up with another way to get the filetype before you put this online, unless you are the only one who will be uploading
    $extension $filename[1];

    if(
    $extension 'jpg' || $extension == 'jpeg'){
      
    header('Content-Type: image/jpeg');
    }
    else {
      
    // unknown filetype
      
    return -1;
    }
    // you can add more filetypes, like gif and png, with a "switch" or } else if() {

    $row mysql_fetch_row($result);

    echo 
    $row[0]; // We echo all the data
    ?>
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  6. #6
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    paulgb and everyone else, Im still at a loss, but thank you very much for your help so far, much appreciated!

    Looking through all of this, including the original tutorial, I reckon that this is not actually what I am looking for.

    What I am trying to do:

    I have made a small sample shoppng site, where I am able to upload product information via a form, and then the products displyed on the site. Fine.
    Now, I would like to have an image for each product, that can be uploaded along with the rest of the details.

    So, I think ideally, the form would upload an image to a desired location, while the image name will be written to the appropriate field in the db . . .

    I could then use then reference the folder that the file was uploaded to as well as the image name, retrieved from the db for that product.

    Is this possible?

    D

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you want the uploaded file stored in the filesystem, and the filename stored in the database? All you have to do is save the content of the file upload to a new file. I havn't tried any filesystem stuff since I started using databases, but somthing along these lines should work:

    PHP Code:
    <?
       
    if(isset($_POST['upload']))
       {
               
    $fileName $_FILES['userfile']['name'];
               
    $tmpName  $_FILES['userfile']['tmp_name'];
               
    $fileSize $_FILES['userfile']['size'];
               
    $fileType $_FILES['userfile']['type'];
               
    // We should really check that the file is an image first, using its extension

    // Replace '/var/www/images/' with your upload folder

    if(!move_uploaded_file($tmpName), '/var/www/images/' $fileName){
      
    //error stuff
      
    return -1;
    }
               
               if(!
    get_magic_quotes_gpc())
               {
                   
    $fileName addslashes($fileName);
               }
               
               
    $con= @mysql_connect("localhost","user","secret");
               
    mysql_select_db("my_db,$con);
               
    $query = "INSERT INTO upload (namesizetypecontent ".
                        "
    VALUES ('$fileName''$fileSize''$fileType''')";
       
               mysql_query(
    $query) or die('Error, query failed' .mysql_error());                    
               
               
               echo "
    <br>File $fileName uploaded<br>";
               exit;
       }        
       ?>
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  8. #8
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And for the output:

    PHP Code:
    print ("<img src=images/" .$img ."/><p><b>The image's name is: " .$imgName ."</b></p>"); 
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  9. #9
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again,
    Thanks very much for the quick response.
    So, to clarify, the code you have just given me will exist on a page eg "uploaded.php" and the action on my upload form (previous page) will be uploaded.php ?

  10. #10
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah. A few changes should be made before running this on a production website, unless you have a strict user system already in place so that only you can upload files. Otherwise, people can upload php and destroy your site.
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  11. #11
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    brill, thanks for the heads up . . .
    D

  12. #12
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, its a problem you would get with any file upload that is open to the public. I assume you are going to build this into a user system so you don't have to worry about it, or protect it with .htaccess. If not, we want to check the file extension so that it is only one of: jpg,jpeg,gif,png and anything else you might want to upload. Here is one way you might do that:

    PHP Code:
    <?
       
    if(isset($_POST['upload']))
       {
               
    $fileName $_FILES['userfile']['name'];
               
    $tmpName  $_FILES['userfile']['tmp_name'];
               
    $fileSize $_FILES['userfile']['size'];
               
    $fileType $_FILES['userfile']['type'];
               
    $explodedfile explode($fileName,'.');
    $extension $explodedfile[count($explodedfile)-1];
    if(!(
    $extension == 'jpeg' || $extension == 'jpg' || $extension == 'gif' || $extension == 'png')){
      echo 
    "invalid file";
      
    // might want to log the IP here, along with $extension could be a hacker attack
      
    exit();
    }

    // Replace '/var/www/images/' with your upload folder

    if(!move_uploaded_file($tmpName), '/var/www/images/' $fileName){
      
    //error stuff
      
    return -1;
    }
               
               if(!
    get_magic_quotes_gpc())
               {
                   
    $fileName addslashes($fileName);
               }
               
               
    $con= @mysql_connect("localhost","user","secret");
               
    mysql_select_db("my_db,$con);
               
    $query = "INSERT INTO upload (namesizetypecontent ".
                        "
    VALUES ('$fileName''$fileSize''$fileType''')";
       
               mysql_query(
    $query) or die('Error, query failed' .mysql_error());                    
               
               
               echo "
    <br>File $fileName uploaded<br>";
               exit;
       }        
       ?>
    I didn't put it in the first time because I figured you would want to modify it for your own purposes anyway. It still needs error handling, but now the security threat is much less.
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  13. #13
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again . . . .
    I really appreciate the time and effort you are putting into this for me, thank you.
    I am going to be using this in a CMS which will obviously be p/word protected etc, but as I am still learning PHP and have not dealt with file handling before, this has been very helpful . . . .I will give it a bash this evening from home, and let you know how i get on . . . Thanks again!

  14. #14
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried testing it on my own server and found a few bugs in it, and at least one syntax error. I have fixed these now on my server, but I didn't have the time to get it working. I can send you the version with fixed bugs if you like, but I have added in some testing too that I will have to strip out.
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.

  15. #15
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi . . . That would be brill!
    The testing wouldnt be a problem, as Im sure I will be figure it out (hopefully).
    You could send it to my email account if it makes it easier? Whatever suits you best.

    Thanks a stack.

  16. #16
    SitePoint Evangelist
    Join Date
    Apr 2006
    Location
    Halifax, Canada
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it working on my server, it turned out to be a directory rights problem. I sent you a PM with my email.
    Paul Butler.org
    JSSpamBlock - Reduce WordPress spam.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •