#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    42
    Rep Power
    2

    Insert image in mysql with PHP


    1. In MYSQL:

    CREATE TABLE Photos
    (
    PhotoID int unsigned not null auto_increment primary key,
    Filename varchar(255) not null unique,
    Caption varchar(255) not null,
    Photo longblob not null
    );

    2. Transferred an image(1.png) from C drive to hosting server

    3. In PHP:
    PHP Code:
    <?PHP

    mysql_connect
    ("...""......."".........") or die(mysql_error()); 
    mysql_select_db("......") or die(mysql_error());

    mysql_query("INSERT INTO Photos8 values 

    NULL, 
    '1.PNG', 
    'MySQL logo by Lancioni OCP', 
    LOAD_FILE('1.PNG')" 
    );

    ?>
    Once i run php, nothing is inserted in db.

    Please advise.

    -P
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,053
    Rep Power
    9398
    1. Is MySQL running on that web server? Are you connecting to localhost?
    2. If it is and you are, LOAD_FILE() needs a full path to the file.
    3. If that still doesn't work then use mysql_error() to find out what the problem was.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    "nothing happens" isn't a very useful error description. When "nothing happens", it's time to start investigating: Is there a MySQL error? If that's the case, what does it say? Does the file exist? Are the database privileges and file permissions set correctly? etc.

    Also note that the MySQL manual specifically says that you need to specifiy the full path to the file.

    Apart from that, I question this whole approach. Why do you wanna store the images in the database? Why do you use this LOAD_FILE() stuff? You do realize this can lead to massive security issues if not used correctly?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    42
    Rep Power
    2
    Hi, I simply want to add image in mysql.

    More Info:

    I have a hosting account with www.mycheaperdomain.com.
    I connected to my hosting account using filezilla.
    I created a php file. In php, I connect to mysql database in hosting account through database id and pw.

    Thanks.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by phpstudent
    Hi, I simply want to add image in mysql.
    The question is: Why do you want to store the actual image content in the database? The usual approach is to store the image in the file system and merely put a link into the database. If you're not going this way, you should have a reason for it. Putting images into a folder is trivial. Putting them into the database isn't and comes with several problems, so don't do it unless you know why.

    For example, if you screw up the database privileges, people might be able to load arbitrary file via LOAD_FILE(). Not cool!
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,985
    Rep Power
    375
    i am surprised no one mentioned that he shouldn't be using the old MYSQL extension and instead switch to mysqli or PDO..

    oh wait i just did that now
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    PHP Code:
       <?php 
        $imagesql 
    "INSERT 
                      INTO Image (ImageFile)
                      VALUES (?)"
    ;

        
    $insert $mysqli->prepare($imagesql);

        
    //Dont pass data directly to bind_param store it in a variable
        
    $insert->bind_param("s",$img);

        
    //Assign the variable
        
    $img 'ImageFiles/'.$_FILES['fileImage']['name'];

        
    $insert->execute();
        
    ?>
    I have used this code many times to upload image and I damn sure definitely going to help you.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,985
    Rep Power
    375
    Originally Posted by Aaliyah Byrne
    PHP Code:
       <?php 
        $imagesql 
    "INSERT 
                      INTO Image (ImageFile)
                      VALUES (?)"
    ;

        
    $insert $mysqli->prepare($imagesql);

        
    //Dont pass data directly to bind_param store it in a variable
        
    $insert->bind_param("s",$img);

        
    //Assign the variable
        
    $img 'ImageFiles/'.$_FILES['fileImage']['name'];

        
    $insert->execute();
        
    ?>
    I have used this code many times to upload image and I damn sure definitely going to help you.
    it might but the OP needs to modify it as he is trying to store an image into db (which is not a good thing to do btw)

IMN logo majestic logo threadwatch logo seochat tools logo