The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Images on MySQL

Discussion in 'General Discussion' started by cannon_balls, Jan 20, 2006.

Thread Status:
Not open for further replies.
  1. cannon_balls

    cannon_balls Member

    Joined:
    Jan 20, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Any idea on how to store images on mySQL.
    Thanks in advance
     
  2. Stylise

    Stylise Registered

    Joined:
    Jan 8, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    MySQL is a database that holds records of entities (tables).

    You don't want to store files on it. You can store locations to files on it.

    Upload your images to a webserver. Use a 'location' text field to 'link it'.
     
  3. rminchrist

    rminchrist Member

    Joined:
    Dec 13, 2004
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Nairobi - Kenya
    Yes you can store images in MySQL
    Store it in a Blob field (column) type

    I have been doing that. Right now I am not in a possition to give you example scripts but on Monday when I get to my office, I will
     
  4. cannon_balls

    cannon_balls Member

    Joined:
    Jan 20, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Thanks Stylise but im just new to mySQL and the book i use doesnt say anything about a 'location' text field. Im hoping you can walk me through using that.

    My regards to rminchrist too. I am interested to know how the Blob field works. One question though. I aim to use the database to store loads and loads of family pictures so can you see any possible problems/disadvantages with using the blob field technique for large amounts of data/pictures
     
  5. webignition

    webignition Well-Known Member

    Joined:
    Jan 22, 2005
    Messages:
    1,880
    Likes Received:
    0
    Trophy Points:
    36
    The point is rather than storing an image in the database, just store the path to the image in the database.

    An example table structure:

    Code:
    image_id int not null auto_increment primary key,
    image_type enum('jpg','gif','png'),
    [B]inage_path varchar(255)[/B]
    Example table content
    Code:
    1 jpg /images/bob/23423dds.jpg
    2 png /images/goat/wert/png
    3 gif http://www.example.com/images/goat/asdf.gif
     
  6. Stylise

    Stylise Registered

    Joined:
    Jan 8, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1


    Sorry I wasn't too clear, was I?

    webignition has pretty much given the general idea. Just follow that.

    Basically, you're just making a reference to where the image is held on the server.

    Best bet is to search thru Google for a tutorial, especially if you need to upload an image with a form as that can be a little tricky.

    here's another quick eg.

    news (id, title, contents, icon)

    The above table would hold a record that looked a little like...

    '1', 'Roddick leaves the Open', 'Roddick has lost his match against...', 'icons/tennis.jpg'

    The when you're retrieving the data for MySQL, use the icon field as the reference to the icon image.

    ie. <img src="$row['icon']" alt="" />


    Cheers,
    Jamie
     
  7. cannon_balls

    cannon_balls Member

    Joined:
    Jan 20, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Thanks guys. I get the picture now. One last question though,
    ive stored in my database the image location and ive used php to extract the location and store it in a variable called $pic i.e ($pic="select img_location from images where id=3");

    How then do i display the image using the variable cause <img src="$pic"> doesnt work both in and out of the php tags.?

    Thanks in advance
     
    #7 cannon_balls, Jan 22, 2006
    Last edited: Jan 22, 2006
  8. Stylise

    Stylise Registered

    Joined:
    Jan 8, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1

    It would look something like this (this is without a template engine being used)

    <?php
    // connect to db
    // select database

    $result = mysql_query("SELECT img_location FROM images WHERE id=3");

    $imagedata = mysql_fetch_assoc($result);

    // running var_dump($imagesdata) here will display what is contained within the array

    echo "<img src=\"" . $imagedata['img_location'] . "\" alt=\"\" />
    ?>

    Hope that helps
     
  9. rminchrist

    rminchrist Member

    Joined:
    Dec 13, 2004
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Nairobi - Kenya
    Ok here it comes, I am in the office, so I don't really have time to explain it. Feel free to ask any questions. I hope you are conversant with php, html and mysql

    -----------------------------------------------------------
    Create a table as shown below

    create table myBlobs
    (
    blobId int auto_increment not null,
    blobTitle varchar(50),
    blobData longblob,
    blobType varchar(50),
    primary key(blobId),
    unique id(blobId)
    );

    Create a form page that you will use for uploading as shown below

    <html>
    <head>
    <title> Upload a File </title>
    </head>
    <body bgcolor="#FFFFFF">
    <form enctype="multipart/form-data" name="frmUploadFile" action="grabfile.php" method="post">
    <a href="http://www.devarticles.com">
    <img border="0" src="http://www.devarticles.com/dlogo.gif">
    </a>
    <table border="0" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
    <tr>
    <td width="100%" bgcolor="#FF9900" height="22" colspan="2">
    <p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
    Upload a File</font></b></td>
    </tr>
    <tr>
    <td width="100%" bgcolor="#FFE3BB" colspan="2">
    <p style="margin-left: 10; margin-right: 10"><font face="Verdana" size="2">
    <br>Please select a file from your local computer to upload to our web server
    for saving in our database. This file can be of any type you like. Once you
    have chosen a file, please click on the &quot;Upload this file&quot; button below.&nbsp;
    &nbsp;<br>&nbsp;</font></td>
    </tr>
    <tr>
    <td width="15%" bgcolor="#FFE3BB">
    <p style="margin-left: 10"><font face="Verdana" size="2">
    File Description:</font></td>
    <td width="85%" bgcolor="#FFE3BB">
    <input type="text" name="strDesc" size="20" maxlength="50"></td>
    </tr>
    <tr>
    <td width="15%" bgcolor="#FFE3BB">
    <p style="margin-left: 10"><font face="Verdana" size="2">File Location:</font></td>
    <td width="85%" bgcolor="#FFE3BB">
    <font face="Verdana" size="2">
    <input type="file" name="fileUpload" size="20"></font></td>
    </tr>
    <tr>
    <td width="33%" bgcolor="#FFE3BB">
    <p style="margin-left: 10"><font face="Verdana" size="2">
    <br>
    <br>
    &nbsp;</font></td>
    <td width="67%" bgcolor="#FFE3BB">
    <font face="Verdana" size="2">
    <input type="submit" value="Upload this file" name="cmdSubmit"></font></td>
    </tr>
    </table>
    </form>
    </body>
    </html>


    The action file for the form is as shown below

    <?php
    // GrabFile.php: Takes the details
    // of the new file posted as part
    // of the form and adds it to the
    // myBlobs table of our myFiles DB.

    global $strDesc;
    global $fileUpload;
    global $fileUpload_name;
    global $fileUpload_size;
    global $fileUpload_type;

    // Make sure both a description and file have been entered

    if(empty($strDesc) || $fileUpload == "none")
    die("You must enter both a description and file");

    // Database connection variables
    $dbServer = "localhost";
    $dbDatabase = "myFiles";
    $dbUser = "admin";
    $dbPass = "password";


    $fileHandle = fopen($fileUpload, "r");
    $fileContent = fread($fileHandle, $fileUpload_size);
    $fileContent = addslashes($fileContent);


    $sConn = mysql_connect($dbServer, $dbUser, $dbPass)
    or die("Couldn't connect to database server");
    $dConn = mysql_select_db($dbDatabase, $sConn)
    or die("Couldn't connect to database $dbDatabase");

    $dbQuery = "INSERT INTO myBlobs VALUES ";
    $dbQuery .= "(0, '$strDesc', '$fileContent', '$fileUpload_type')";
    mysql_query($dbQuery) or die("Couldn't add file to database");

    echo "<h1>File Uploaded</h1>";
    echo "The details of the uploaded file are shown below:<br><br>";
    echo "<b>File name:</b> $fileUpload_name <br>";
    echo "<b>File type:</b> $fileUpload_type <br>";
    echo "<b>File size:</b> $fileUpload_size <br>";
    echo "<b>Uploaded to:</b> $fileUpload <br><br>";
    echo "<a href='uploadfile.php'>Add Another File</a>";

    ?>

    That’s it

    Then to display the images you can create a page as shown below

    // Database connection variables
    $dbServer = "localhost";
    $dbDatabase = "myFiles";
    $dbUser = "admin";
    $dbPass = "password";

    $sConn = mysql_connect($dbServer, $dbUser, $dbPass)
    or die("Couldn't connect to database server");

    $dConn = mysql_select_db($dbDatabase, $sConn)
    or die("Couldn't connect to database $dbDatabase");

    $dbQuery = "SELECT blobId, blobTitle, blobType ";
    $dbQuery .= "FROM myBlobs ";
    $dbQuery .= "ORDER BY blobTitle ASC";
    $result = mysql_query($dbQuery) or die("Couldn't get file list");
    ?>

    <a href="http://www.devarticles.com"><img border="0" src="http://www.devarticles.com/dlogo.gif"></a>
    <table border="1" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
    <tr>
    <td width="34%" bgcolor="#FF9900" height="21">
    <p style="margin-left: 10"><b><font size="2" face="Verdana" color="#FFFFFF">
    Description</font></b></td>
    <td width="33%" bgcolor="#FF9900" height="21">
    <p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
    Type</font></b></td>
    <td width="33%" bgcolor="#FF9900" height="21">
    <p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
    File</font></b></td>
    </tr>

    <?php
    while($row = mysql_fetch_array($result))
    {
    ?>

    <tr>
    <td width="34%" bgcolor="#FFDCA8" height="21">
    <p style="margin-left: 10; margin-right: 10">
    <font face="Verdana" size="1">
    <?php echo $row["blobTitle"]; ?>
    </font>
    </td>
    <td width="33%" bgcolor="#FFDCA8" height="21">
    <p style="margin-left: 10">
    <font face="Verdana" size="1">
    <?php echo $row["blobType"]; ?>
    </font>
    </td>
    <td width="33%" bgcolor="#FFDCA8" height="21">
    <p style="margin-left: 10"><font face="Verdana" size="1">
    <a href="downloadfile.php?fileId=<?php echo $row["blobId"]; ?>">
    Download now
    </a></font>
    </td>
    </tr>

    <?php
    }
    echo "</table>";
    ?>

    The download file that is referenced by the file above is as shown below
    <?php
    global $blobId;

    if(!is_numeric($blobId))
    die("Invalid blobId specified");

    // Database connection variables

    $dbServer = "localhost";
    $dbDatabase = "myFiles";
    $dbUser = "admin";
    $dbPass = "password";

    $sConn = mysql_connect($dbServer, $dbUser, $dbPass)
    or die("Couldn't connect to database server");
    $dConn = mysql_select_db($dbDatabase, $sConn)
    or die("Couldn't connect to database $dbDatabase");
    $dbQuery = "SELECT blobType, blobData ";
    $dbQuery .= "FROM myBlobs ";
    $dbQuery .= "WHERE blobId = $blobId";
    $result = mysql_query($dbQuery) or die("Couldn't get file list");

    if(mysql_num_rows($result) == 1)
    {
    $fileType = @mysql_result($result, 0, "blobType");
    $fileContent = @mysql_result($result, 0, "blobData");
    header("Content-type: $fileType");
    echo $fileContent;
    }
    else
    {
    echo "Record doesn't exist.";
    }
    ?>
     
  10. rminchrist

    rminchrist Member

    Joined:
    Dec 13, 2004
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Nairobi - Kenya
    PS,
    You could use the four files above for mp3, Word Documents etc
    Have a nice day
     
  11. cannon_balls

    cannon_balls Member

    Joined:
    Jan 20, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Thanks guys. im also at worrk so i cant try this out now ubst i sure will when i get home.
     
  12. cannon_balls

    cannon_balls Member

    Joined:
    Jan 20, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Thanks rminchris. Ill be sure to use that in the future but for now while im still a novice, ill use Stylise's method which works perfectly. One last question though, is it possible to control the dimensions of the image displayed like width and height. I asked cause adding <width="100px" /> doesnt work
     
  13. mctDarren

    mctDarren Well-Known Member

    Joined:
    Jan 6, 2004
    Messages:
    664
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    New Jersey
    cPanel Access Level:
    Root Administrator
    In your example drop the 'px'. Here's a little routine to grab the actual dimensions and insert them into the HTML. I use this to resize pics to a uniform size sometimes. Hope it helps.

    PHP:
    list($width$height$type$attr) = getimagesize($PathToFileToExamine);
    echo 
    "<img src=\"" $imagedata['img_location'] . "\" width=\"$width\" height=\"$height\" alt=\"\" />
     
  14. cannon_balls

    cannon_balls Member

    Joined:
    Jan 20, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Thanks guys . youve all been great. Cheers
     
  15. ezpaintinginc

    ezpaintinginc Registered

    Joined:
    Apr 11, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Help!

    Please advise how I can add alt tags to the pictures.
     
Loading...
Thread Status:
Not open for further replies.

Share This Page