SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: My sql problem

  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2002
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Thanks

  2. #2
    Ribbit... Eric.Coleman's Avatar
    Join Date
    Jun 2001
    Location
    In your basement
    Posts
    1,268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright.

    Are you actually tring to store the picture in the database? If so, you need to use PHP's FOPEN ( http://php.net/fopen ) and FREAD ( http://php.net/fread ) to get the pictures contents into a string, then you would insert that string into the database.

    The way you are doing it presently, is just inserting HTML into the database, which is in fact, doing nothing for you.

    When taking the picture out of the database, you first need to send the image type as a header, i.e. header( "Content Type: image/gif" ); for a gif. Then you just echo the contents from your DB query, which would look something like:

    Code:
    SELECT imageBlob FROM images WHERE imageID = 9
    Make sure your DB table is set on blob..

    Eric

    (if you want an example, I can show you one. I know there are a few articles on http://devshed.com and http://phpdeveloper.org / http://phpcomplete.com that cover this subject )
    Eric Coleman
    We're consentratin' on fallin' apart
    We were contenders, now throwin' the fight
    I just wanna believe, I just wanna believe in us

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2002
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand it. Sorry Could you explain with a example with scirpt.

    If I see a script(example) I will understand it

    Thanks

  4. #4
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I'm not going to write an example script for you because I'm lazy, maybe someone else will. But I'll will reaffirm what Zaire has said.

    You need to get the binary data of the image file. You do this with the file commands in PHP, to load this into a variable.

    Now that you've got the data that makes up the image, you can insert it into a table. I'll assume that you know how to insert into a table.

    The one stipulation is that the field type that you insert the data of the image into, is of type blob.

    Maybe if you now ask specifically what part you need some help with, eg loading the image data into a variable, or inserting to a table, making a field of type blob, or displaying the image when retrieving it.

    Oh no! the coots are eating my nodes!

  5. #5
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's something i had close to hand, it's pretty messy but shows how to do it. Copy the whole thing down and run it. Obviously you'll need to set up the required tables etc...
    PHP Code:
    <?php

    include('dbConnect.inc.php');

    if ((
    $action == "view" or $action == "dnld") and $id != "") {
        
        
    // User is retrieving a file
        
    $sql "SELECT FileName, MimeType, FileData
            FROM filestore WHERE ID = '
    $id'";
        
    $result = @mysql_query($sql);
        if (!
    $result) die("Database error: " mysql_error());
        
        
    $filename mysql_result($result,0,"FileName");
        
    $mimetype mysql_result($result,0,"MimeType");
        
    $filedata mysql_result($result,0,"FileData");
        
        if (
    $action == "dnld") {
            if (
    strpos($HTTP_USER_AGENT,"MSIE"))
                
    $mimetype "application/x-download";
            else
                
    $mimetype "application/octet-stream";
        }

        
    header("Content-disposition: filename=$filename");
        
    header("Content-Type: $mimetype");
        
    header("Content-Length: " strlen($filedata));

        echo(
    $filedata);
        exit();
        
    } elseif (
    $action == "del" and $id != "") {
        
        
    // User is deleting a file
        
    $sql "DELETE FROM filestore WHERE ID = '$id'";
        
    $ok = @mysql_query($sql);
        if (!
    $ok) die("Database error: " mysql_error());

        
    header("Location: $PHP_SELF");
        exit();

    } elseif (
    $action == "ulfile" and $uploadfile != "") {

        
    // Bail out if the file isnÃ_t really an upload.
        
    if (!is_uploaded_file($uploadfile))
            die(
    "$uploadfile is not an uploaded file!");
            
        
    // Open file for binary reading ("rb")
        
    $tempfile fopen($uploadfile,"rb");

        
    // Read the entire file into memory using PHP's
        // filesize function to get the file size.
        
    $filedata fread($tempfile,filesize($uploadfile));

        
    // Prepare for database insert by adding backslashes
        // before special characters.
        
    $filedata addslashes($filedata);

        
    // Create the SQL query.
        
    $sql "INSERT INTO filestore SET
        CATID = '
    $CATID',
        FileName = '
    $uploadfile_name',
        MimeType = '
    $uploadfile_type',
        Description = '
    $desc',
        FileData = '
    $filedata'";

        
    // Perform the insert.
        
    $ok = @mysql_query($sql);
        if (!
    $ok) die("Database error storing file: " mysql_error());

        
    header("Location: $PHP_SELF");
        exit();
    }

    // Default page view: lists stored files

    $sql "SELECT ID, FileName, MimeType, Description
        FROM filestore"
    ;
    $filelist = @mysql_query($sql)
        or die(
    "Database error: " mysql_error());
    ?>
    <html>
    <head>
    <title> Add a New Product Image </title>
    </head>
    <body>

    <h2>Add a New Product Image</h2>

    <form name="addimage" action="<?=$PHP_SELF?>?action=ulfile" method="post"
        enctype="multipart/form-data">
    <p>Choose the image to upload:<br />
    <input type="file" name="uploadfile" /></p>

    <p>Choose a category for this image:<br />
        <select name="CATID" size="1">
          <OPTION selected value="">Which Category >></OPTION>
    <?php
        $sql 
    "SELECT * FROM Categories ORDER BY CATID";
        
        
    $cats mysql_query($sql);
        
        while (
    $myrow mysql_fetch_array($cats)) {
            
    $cid $myrow["CATID"];
            
    $cname $myrow["CatName"];
            echo (
    "<OPTION VALUE='$cid'>$cname</OPTION>\n");
        }
    ?>
    </select>

    <p>Image Name:<br />
    <input type="text" name="desc" maxlength="255" /></p>
    <p><input type="submit" name="go" value="Upload" /></p>
    </form>
    <p>.</p>
    <p><b>To view existing images choose a category from below.</b></p>

    <form name="viewcategory" action="viewimages.php" method="post">
        <select name="CATID" size="1" onChange="document.viewcategory.submit();">
          <OPTION selected value="0">View Category >></OPTION>
    <?php
        $sql 
    "SELECT * FROM Categories ORDER BY CATID";
        
        
    $cats mysql_query($sql);
        
        while (
    $myrow mysql_fetch_array($cats)) {
            
    $cid $myrow["CATID"];
            
    $cname $myrow["CatName"];
            echo (
    "<OPTION VALUE='$cid'>$cname</OPTION>\n");
        }
    ?>
    </select>
    </form>

    <p>The following files are stored in the database:</p>
    <table width="85%" border="0" cellpadding="0" cellspacing="0">
    <tr>
        <th align="left">Filename</th>
        <th align="left">Type</th>
        <th align="left">Description</th>
    </tr>
    <?php

    if (mysql_num_rows($filelist) > 0) {
        while (
    $f mysql_fetch_array($filelist)) {
            
    ?>

    <tr valign="top">
        <td nowrap>
            <a href="<?=$PHP_SELF?>?action=view&id=<?=$f['ID']?>">
            <?=$f['FileName']?></a>
        </td>
        <td nowrap><?=$f['MimeType']?></td>
        <td><?=$f['Description']?></td>
        <td nowrap>
            [<a href="<?=$PHP_SELF?>?action=dnld&id=<?=$f['ID']?>"
            >Download</a> |
            <a href="<?=$PHP_SELF?>?action=del&id=<?=$f['ID']?>"
                onClick="return confirm('Delete this file?');"
            >Delete</a>]
        </td>
    </tr>
        
        <?php
        
    }
    } else {
        
    ?>
        <tr><td colspan="3" align="center">No Files!</td></tr>
        <?php
    }
    ?>
    </table>
    </body>
    </html>

  6. #6
    SitePoint Evangelist
    Join Date
    Apr 2002
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks I just would like to know how to store img or jpg to my sql. I know that I have to use blob. But I don't know how to write command and take out it from my sql.

    could you give me a simple example for it??

    Thanks

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2002
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you copy the code above and get it running live you can see for yourself how to doe what you want

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Location
    New Jersey
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Originally posted by philm
    If you copy the code above and get it running live you can see for yourself how to doe what you want
    Hi all,

    Would you have to do the same for say a document you wanted to store in a MySQL dB? Particularly, I have a need to create a document management interface, the documents will all be Word documents. Can I use the fopen() and fread() functions previously listed for this

    Thanks.
    If you keep doing what you did,
    you will keep getting what you got..


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
  •