SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Image uploads to mySQL database

    Is it possible to store(upload) and retrieve images in a mySQL database? (i know you can store binary data in a mySQL database, but how would one go about inputting this data using php, and then, how would one go about extracting the pictures from the database...and then displaying them?)

  2. #2
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    This is the very basic idea:

    Once you have uploaded your file, you will have to open it for binary ready reading (''rb) using the fopen() function:
    PHP Code:
    $file fopen($yourimage'rb'
    then perform the insert query:
    Code:
    $sql = "INSERT INTO yourtable SET
    filedata = '$file'";
    NOte that the 'filedata' col must be a binary column type:
    TINYBLOB
    BLOB
    MEDUMBLOB
    LONGBLOB
    (depending on the size of the file to be stored)
    Retrieving the data is also pertty straightforward. In the process of recording and displaying the data, you will have to record information about the file to send it to browser for displya purposes:
    file size
    file type
    file name

    I hope that gets you started

  3. #3
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i want to be able to have pictures load via mysql/php. i know that storing pictures in a databse is not good but storing the url is much better. can some1 tell me how to do this? or at least point me to a tutorial that goes into this in depth so i know exactly what i am doing.

  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what i want to do is just display images. no one is going to upload images. the site is for an architectural comany that want to display their portfolio of work. so the site is really a springboard for displaying plans, refurbishments and upcoming work. there is no uploading by the user at all.

  5. #5
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you don't need to create the link in a dynamic way? You just want to store it in the db?
    create a table:
    Code:
    CREATE TABLE `imageslinks` (
      `imageID` mediumint(8) unsigned NOT NULL auto_increment,
      `imageLink` varchar(250) NOT NULL,
      PRIMARY KEY  (`imageID`)
    ) TYPE=MyISAM AUTO_INCREMENT=1 ;
    and then store the image link with a query like this one:
    PHP Code:

    $imageLink 
    'the link of the image';

    $sql = @mysql_query(
     
    "INSERT INTO imagesLinks (imageLink

     VALUES ('
    $imageLink'
    )"
    ); 
    echo 
    mysql_error(); 
    untested but it should work, at least get you started

  6. #6
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the id='$id' will be in there somewhere since the images are record dependant. so its like this. record 1 which has the textual info for the site either must contain the image link so the image can be displayed aswell as the text or the image table must habe image_id which is the secondary key of the main textual table. i am not sure how to go about this.

  7. #7
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    normally when I have control of the image locations (e.g. not external links) I dont bother storing a link in the DB as there is usually little point , instead , take some identifying data from the DB , say `id` , `cat_id` and `user_id` (ok your fields will be different but whatever)

    then store the images logically to that schema e.g /images/$cat_id/$user_id/$prefix_$id.jpg

    in other words , you know for a given category 'beach' that an image for user '1' is going to be (if it exists) in /images/beach/1/$prefix_$id.jpg where prefix might be 'thumb','main' etc

    what schema you use for the file structure will depend on your needs but similar works for me 99/100 , you may still want to hold say alt/title tag data in the DB if you can't embed it in the image, but otherwise I don't see the point.

    the obvious con to this is that you need to check if an image exists before displaying it , but the overhead of that is pretty small.

    not so obvious advantages are e.g. you want to run a script against all of your image data , with a known file structure this is easy and can even be done without reference to the database.

  8. #8
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry but that to me sounds a lil confusing. i m aslowly gettin my head around the whole mysql php images thing and now this is something else to look at. i am about 1/2 way thru gettin the text sorted. the site has to go up in about a week and i need help with the images. what is the best way to do this. bearing in mind that there will be NO UPLOADING OF IMAGES by anyone or deletions of any kind. i just want view.php?id=23 to have text and images on it. to my understanding this will be due to php/mysql.

  9. #9
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in MySQL 3.23, you can use the LOAD_FILE() function to read a file from the file system, e.g.

    mysql> INSERT INTO table (description,image)
    -> VALUES ("my image",LOAD_FILE('image.jpg'));

    how do i then use php to acces it? can i create a table that stores both images and text or do they need to be different? can i have an id for the image to correspond to the id of the other tables that hold certain subject text. ie can i have a table that holds kit information and a table that hold kit pics and somehow combine them?

  10. #10
    SitePoint Member
    Join Date
    May 2004
    Location
    UK
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Buzzby,

    I don't really understand what you want.

    This is what I've gathered,
    you have a directory of images and a database and when you put view.php?id=19 you want image 19 to appear?

    If this is so you can do it like this:

    create a table with an id as an integer and url as text or varchar(255)

    Then you need to populate your table, fill it with the names of your image file and assign numbers to them.

    Once you've done that you can use PHP and MYSQL to search for the image and display it.

    If that doesn't make sense I'll try and elaborate but you have all the tools you need in the previous replies.

  11. #11
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok. i think i am confusing some people here. let me break it down like this.

    i have a site. i have a template that i will use for the site. (well about 3 - 5 templates depending on the look) i have text and images that i want to appear on every page. i would like to create/generate the pages dynamically via php/mysql. i have got the view.php?id=2 thing to work for a page with no images. i now want the page to work with text and images. there will not be any deletion or uploading of images at all. just a site that will display them. i would like to have a link on each pic linking to a bigger version (but will come to that later)

  12. #12
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    create table image_index (
    id int not null auto_increment primary key,
    url varchar(255),
    );


    insert into image_index values (1,'services_01.jpg');
    insert into image_index values (2,'services_02.jpg');
    insert into image_index values (3,'services_03.jpg');

    this did nothing. it just displayed the text in a table

  13. #13
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as far as i understand your problem is pretty simple. all you have to do is store and image_ID and image_URL (and any other data you feel like)in a table lets call it 'images', then to display it you would do something like

    make a recordset with the SQL:
    "select image_ID, image_URL from images where img_ID = '". $_GET['id']."'";

    then to display it you do something like:

    <img src = "<?php $rs['image_URL'] ?>">

    ..but i might well be missing the point here...

  14. #14
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ..yeah, im pretty sure i missed the point there.

  15. #15
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is it possible to store image data and textual data in the same table? i m sure it is. but both will have an id num. i suppose one would be text_id and one would be image_id, both set to int not null auto_increment, but one will have the primary key setting. that right?

  16. #16
    SitePoint Member
    Join Date
    May 2004
    Location
    london
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql code:

    CREATE TABLE imageslinks (
    imageID mediumint(8) unsigned NOT NULL auto_increment,
    imageLink varchar(250) NOT NULL,
    name varchar(255) not null,
    address varchar(255),
    PRIMARY KEY (imageID),
    ) TYPE=MyISAM AUTO_INCREMENT=1;

    insert into imageslinks values (1,'services_01.jpg','danny blancheflower','123 here street');
    insert into imageslinks values (2,'services_02.jpg','david blaine','45 west 45 street');
    insert into imageslinks values (3,'services_03.jpg','rolf harris','123 rue de la rue, la rochelle');

    php code:

    <body>
    <?php
    //$db = mysql_connect_db("localhost","","");
    $db=mysql_connect('localhost','root');
    mysql_select_db("danny",$db);
    $imageid = isset( $_GET['imageID'] ) ? $_GET['imageID'] : '';
    //$imageid; //contains the unique_id of the required image
    $sql="SELECT * FROM imageslinks WHERE imageID='$imageid'";
    $res=mysql_query($sql) or die(mysql_error());
    $row=mysql_fetch_array($res, MYSQL_ASSOC);
    print('<img src="http://localhost/images/'.$row['imageID'].'" />');
    ?>
    </body>

    i am stumped as to why this didnt work. can you help please? what is wrong with this code? does there need to be any coding to do with IMAGEBLOB?
    Last edited by buzzby365; May 14, 2004 at 04:17.

  17. #17
    SitePoint Member
    Join Date
    May 2004
    Location
    UK
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This didn't work becuae you imageID is not the name of the file.

    Put the file name in insted of the image id.

  18. #18
    SitePoint Member
    Join Date
    May 2004
    Location
    UK
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <body>
    <?php
    //$db = mysql_connect_db("localhost","","");
    $db=mysql_connect('localhost','root');
    mysql_select_db("danny",$db); 
    $imageid = isset( $_GET['imageID'] ) ? $_GET['imageID'] : ''
    //$imageid; //contains the unique_id of the required image 
    $sql="SELECT * FROM imageslinks WHERE imageID='$imageid'"
    $res=mysql_query($sql) or die(mysql_error()); 
    $row=mysql_fetch_array($resMYSQL_ASSOC); 
    print(
    '<img src="http://localhost/images/'.$row['[B]imagelink[/B]'].'" />');
    ?>
    </body>

  19. #19
    SitePoint Member
    Join Date
    May 2004
    Location
    UK
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No thats wrong, you can't just print it.

    You need to close you code bracket thing "?>"
    Then in the html area put this-

    PHP Code:
    <img href=/directory where your images are<?=$row['imagelink']?>>
    hope that helps

  20. #20
    SitePoint Member
    Join Date
    May 2004
    Location
    UK
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and when I say href I mean src.

    Sorry, its friday!


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
  •