Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Banned
    Join Date
    Aug 2002
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to store picture in mysql

    My sql problem
    Hi how are you doing

    I would like to store my picture in my database. How can I do it


    PHP Code:

    insert into mypic
    (picture)values('<img src="ito.gif">'); 

    like this

    I am not sure

    also When I take out my pic from my database how can I do it.

    could you explain with sql command. If you have any example, could you show me with script. I really want to know hot to do it but no idea




    Thanks

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    The best way to store graphics/images in a database is to not store it in the database. Confused? Of course. Instead of store the file itself in the database just store the path to the image along with the image name. Store the image in some directory and when you need it just retrieve the filename and path to it.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Banned
    Join Date
    Aug 2002
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi thanks but could you explain with sample script. Then i will understand it clearly

    Thanks

  • #4
    Banned
    Join Date
    Aug 2002
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    also I don't know hot to save my pictures from my database.

    Can I do like this
    PHP Code:
     insert into myimg(mypic)values('images/ito.jpg'); 
    like this

    when I take my picture out

    like this
    PHP Code:
     select mypic from myimg 
    Please teach me how to do it

    Thanks all

  • #5
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    If you insist on storing the actual image file in the database ( I recommend against it) then make sure you set the datatype to BLOB.

    As to your earlier question it can't really be explained better with a sample script. All you need to do is insert strings (varchar datatype) into the database. The strings you store will be the file name and the path to the image ie. ( "image.jpg" and "/home/images/" ). You would store the actual image file in some directory. All you store in the database is the information necessary to find the image.

    I don't know how I could possibly explain that any more clearly.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #6
    New to the CF scene
    Join Date
    Dec 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    <?php
    
    // Connect to database
    
    $errmsg = "";
    if (! @mysql_connect("localhost","trainee","abc123")) {
            $errmsg = "Cannot connect to database";
            }
    @mysql_select_db("test");
    
    // First run ONLY - need to create table by uncommenting this
    // Or with silent @ we can let it fail every sunsequent time ;-)
    
    $q = <<<CREATE
    create table pix (
        pid int primary key not null auto_increment,
        title text,
        imgdata longblob)
    CREATE;
    @mysql_query($q);
    
    // Insert any new image into database
    
    if ($_REQUEST[completed] == 1) {
            // Need to add - check for large upload. Otherwise the code
            // will just duplicate old file ;-)
            // ALSO - note that latest.img must be public write and in a
            // live appliaction should be in another (safe!) directory.
            move_uploaded_file($_FILES['imagefile']['tmp_name'],"latest.img");
            $instr = fopen("latest.img","rb");
            $image = addslashes(fread($instr,filesize("latest.img")));
            if (strlen($instr) < 149000) {
                    mysql_query ("insert into pix (title, imgdata) values (\"".
                    $_REQUEST[whatsit].
                    "\", \"".
                    $image.
                    "\")");
            } else {
                    $errmsg = "Too large!";
            }
    }
    
    // Find out about latest image
    
    $gotten = @mysql_query("select * from pix order by pid desc limit 1");
    if ($row = @mysql_fetch_assoc($gotten)) {
            $title = htmlspecialchars($row[title]);
            $bytes = $row[imgdata];
    } else {
            $errmsg = "There is no image in the database yet";
            $title = "no database image available";
            // Put up a picture of our training centre
            $instr = fopen("../wellimg/ctco.jpg","rb");
            $bytes = fread($instr,filesize("../wellimg/ctco.jpg"));
    }
    
    // If this is the image request, send out the image
    
    if ($_REQUEST[gim] == 1) {
            header("Content-type: image/jpeg");
            print $bytes;
            exit ();
            }
    ?>
    
    <html><head>
    <title>Upload an image to a database</title>
    <body bgcolor=white><h2>Here's the latest picture</h2>
    <font color=red><?= $errmsg ?></font>
    <center><img src=?gim=1 width=144><br>
    <b><?= $title ?></center>
    <hr>
    <h2>Please upload a new picture and title</h2>
    <form enctype=multipart/form-data method=post>
    <input type=hidden name=MAX_FILE_SIZE value=150000>
    <input type=hidden name=completed value=1>
    Please choose an image to upload: <input type=file name=imagefile><br>
    Please enter the title of that picture: <input name=whatsit><br>
    then: <input type=submit></form><br>
    <hr>
    By Graham Ellis - graham@wellho.net
    </body>
    </html>

  • #7
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy working but

    ferinds
    I used this code, it upload griatly
    put it down load only the last pricture or the first one or second one.

    whay. is it hanging and not working.
    if I add the where pid in the sql to idintify the picture it stop working.

  • #8
    Regular Coder lokeshshettyk's Avatar
    Join Date
    Aug 2008
    Location
    On the way to the moon!
    Posts
    157
    Thanks
    5
    Thanked 20 Times in 20 Posts
    what's hanging and not working? be a bit clear and which code did you use? post your code.
    _______________________________________________________________________________________
    Thank the user for his/her helpful post by clicking on the button below the post :)

    Cheers :)
    ~ LSK ~

  • #9
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Hi dear frind lokeshshettyk and all of the others.

    the code that bring up the picture is
    PHP Code:
    $gotten = @mysql_query("select * from pix order by pid desc limit 1");
    if (
    $row = @mysql_fetch_assoc($gotten)) {
            
    $title htmlspecialchars($row[title]);
            
    $bytes $row[imgdata];
    } else {
            
    $errmsg "There is no image in the database yet";
            
    $title "no database image available";
            
    // Put up a picture of our training centre
            
    $instr fopen("../wellimg/ctco.jpg","rb");
            
    $bytes fread($instr,filesize("../wellimg/ctco.jpg"));
    }

    // If this is the image request, send out the image

    if ($_REQUEST[gim] == 1) {
            
    header("Content-type: image/jpeg");
            print 
    $bytes;
            exit ();
            } 
    this is the code that bring the last pic. only
    I need a serten picture from the db so I add where pid=5 for the query to be as
    PHP Code:
    $gotten = @mysql_query("select * from pix where pid=5 order by pid desc limit 1"); 
    so this locks for the picture number 5 only. it did work some times. but when i changed it to a variable to be like this
    PHP Code:
    $gotten = @mysql_query("select * from pix where pid=$picidreq order by pid desc limit 1"); 
    and the viarbla is a string or a number it is the same , no picture comes out. and in printing the query it give the string of (select * from pix where pid=5 order by pid desc limit 1) but with no output picture. is there a problime.
    Note: that in writing the 5 dirctuly it some times work, the varible don't work at all.

  • #10
    Regular Coder lokeshshettyk's Avatar
    Join Date
    Aug 2008
    Location
    On the way to the moon!
    Posts
    157
    Thanks
    5
    Thanked 20 Times in 20 Posts
    Quote Originally Posted by a4world View Post
    Hi dear frind lokeshshettyk and all of the others.

    the code that bring up the picture is
    PHP Code:
    $gotten = @mysql_query("select * from pix order by pid desc limit 1");
    if (
    $row = @mysql_fetch_assoc($gotten)) {
            
    $title htmlspecialchars($row[title]);
            
    $bytes $row[imgdata];
    } else {
            
    $errmsg "There is no image in the database yet";
            
    $title "no database image available";
            
    // Put up a picture of our training centre
            
    $instr fopen("../wellimg/ctco.jpg","rb");
            
    $bytes fread($instr,filesize("../wellimg/ctco.jpg"));
    }

    // If this is the image request, send out the image

    if ($_REQUEST[gim] == 1) {
            
    header("Content-type: image/jpeg");
            print 
    $bytes;
            exit ();
            } 
    this is the code that bring the last pic. only
    I need a serten picture from the db so I add where pid=5 for the query to be as
    PHP Code:
    $gotten = @mysql_query("select * from pix where pid=5 order by pid desc limit 1"); 
    so this locks for the picture number 5 only. it did work some times. but when i changed it to a variable to be like this
    PHP Code:
    $gotten = @mysql_query("select * from pix where pid=$picidreq order by pid desc limit 1"); 
    and the viarbla is a string or a number it is the same , no picture comes out. and in printing the query it give the string of (select * from pix where pid=5 order by pid desc limit 1) but with no output picture. is there a problime.
    Note: that in writing the 5 dirctuly it some times work, the varible don't work at all.

    As you can see,
    PHP Code:
    $gotten = @mysql_query("select * from pix order by pid desc limit 1"); 
    will bring up only one pic since the limit it set to 1......


    if you want all the pics to be retrieved, remove "limit 1" and run the result set in a loop like

    PHP Code:

    while ($row = @mysql_fetch_assoc($gotten)) {
            
    $title htmlspecialchars($row[title]);
            
    $bytes $row[imgdata];
            
    header("Content-type: image/jpeg");
            echo 
    $bytes;

    and if
    PHP Code:
    $gotten = @mysql_query("select * from pix where pid=5 order by pid desc limit 1"); 
    works, then
    PHP Code:
    $pid "5";
    $gotten = @mysql_query("select * from pix where pid='".$pid."' order by pid desc limit 1"); 
    should work....
    _______________________________________________________________________________________
    Thank the user for his/her helpful post by clicking on the button below the post :)

    Cheers :)
    ~ LSK ~

  • #11
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    1) Download and install MySQL Query Browser

    2) Start it and create a connection to your database. Use the "test" as default schema.

    3a) Excute the following SQL statement.

    CREATE TABLE `test`.`pic` (
    `idpic` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `caption` VARCHAR(45) NOT NULL,
    `img` LONGBLOB NOT NULL,
    PRIMARY KEY(`idpic`)
    )
    TYPE = InnoDB;

    3b) Instead of creating the table via SQL, you can create a table by pressing the right mouse button on the schemata tree and select "Create New Table"

    Type "pic" in the Table Name field. Press enter. Press enter again to create the first column named "idpic" (that will be your primary key). Press enter to accept INTEGER as datatype. Enter "caption" to store a name for you picture. Press enter. Type "v" (which will trigger VARCHAR(45)) and press enter. Type "img" and enter. Type "longb" (which will trigger LONGBLOB) and press enter. Click [Apply Changes]. This will show you the SQL statement that will be executed.

    Press [Execute] to create the table.

    5) Close the table editor or refresh the schemata tree and doubleclick the new generated table in the schemata tree

    6) This will generate the SQL statement

    SELECT * FROM pic p

    Now click the table again to execute it. You can press Ctrl+Enter instead (of course)

    7) Now click on the [Edit] button in the action bar at the bottom. Doubleclick the empty caption field. Enter a name for your picture. Press enter.

    8) Click the "Open" overlay icon in the left of the img field in the first row. Or you can press the right mousebutton on the field and select "Load Field Content" from the popup menu.

    9) Click [Apply Changes] to actually store the new data in the database.

    10) Now click the "Magnifyer" overlay icon and if you have inserted a JPG, PNG or BMP image you will see it in the field viewer.

    Tip: To be able to insert images that are bigger than 1MB you have to increase the max_allowed_packed option in the server configuration file. You can change that in the Startup Variables section in the MySQL Administrator on the Networking page.


  •  

    Posting Permissions

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